CODEWITHSUNDEEP
excelvba
scamparella
scamparella

Reputation: 25

Show message box if value not found

A fantastic user has already helped me to develop a code to look up in sheet"Reqs" column B, a value the user has input into sheet "Home" G11 (a reference number), and then enter the current date & time into a cell in sheet"Reqs", 21 columns along from the found value.

Now I would like to modify the code, to both

a) enter the value from sheet"Home" G12 into the cell in sheet"Reqs" adjacent to the just-input date (one column along, same row).

and b) if the input reference number is not found, show a msgbox (in sheet"Home") saying "number not found"

For b) I tried to simply add an "Else: msgbox "Number not found", but this gives the error, "Else without If", but I already have an If...

Many thanks

Sub CloseJob()
    Dim temp As Range
    Set temp = 
Sheets("Reqs").Columns("B").Find(What:=Sheets("Home").Range("G11").Value, _
                       LookIn:=xlFormulas, LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, MatchCase:=True)
    'if found
    If Not temp Is Nothing Then temp.Offset(0, 21) = Date

There are other actions after this, but they work OK

Upvotes: 1

Views: 2635

Answers (2)

Dylan L
Dylan L

Reputation: 136

Without knowing the exact error, or seeing what you attempted I can't be entirely sure, but it may have came down to formatting of the syntax.

Give this a try:

If Not temp Is Nothing Then 
    temp.Offset(0, 21) = Date
Else
    MsgBox("Number Not Found")
End If

If you still have an error, let us know what the error is and we can go from there

For your first question go ahead and try something like this:

Sub Test()
Worksheets("Home").Cells(12, 7).Copy

'You mentioned 21 columns along from the FoundValue, so I added a variable to the column argument of the cells function
'that you can change as needed

Worksheets("Reqs").Cells(12, FoundValue + 21).Paste

End Sub

Keep in mind the Cells function takes an argument of Rows, Columns -- so make sure what I wrote is consistent with what you need. Always try on test data first!

Upvotes: 0

Imran Malek
Imran Malek

Reputation: 1719

You need to separate the part after Then in your code , currently it is ending the IF statement which is not what you want.

Put your code in following formatting and see if this works.

If Not temp Is Nothing Then 
  temp.Offset(0, 21) = Date
  temp.Offset(0, 22) = Sheets("Home").range("G12").value 

Else
...
End If

Upvotes: 0

Related Questions