Using a variable in an If statement

[Rob]

Hello All,

Is there a way to use a variable in an If statement? I have collected some
information in one part of my subroutine and would like it to be the
conditional of the If statement like this:

Workbooks(orgFile).Worksheets("Cost Centers").Activate
Range("C2").Select
strGetCostCenter = """" & "790-30-00" & """" & " OR Temp = " & """" &
ActiveCell.Value & """"""
ActiveCell.Offset(1, 0).Range("A1").Select

Do Until ActiveCell = ""
strGetCostCenter = strGetCostCenter & " OR Temp = " & """" &
ActiveCell.Value & """"
ActiveCell.Offset(1, 0).Range("A1").Select
Loop


If Temp = strGetCostCenter Then


endif

If have looked at the results of the strGetCostCenter variable and it
matches exactely what I would use manually in the If statement.

Any suggestions would be greatly appreciated.

Regards,

Rob

 

[meh2030]

Hello All,

Is there a way to use a variable in an If statement?  I have collected some
information in one part of my subroutine and would like it to be the
conditional of the If statement like this:

Workbooks(orgFile).Worksheets("Cost Centers").Activate
Range("C2").Select
strGetCostCenter = """" & "790-30-00" & """" & " OR Temp = " & """" &
ActiveCell.Value & """"""
ActiveCell.Offset(1, 0).Range("A1").Select

Do Until ActiveCell = ""
    strGetCostCenter = strGetCostCenter & " OR Temp = " & """" &
ActiveCell.Value & """"
    ActiveCell.Offset(1, 0).Range("A1").Select
Loop

If Temp = strGetCostCenter Then

endif

If have looked at the results of the strGetCostCenter variable and it
matches exactely what I would use manually in the If statement.

Any suggestions would be greatly appreciated.

Regards,

Rob

Rob,

I'm not quite sure what you are asking for, but I think you have all
of your pieces in your code already. I've created a simple example
below.

Best,

Matt Herbert

If Temp = strGetCostCenter Then

endif

If Temp = strGetCostCenter Then
'insert your code here
End If

'-----------------------

Sub TestVarInIf()
Dim strName As String
Dim strMyName As String

strName = Range("a1").Value
strMyName = "Rob"

If strName <> strMyName Then
MsgBox "The name does not match." & vbLf & vbLf & "The name is: "
& strMyName
End If

End Sub

 

[Rob]

Matt,

The problem that I have found is that if I type it out like this:

If Temp = "790-30-00" Or Temp = "981107" or Temp = "981022" then
place my statements here
else
place my statements here
endif

it works just fine. However, when I use the process to store "790-30-00"
Or Temp = "981107" or Temp = "981022" to the variable strGetCostCenter it
goes right to the Else. I can't figure out why - I even had a msgbox show
the results of strGetCostCenter to make sure that it is showing properly -
and it is.

 

[FSt1]

hi
guessing here but there might be something wrong with this line.....
strGetCostCenter = """" & "790-30-00" & """" & " OR Temp = " & """" &
ActiveCell.Value & """"""
i suspect that you may have too many double quotes at the end. I pasted the
line in to my vb editor and display the variable in a msgbox. the "extra"
quotes showed up there. i reduced the quottes to 4 and the "extra" quotes
went away.
how are you getting temp??

regards
FSt1

 

[meh2030]

Matt,

The problem that I have found is that if I type it out like this:

If Temp = "790-30-00" Or Temp = "981107" or Temp = "981022" then
    place my statements here
else
    place my statements here
endif

it works just fine.  However, when I use the process to store  "790-30-00"
Or Temp = "981107" or Temp = "981022" to the variable strGetCostCenter it
goes right to the Else.  I can't figure out why - I even had a msgbox show
the results of strGetCostCenter to make sure that it is showing properly -
and it is.









- Show quoted text -

Rob,

Try the code below so that you can see how your variables are
behaving. Make sure the Immediate Window is open in VBE (View |
Immediate Window; or Ctrl + g). Step through your program with F8 and
watch the Immediate Window. Also, how are you initializing the "Temp"
variable (i.e. how are you putting data into it for the If Then
comparison)? (Later we can improve your code by eliminating
the .Select from your code. .Select slows things down).

Sub testCostCenter()

Dim strGetCostCenter As String
Dim strTemp As String

Workbooks(orgFile).Worksheets("Cost Centers").Activate
Range("C2").Select

strGetCostCenter = """" & "790-30-00" & """" & " OR Temp = " & """" &
ActiveCell.Value & """"""""
Debug.Print "strGetCostCenter:"; strGetCostCenter

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell = ""
strGetCostCenter = strGetCostCenter & " OR Temp = " & """" &
ActiveCell.Value & """"
Debug.Print "strGetCostCenter:"; strGetCostCenter
ActiveCell.Offset(1, 0).Select
Loop

Debug.Print "strTemp:"; strTemp
Debug.Print "strGetCostCenter:"; strGetCostCenter

If strTemp = "790-30-00" Or strTemp = "981107" Or strTemp = "981022"
Then
'place statements here
Else
'place statements here
End If

End Sub

 

[Rob]

Thank you for the response. First, Temp is a string variable that goes to a
specific cell in the worksheet and sees if the cell value equals one of the
numbers in strGetCostCenter. If I try what you are proposing and take off
the last four quotes the first number will not have quotes around it.
Instead of "790-30-00" Or Temp = "923029" I would get "790-30-0" Or Temp =
"923029

If I were to hard code this into my macro as this it works fine:

If Temp = "923029" I would get "790-30-0" Or Temp = "923029" Then

Endif

Why won't it work if it is part of a variable?

Regards,

Rob

 

[Rob]

Temp is a string variable that I use to compare a specific cell to the
numbers in strGetCostCenter. What is odd is that if I type the if statement
out manually adding each on of the cost centers like the example below it
works great:

If Temp = "790-30-00" Or Temp = "981107" Or Temp = "981022" Then

Endif

When I look at the immediates window in the test that you gave me it looks
exactely like what I show above. I need to be able to have the user list the
cost centers before starting. This keeps them from having to change the
macro when cost centers change. I just don't know why it will work when
typed out but not when using a variable.

 

[meh2030]

Temp is a string variable that I use to compare a specific cell to the
numbers in strGetCostCenter.  What is odd is that if I type the if statement
out manually adding each on of the cost centers like the example below it
works great:

If Temp = "790-30-00" Or Temp = "981107" Or Temp = "981022"  Then

Endif

When I look at the immediates window in the test that you gave me it looks
exactely like what I show above.  I need to be able to have the user list the
cost centers before starting.  This keeps them from having to change the
macro when cost centers change.  I just don't know why it will work when
typed out but not when using a variable.














- Show quoted text -

Rob,

I think there is confusion as to what the actual string data is. I
have two different Subs below to look for two separate matches and I
include my worksheet assumptions.

Are you looking for an exact match to one of the three cost centers -
"790-30-00", "981107", "981022"?

Sub TestSingleMatch()
Dim varMyAry As Variant
Dim strGetCostCenter As String
Dim varMatch As Variant

varMyAry = Array("790-30-00", "981107", "981022")
strGetCostCenter = "981107"

varMatch = Application.Match(strGetCostCenter, varMyAry, 0)

If IsError(varMatch) Then
MsgBox "Did not find a match."
Else
MsgBox "Found a match." & vbLf & vbLf & "The match is " &
strGetCostCenter
End If

End Sub

Or are you looking for an exact match to your ever expanding
strGetCostCenter which is embedded in your loop? For purposes of
illustration I placed 981107 in Range("C2") and 981022 in Range
("C3"). Range("C4") is blank.

Sub TestLongStringMatch()

Dim strGetCostCenter As String
Dim strTemp As String

strTemp = """790-30-00""" & " OR Temp = " & """981107""" & " OR Temp =
" & """981022"""
Debug.Print "strTemp:"; strTemp

'Workbooks(orgFile).Worksheets("Cost Centers").Activate
Range("C2").Select

strGetCostCenter = """" & "790-30-00" & """" & " OR Temp = " & """" &
ActiveCell.Value & """"
Debug.Print "strGCC :"; strGetCostCenter

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell = ""
strGetCostCenter = strGetCostCenter & " OR Temp = " & """" &
ActiveCell.Value & """"
Debug.Print "strGCC :"; strGetCostCenter
ActiveCell.Offset(1, 0).Select
Loop

Debug.Print "strTemp:"; strTemp
Debug.Print "strGCC :"; strGetCostCenter

If strTemp = strGetCostCenter Then
MsgBox "Found a match."
Else
MsgBox "Did not find a match."
End If

End Sub

Let me know if this helps.

Best,

Matt Herbert