On Mar 17, 10:27*am, Rob wrote:
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.



" wrote:
On Mar 16, 9:49 pm, Rob wrote:
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.

" wrote:
On Mar 16, 9:04 pm, Rob wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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