Can't get the If and Else statement correct
I using the below to find IF a set of 2 cell values exist in another sheet.
If they Do exist then i need to run say Macro1. If they do NOT exist then i need to run say Macro2. I can't seem to get the ELSE statement correct. Thet either BOTH run or NONE. ~~~~~~~~~~~~~~~~~~~~~~ Sub AlreadyThere() ' Check to see if it Already exists With Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If rngFound.Value = Sheet4.Range("D2").Value And rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else: 'DO THAT End If End With End With End Sub ~~~~~~~~~~~~~~~~~~~~~~ I am pretty sure it is a simple matter of getting the ELSE or ELSEIF statement correct to saolve it. Corey.... |
Can't get the If and Else statement correct
Sub AlreadyThere()
' Check to see if it Already exists With ActiveWorkbook 'Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ Matchbyte:=False) If Not rngFound Is Nothing Then If rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else ' DO THAT End If Else ' DO THAT End If End With End With End Sub -- __________________________________ HTH Bob "Corey ...." wrote in message ... I using the below to find IF a set of 2 cell values exist in another sheet. If they Do exist then i need to run say Macro1. If they do NOT exist then i need to run say Macro2. I can't seem to get the ELSE statement correct. Thet either BOTH run or NONE. ~~~~~~~~~~~~~~~~~~~~~~ Sub AlreadyThere() ' Check to see if it Already exists With Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If rngFound.Value = Sheet4.Range("D2").Value And rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else: 'DO THAT End If End With End With End Sub ~~~~~~~~~~~~~~~~~~~~~~ I am pretty sure it is a simple matter of getting the ELSE or ELSEIF statement correct to saolve it. Corey.... |
Can't get the If and Else statement correct
Thank you very much Bob.
You are a true gentleman. I think i got lost int he If and If Not's etc .... Corey.... "Bob Phillips" wrote in message ... Sub AlreadyThere() ' Check to see if it Already exists With ActiveWorkbook 'Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ Matchbyte:=False) If Not rngFound Is Nothing Then If rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else ' DO THAT End If Else ' DO THAT End If End With End With End Sub -- __________________________________ HTH Bob "Corey ...." wrote in message ... I using the below to find IF a set of 2 cell values exist in another sheet. If they Do exist then i need to run say Macro1. If they do NOT exist then i need to run say Macro2. I can't seem to get the ELSE statement correct. Thet either BOTH run or NONE. ~~~~~~~~~~~~~~~~~~~~~~ Sub AlreadyThere() ' Check to see if it Already exists With Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If rngFound.Value = Sheet4.Range("D2").Value And rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else: 'DO THAT End If End With End With End Sub ~~~~~~~~~~~~~~~~~~~~~~ I am pretty sure it is a simple matter of getting the ELSE or ELSEIF statement correct to saolve it. Corey.... |
Can't get the If and Else statement correct
You also need to test whether a match was found. By doing that, no need to
test if the found cell is the same as the lookup value, it must be. -- __________________________________ HTH Bob "Corey" wrote in message ... Thank you very much Bob. You are a true gentleman. I think i got lost int he If and If Not's etc .... Corey.... "Bob Phillips" wrote in message ... Sub AlreadyThere() ' Check to see if it Already exists With ActiveWorkbook 'Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ Matchbyte:=False) If Not rngFound Is Nothing Then If rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else ' DO THAT End If Else ' DO THAT End If End With End With End Sub -- __________________________________ HTH Bob "Corey ...." wrote in message ... I using the below to find IF a set of 2 cell values exist in another sheet. If they Do exist then i need to run say Macro1. If they do NOT exist then i need to run say Macro2. I can't seem to get the ELSE statement correct. Thet either BOTH run or NONE. ~~~~~~~~~~~~~~~~~~~~~~ Sub AlreadyThere() ' Check to see if it Already exists With Workbooks("TSS") ' "Data" Sheet3 is NOT Sheet's "Data" Dim rngFound As Range On Error Resume Next With Worksheets("Data").Range("A:A") Set rngFound = .Find(What:=Sheet4.Range("D2").Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If rngFound.Value = Sheet4.Range("D2").Value And rngFound.Offset(0, 1).Value = Sheet4.Range("K2").Value Then ' Do THIS Else: 'DO THAT End If End With End With End Sub ~~~~~~~~~~~~~~~~~~~~~~ I am pretty sure it is a simple matter of getting the ELSE or ELSEIF statement correct to saolve it. Corey.... |
All times are GMT +1. The time now is 05:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com