Excel 97 / VB variable in function not evaluating properly

[wrbodine]

I have a number of functions that are comparing based on a character selected in a worksheet. The code looks like:

funct = "=(IF( R[1]C[-24] = ""R"" ,(R[1]C[-22] - R[1]C[-23]), )

Range("AB" & newrow).Select
ActiveCell.value = funct

Then another one like:
funct = "=(IF( R[1]C[-24] = ""F"" ,(R[1]C[-22] - R[1]C[-23]), )
...

I want to put these in an loop using an array (I assume it should be a string array):

Dim Array(5) as string
Array(0) = "R" (or maybe """R""" ??)
Array(1) = "F"

for I = 0 to max

funct = "=(IF( R[1]C[-24] = Array(I),(R[1]C[-22] - R[1]C[-23]), )

next I

I've tried lots of different syntax for this & nothing works yet. It seems that the array isn't evaluating in the function to its string value....

Any ideas??

Thanks,
Ray

 

[dsi]

You should use the FormulaR1C1 property for defining formulas:

ActiveCell.FormulaR1C1 = funct

---------------------------------------------------------
You could use a nested IF statement:

=IF(A1="A","Value is A",IF(A1="B","Value is B","Unknown Value"))
[code]
---------------------------------------------------------
Or, you can create a function and call it out in the destination cell:
[code]
Public Function MyFcn(sVal As String) As String
    Select Case sVal
        Case "A"
            MyFcn = "Value is A"
        Case "B"
            MyFcn = "Value is B"
        Case Else
            MyFcn = "Unknown Value"
    End Select
End Function

Then, in the destination cell, enter the formula:

=MyFcn(A1)

Hope this helps!

 

[wrbodine]

Thanks for your ideas...

Someone else showed me what I needed to do it with the array....

funct = "=(IF( R[1]C[-24] = " & Array(I) & ",(R[1]C[-22] - R[1]C[-23]), )

I just needed to put the string array in the statement as you would a normal string, outside of the quotes. Duh....

(PS. - Array(0) = """R""" was the syntax needed for assignment...)

 

[dsi]

Instead of all the quotes, you could use the Chr() value:

Array(0) = Chr(34) & "R" & Chr(34)

 

[wrbodine]

I need to use an array of variables w/ the characters, bec. there's about 10 of them that all give the same result, and I don't want to write the same code 10 times...

Thanks,
Ray