Excel VB Prompt MsgBox If No Data Found

orion23 · Mar 17th 2016

Hi all excel guru,

Need some help as i am still learning excel vb, i have below vb code and try to copy data from sheet2 to sheet1 if the input value (C1) match the cell ranges, i would like to put a msgbox if no matching input value in C1, so how to do this and would appreciate your help.

Thank you.

------------------------------------------------------------------------

Code

Sub searchdata()
Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer




lastrow = Sheets("sheet2").Cells(Rows.count, 1).End(xlUp).Row




For x = 2 To lastrow
If Sheets("sheet2").Cells(x, 2) = Sheet1.Range("c1") Then
Sheet1.Range("d5") = Sheets("sheet2").Cells(x, 2)
Sheet1.Range("d6") = Sheets("sheet2").Cells(x, 3)
Sheet1.Range("f5") = Sheets("sheet2").Cells(x, 4)
Sheet1.Range("d7") = Sheets("sheet2").Cells(x, 13)
Sheet1.Range("d11") = Sheets("sheet2").Cells(x, 13)
Sheet1.Range("d12") = Sheets("sheet2").Cells(x, 13)








End If




Next x


end sub

Display More

**Luke M** · Mar 17th 2016

Re: Excel VB Prompt MsgBox If No Data Found

Try this?

Code

Sub searchdata()
Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer
Dim boolMissing As Boolean








lastrow = Sheets("sheet2").Cells(Rows.count, 1).End(xlUp).Row
'Set starting value
boolMissing = True




For x = 2 To lastrow
    If Sheets("sheet2").Cells(x, 2) = Sheet1.Range("c1") Then
    boolMissing = False
    Sheet1.Range("d5") = Sheets("sheet2").Cells(x, 2)
    Sheet1.Range("d6") = Sheets("sheet2").Cells(x, 3)
    Sheet1.Range("f5") = Sheets("sheet2").Cells(x, 4)
    Sheet1.Range("d7") = Sheets("sheet2").Cells(x, 13)
    Sheet1.Range("d11") = Sheets("sheet2").Cells(x, 13)
    Sheet1.Range("d12") = Sheets("sheet2").Cells(x, 13)
    
    End If




Next x




'Error check if needed
If boolMissing Then
    MsgBox "Couldn't find the value"
End If
End Sub

Display More

orion23 · Mar 18th 2016

Re: Excel VB Prompt MsgBox If No Data Found

Hi Luke,

Thank you so much, it work!

Excel VB Prompt MsgBox If No Data Found

Participate now!

Share