I have the below code:
Goal - I want the code to search Column "E" for the word "ExchangeIssue". If text "ExchangeIssue" is not found in Column E then Exit Sub.
If text "ExchangeIssue" is found, display message box asking to if they want to confirm exchanges. Depending on if answered yes or
no, "Call Confirm" or "Call No_Confirm" respectively.
Issues - Line "If InStr(1, Range("E2:E" & lrow).Text, "ExchangeIssue", 1) = 0 Then"
If there is any word in column E the message box will be displayed, NOT only when the word "ExchangeIssue" exists. If I clear all
contents in Column E, then the code will exit sub.
What I tried - I tried changing line to If InStr(1, Range("E2:E" & lrow).Value, "ExchangeIssue", 1) = 0 Then
but that only return a Run Time Error '13 - Type Mismatch. I think it has something to do with the range, but I need some
guidance
Code
Sub Exchange_Msg()
Dim lrow As Long
Sheets("Exchanges").Select
'find the last row
lrow = Range("E65536").End(xlUp).row
'select from E2 to the last used row
Range("E2:E" & lrow).Select
If InStr(1, Range("E2:E" & lrow).Text, "ExchangeIssue", 1) = 0 Then
Exit Sub
Else
MSG = MsgBox("Do you want to confirm exchanges?", vbYesNo, "Confirm Exchanges?")
If MSG = vbYes Then
Call Confirm
Else
Call No_Confirm
End If
End If
End Sub
End Code
Any assistance is greatly appreciated!
Thank you
Bookmarks