VBA: Cell Range

Registration date
Monday August 14, 2017
Last seen
August 15, 2017
 Blocked Profile -

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
Registration date
Monday August 14, 2017
Last seen
August 15, 2017

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
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.