| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable in an If statement
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 "FSt1" wrote: 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 "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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using a variable in an If statement
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable statement | Excel Discussion (Misc queries) | |||
| Variable in if statement | Excel Worksheet Functions | |||
| using a variable for MOD Statement | Excel Programming | |||
| if than statement has too many variable | New Users to Excel | |||
| Macro Creating Variable and using variable in a SQL statement | Excel Programming | |||
Hybrid Mode
