How to insert string into an if formula?

[Tuscana]

Hello users on MrExcel. I am still a Newbie at VBA, so this question might seem a bit dumb, but i have tried to find a solution for the past 2 days, on and off.

Lets just say, for the ease of it, that i have a Cell with the symbol ">", and it can change to "<"

The cell i defined as "s" and dimmed as string.

My question is:

How do i put it into my if formula

If i & s & k Then (I want it to say i < k Then)

x = x + 1
Else

End if

Any help will be much appreciated!

 

[fadee2]

based on your query, your idea is correct. You only have to define i and k. try the following:

i = "i"
k = "k"
MsgBox i & s & k

the result will be

hth...

 

[offthelip]

to show you how I would do this try this simple demo,:
put a number in A2 and another number in C2 then put < or > in b2 and run this code. then play with the values:

a2val = Range("A2").Value
B2Val = Range("b2").Value
c2val = Range("c2").Value
B2asc = Asc(B2Val)
If B2asc = 60 Then ' " <"
 If a2val < c2val Then
   MsgBox (" a2 is less than c2")
 Else
   MsgBox (" a2 is not less than c2")
 End If
End If
If B2asc = 62 Then ' " >"
 If a2val > c2val Then
   MsgBox (" a2 is greater than c2")
 Else
   MsgBox (" a2 is not greater than c2")
 End If
End If

 

[Tuscana]

based on your query, your idea is correct. You only have to define i and k. try the following:

i = "i"
k = "k"
MsgBox i & s & k

the result will be

View attachment 33033

hth...

Thank you for your quick response!

I Dimmed i and k to numbers. for information i and k is dices with 6 sides.

If i enter the msgbox in my query it gives me the correct "setup" . But i get type mismatch when it moves on to the if formula.

    For i = 1 To 6
   
   
        For k = 1 To 6
      
        If i & s & k Then
            x = x + 1
Else
           
            End If
Next k

Next i

 

[RoryA]

You cannot use a string variable as code like that. You'd need to use something like Evaluate there:

If Evaluate(i & s & k) then

 

[Tuscana]

to show you how I would do this try this simple demo,:
put a number in A2 and another number in C2 then put < or > in b2 and run this code. then play with the values:

a2val = Range("A2").Value
B2Val = Range("b2").Value
c2val = Range("c2").Value
B2asc = Asc(B2Val)
If B2asc = 60 Then ' " <"
If a2val < c2val Then
   MsgBox (" a2 is less than c2")
Else
   MsgBox (" a2 is not less than c2")
End If
End If
If B2asc = 62 Then ' " >"
If a2val > c2val Then
   MsgBox (" a2 is greater than c2")
Else
   MsgBox (" a2 is not greater than c2")
End If
End If

Thank you for you answer! This works and i will try to learn what i can from this code. It is way more advanced than what my vba skills allow me to write :D

 

[fadee2]

As @RoryA pointed out, IF evaluates your logic as True or False and then points you towards relative result.

You cannot use a string variable as code like that. You'd need to use something like Evaluate there:

If Evaluate(i & s & k) then

My reply was merely an extension of your own query. The reason behind

If i enter the msgbox in my query it gives me the correct "setup"

is that message box is only displaying all the variables one by one. MsgBox does not calculate the results itself.

hth...

 

[Tuscana]

You cannot use a string variable as code like that. You'd need to use something like Evaluate there:

If Evaluate(i & s & k) then

This works very well and it is easy for me to understand. Just what i needed for my dice evaluation!