Create a Function that has several lines of if then - trouble identifying the Row

[CRIMEDOG]

I want to fill Column P with =Rcost() function to determine value based on the IF Thens in the code... Problem: How do you identify the row the function is working on? This gives me Value error.

Function Rcost(Mynum)

  Dim Cell As Range
  Dim R As Long
  Dim Wks As Worksheet
  Dim Listcost As String
  Dim Repcost As String
  Dim Specost As String
  Dim Trancost As String
  Dim Facter As String
  
  
Set Wks = ActiveSheet
 Set Row = Mynum
      
      Repcost = Wks.Cells(R, "Q")
      Listcost = Wks.Cells(R, "O")
      Specost = Wks.Cells(R, "R")
      Trancost = Wks.Cells(R, "M")
      
      If Wks.Cells(R, "O") = 0 Then
      Facter = WorksheetFunction.Index(Worksheets("Mainframes").Range("L5:R6"), 1, Worksheets("Entry").Range("AJ1"))
      ElseIf Round(Repcost / 0.48, 0) + Specost <= Listcost Then
      Facter = WorksheetFunction.Index(Worksheets("Mainframes").Range("L5:R6"), 1, Worksheets("Entry").Range("AJ1"))
      Else: Facter = WorksheetFunction.Index(Worksheets("Mainframes").Range("L5:R6"), 2, Worksheets("Entry").Range("AJ1"))
      End If
      
 
      If Wks.Cells(R, "S") = "Promo" Then
      Rcost = Listcost
      
      ElseIf Wks.Cells(R, "O") = 0 Then
      Rcost = Round(Repcost / Facter, 0) + Specost
      
      ElseIf Wks.Cells(R, "O") > 0 And Round(Repcost / Facter, 0) + Specost <= Listcost Then
      Rcost = Round(Repcost / Facter, 0) + Specost
      
      Else: Rcost = Listcost
      End If
    
      
End Function

[Bob Phillips]

Use

Application.Caller.Row

[swoop99]

Declare a variable as an integer and use it as a row counter in a for next loop. Then replace the row in the range with the variable.

eg

Dim R as integer

for R = 1 to 1000 ' or whatever row your data stops at.
      Repcost = Wks.Cells(R, 16)
      Listcost = Wks.Cells(R, 15)
      Specost = Wks.Cells(R, 17)
      Trancost = Wks.Cells(R, 13)

   etc, etc

next R

[CRIMEDOG]

That looks good, but I wanta function that returns a value based on the if then statements - That only applies to the row the function is added to.
So if I added =Rcost() to cell P4. I want the Cells(R,... to read as Cells(4,...
I tried:

Dim R As Integer  
  Set Wks = ActiveSheet
  
      R = Application.Caller.Row

Then added =Rcost() to cell P4 : #VALUE error

[Bob Phillips]

That looks good, but I wanta function that returns a value based on the if then statements - That only applies to the row the function is added to.
So if I added =Rcost() to cell P4. I want the Cells(R,... to read as Cells(4,...
I tried:

Dim R As Integer  
  Set Wks = ActiveSheet
  
      R = Application.Caller.Row

Then added =Rcost() to cell P4 : #VALUE error

Cells(R, "P").Value = ...
Cells(R, "Q").Value = ...
'etc.

[CRIMEDOG]

also tried R as Integer - For R= 1-1000... same error

[MrShorty]

So many thoughts, where to begin:

When I've written UDF's, the #Value error is often caused by some syntax error that is preventing the function of running. For example, if you are really using the code as stated with the call being =Rcost(), you have told the function to require an argument (mynum), then not given the function an argument when called. Unless you put optional in front of the argument, Excel/VBA will require that there be an argument there to run.

You also might look at data type mismatches. I notice most of your variables are dimensioned as strings, yet you make a lot of numerical comparisons and operations on them. VBA may not be automatically making those data type conversions (or maybe I'm the one who is not).

As general good practice when coding UDF's pass all the information the function needs through the argument list. This allows Excel to put the function into the calculation tree correctly, so the function can update properly. It will also resolve the main question, because you can then call the function with something like (and pardon me for using R1C1 notation) =Rcost(rc2,rc4,rc8) and, as you copy the function down, the argument references will always refer to the row the function is called from. Just looking at your code, there is the potential for needing several arguments, but I think it would still make the code more readable and more workable.

[CRIMEDOG]

Okay - changed the Variables to Integers - trying the R1C1 (I suppose R1C1=mynum) I am attaching sample workbook...

Thanks for all of your help

[MrShorty]

R1C1 notation is just a different way of doing cell references in Excel. If you are in row 3, the a reference to RC2 is exactly the same as $B3, so in A1 notation, the same function call (in row 3) looks like =Rcost($B3,$D3,$H3). Which reference notation you use isn't important. What is important is passing those cell references through the argument list.

[CRIMEDOG]

What about this:

Function Rcost(Listcost As Integer, Repcost As Integer, Specost As Integer)
  
  Dim Facter As Integer
    
      If Listcost = 0 Then
       Facter = WorksheetFunction.Index(Worksheets("Mainframes").Range("L5:R6"), 1, Worksheets("Entry").Range("AJ1"))
      ElseIf Round(Repcost / 0.48, 0) + Specost <= Listcost Then
       Facter = WorksheetFunction.Index(Worksheets("Mainframes").Range("L5:R6"), 1, Worksheets("Entry").Range("AJ1"))
      Else: Facter = WorksheetFunction.Index(Worksheets("Mainframes").Range("L5:R6"), 2, Worksheets("Entry").Range("AJ1"))
      End If
      
 
      If Listcost = 0 Then
       Rcost = Round(Repcost / Facter, 0) + Specost
      
      ElseIf Listcost > 0 And Round(Repcost / Facter, 0) + Specost <= Listcost Then
       Rcost = Round(Repcost / Facter, 0) + Specost
      
      Else: Rcost = Listcost
      End If
    
    
End Function

[MrShorty]

Does it work?

I would probably pass the range you are pulling facter from (and the column number taken from the "entry" sheet) through the argument list as well. They can be absolute references in the function call.

[CRIMEDOG]

no it isn't working yet.
can you use the range from another sheet in the argument list? The ranges I use to determine "Facter" is a fixed range. Btw, I purposely spelled Factor wrong because it is such a common word.

Also, when I start typing Rcost in a cell - It shows as an option, but doesn't display the arguments like a regular function.
eg average(range,... --it just shows Rcost(

[CRIMEDOG]

Well, it just started giving a result and not an error - New problem, I need to reference a cell that is not an integer - I am defining as string, should I put it in the argument list (as it is, excel isn't reminding which arguments I used)

[MrShorty]

can you use the range from another sheet in the argument list? The ranges I use to determine "Facter" is a fixed range.

Yes. As with Excel's native functions, arguments to UDF's can be references to the current spreadsheet, another tab in the same workbook, another workbook, or even hand entered constants. As with native functions, you just need to make the references absolute (using dollar signs $ in A1 notation) so the reference doesn't change as you copy the function around.

Also, when I start typing Rcost in a cell - It shows as an option, but doesn't display the arguments like a regular function.

That would be nice, wouldn't it. It seems that Microsoft has never made these "tips" work well (if at all) with user-defined functions. I've found that the best way to get this kind of reminder is to use the function wizard. Keystrokes in my version of Excel is alt-i (to bring up the insert menu), then f to bring up the function wizard. I don't know how to find the function wizard in the newer versions of Excel.

I need to reference a cell that is not an integer - I am defining as string,

Why string? Are you specifically avoiding the double data type?

[CRIMEDOG]

That cell contains a word (or not)
Basically if it contains "promo" then Rcost=Listcost

As soon as I asked about the range - I figured the answer. so here is what I have now (Once I started trying to check the "Promo" cell I got error again...

Function Rcost(Listcost As Integer, Repcost As Integer, Specost As Integer, Tier As Integer, Promo As String, Facterrange As Range)
  
  Dim Facter As String
  

      If Listcost = 0 Then
       Facter = WorksheetFunction.Index(Facterrange, 1, Tier)
      ElseIf Round(Repcost / 0.48, 0) + Specost <= Listcost Then
       Facter = WorksheetFunction.Index(Facterrange, 1, Tier)
      Else: Facter = WorksheetFunction.Index(Facterrange, 2, Tier)
      End If
      
      If Promo = "Promo" Then
       Rcost = Listcost
      ElseIf Listcost = 0 Then
       Rcost = Round(Repcost / Facter, 0) + Specost
      
      ElseIf Listcost > 0 And Round(Repcost / Facter, 0) + Specost <= Listcost Then
       Rcost = Round(Repcost / Facter, 0) + Specost
      
      Else: Rcost = Listcost
      End If
        
    
End Function

[MrShorty]

What kind of error are you getting?

Set a breakpoint somewhere (F9), so you can get the function to enter debug mode. That will allow you to step through the function and locate the statement that is causing the error.

I don't know if it matters really, though it seems like it could cost some processor time, you can use the .cells property/method to return your facter instead of the worksheet index function.

Facter=facterrange.cells(1,tier)

[CRIMEDOG]

Apparently saving the workbook and then re-openning it fixed it.