Message box for error

[Edgarvelez]

Hi,

Very new to VBA and I have a code that works great.
All it really does is selects the value of a cell in sheet one goes to another sheet finds it and copies the cell to the right of it and then goes on and paste the the vale to another sheet.
If it does not find this specific value then I get "Run-time error "91": Object variable or With block variable not set" which is ok because it could not find what it was looking for.
I would like to add a message box so it can display "Description Code Not Found Add to Database"
and I would like to reset the macro back to the beginning.

sh1.Range("D21").Select
rngY = ActiveCell.Value
Sheets(Range("D7").Value).Select
Columns("A:A").Select
Selection.Find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Activate
ActiveCell.Copy
sh2.Cells(Rows.Count, 12).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
sh2.Cells(Rows.Count, 26).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
sh2.Cells(Rows.Count, 18).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
sh2.Cells(Rows.Count, 25).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
Application.CutCopyMode = False

 

[dmt32]

Hi,

Try following update to your code & see if helps you

Dim foundcell As Range
    
    rngY = ActiveCell.Value
    
    Set foundcell = Columns("A:A").Find(What:=rngY, LookIn:=xlFormulas, LookAt:=xlWhole, _
                                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                        MatchCase:=False, SearchFormat:=False)
    
    If Not foundcell Is Nothing Then
        foundcell.Offset(0, 1).Copy
        sh2.Cells(Rows.Count, 12).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
        sh2.Cells(Rows.Count, 26).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
        sh2.Cells(Rows.Count, 18).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
        sh2.Cells(Rows.Count, 25).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Else
        MsgBox "Description Code " & rngY & Chr(10) & "Not Found Add to Database", 48, "Record Not Found"
    End If

Dave

 

[Edgarvelez]

Hi
Do you mean like below, keeping the code line for D21 & D7?


Dim foundcell As Range
sh1.Range("D21").Select
rngY = ActiveCell.Value
Sheets(Range("D7").Value).Select
Set foundcell = Columns("A:A").Find(What:=rngY, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not foundcell Is Nothing Then
foundcell.Offset(0, 1).Copy
sh2.Cells(Rows.Count, 12).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
sh2.Cells(Rows.Count, 26).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
sh2.Cells(Rows.Count, 18).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
sh2.Cells(Rows.Count, 25).End(xlUp)(2).Resize(sh1.Range("D28").Value).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
MsgBox "Description Code " & rngY & Chr(10) & "Not Found Add to Database", 48, "Record Not Found"
End If

Or can we do this to shorten it up sine we are really not going to copy and paste anything until way further down the code and this is only to check that what it is searching for is available.

Dim foundcell As Range
sh1.Range("D21").Select
rngY = ActiveCell.Value
Sheets(Range("D7").Value).Select
Set foundcell = Columns("A:A").Find(What:=rngY, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not foundcell Is Nothing Then
Else
MsgBox "Description Code " & rngY & Chr(10) & "Not Found Add to Database", 48, "Record Not Found"
End If

 

[dmt32]

Hi,
you don't need to use select in the code

rngY = sh1.Range("D21").Value

Solution just answered resolving the error 91 issue you requested help with and then addition of msgbox displaying if search value not found which you should be able to adapt for your project. If need further guidance, helpful if you post all the code plus copy of of your worksheet using MrExcels XL2BB - Excel Range to BBCode addin with sample data and an explanation of outcome required.

Dave

 

[Edgarvelez]

Thank you Dave, it worked.

Edgar

 

[dmt32]

Thank you Dave, it worked.

Edgar

welcome - thanks for feedback

Dave