Can't get the If and Else statement correct


C

Corey ....

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....
 
Ad

Advertisements

B

Bob Phillips

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
 
C

Corey

Thank you very much Bob.
You are a true gentleman.

I think i got lost int he If and If Not's etc ....


Corey....
 
Ad

Advertisements

B

Bob Phillips

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top