×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel 97 / VB variable in function not evaluating properly

Excel 97 / VB variable in function not evaluating properly

Excel 97 / VB variable in function not evaluating properly

(OP)
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

RE: Excel 97 / VB variable in function not evaluating properly

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"))

---------------------------------------------------------
Or, you can create a function and call it out in the destination cell:

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!

RE: Excel 97 / VB variable in function not evaluating properly

(OP)
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...)

RE: Excel 97 / VB variable in function not evaluating properly

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

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

RE: Excel 97 / VB variable in function not evaluating properly

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close