VBA: Cell Range

shellboy Posts 2 Registration date Monday August 14, 2017 Status Member Last seen August 15, 2017 - Aug 14, 2017 at 04:16 AM
 Blocked Profile - Aug 16, 2017 at 04:18 PM

I am a beginner with VBA and I have a question that might sounds stupid to some. I have created a function of IfElseIf and I want to run it only on one column of my excel sheel. However I have been looking all over the internet on how to do it but nothing so far. Here is my code: ( I want to run it only on cell range P16 to P210)

Function Royalty(Rfactor As String) As Long

If Rfactor <= 0.5 Then

Royalty = 0.02

ElseIf Rfactor <= 0.8 And Rfactor > 0.5 Then

Royalty = 0.04

ElseIf Rfactor <= 1.1 And Rfactor > 0.8 Then

Royalty = 0.06

ElseIf Rfactor <= 1.5 And Rfactor > 1.1 Then

Royalty = 0.08

ElseIf Rfactor <= 2 And Rfactor > 1.5 Then

Royalty = 0.09

ElseIf Rfactor <= 2.5 And Rfactor > 2 Then

Royalty = 0.1

ElseIf Rfactor <= 3 And Rfactor > 2.5 Then

Royalty = 0.11

ElseIf Rfactor <= 3.5 And Rfactor > 3 Then

Royalty = 0.13


Royalty = 3.5

End If

End Function


1 reply

Ok, this is not constructed right. First of all:

Where is this variable initialized at? Rfactor

You will need to post the whole program so we can see how it flows!

It's kind of fun to do the impossible! -Walter Elias Disney
shellboy Posts 2 Registration date Monday August 14, 2017 Status Member Last seen August 15, 2017
Aug 15, 2017 at 08:26 AM
That is the thing, I was able to write this program by looking at a couple of youtube videos... This is the WHOLE program I wrote. The thing is on the video I was watching it seemed that with just this code, everything was working fine.. Here my variable is indeed Rfactor, however I don't know how to set it. Here basically what I am trying to input is that if the value of R factor calculated on my excel to be for example 1.5 then the Royalty should be equal to 0.08. I hope you can understand what I am saying even if it does not seem to be very clear. Please let me know if you need some more information. I can even share my excel file with you if you would prefer.
Blocked Profile
Aug 16, 2017 at 04:18 PM
OK, if you are not passing the value of Rfactor, then that is what is failing. Initialize RFactor as a cell.value, as in an integer, and pass it to the subroutine.

Write now, you are trying to compare a string(RFactor as String) to an integer (.05)!

Do not declare the RFactor as anything.

Try this, in a cell place the value:

And see what the cell turns into.

Also, you must place this Royalty subroutine, into a module, not a worksheet.