VBA - If Nothing Found, then Put Text into the cell

[vbanoob1234]

Hello everyone,

I have a macro right now, that has a "Master" worksheet, and several Accounts worksheets. It currently finds the word "Dividend" and "Interest" in the Accounts worksheets. Then it will find the amount of the Dividend or Interest, and place the amount/# in the Master worksheet, with the corresponding account number respectively.

However, what if there is no amount found. Is there a way I can make my code smarter, and say "nothing was found" on the Master sheet, instead of leaving it blank

I have a code so far like this:

Sub GrabData()


Dim master As Worksheet
Dim account As Worksheet
Dim divd As Range
Dim int As Range



Set master = Sheets("Master") 'Master sheetname

For Each c In master.Range("A2:A" & Range("A" & master.Rows.Count).End(xlUp).Row) 'range of account names on master, adjust to suit

On Error Resume Next
Set account = Worksheets(c.Text)

On Error GoTo 0
If Not account Is Nothing Then

With account.Range("A1:A" & account.Range("A" & account.Rows.Count).End(xlUp).Row)
Set divd = .Find("Dividend", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
Set int = .Find("Interest", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)


End With
If Not divd Is Nothing Then c.Offset(, 1) = cdivd.Offset(, 1)
If Not int Is Nothing Then c.Offset(, 2) = fdivd.Offset(, 1)

Else
MsgBox "Worksheet for account " & c.Value & " doesn't exist"
End If
Set account = Nothing
Next
End Sub

 

[BQardi]

Try this:

Sub GrabData()

Dim master As Worksheet
Dim account As Worksheet
Dim divd As Range
Dim int As Range

Set master = Sheets("Master") 'Master sheetname

For Each c In master.Range("A2:A" & Range("A" & master.Rows.Count).End(xlUp).Row) 'range of account names on master, adjust to suit

On Error Resume Next
Set account = Worksheets(c.Text)

On Error GoTo 0
If Not account Is Nothing Then

With account.Range("A1:A" & account.Range("A" & account.Rows.Count).End(xlUp).Row)

[COLOR="#FF0000"]On Error Resume Next[/COLOR]
Set divd = .Find("Dividend", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
Set int = .Find("Interest", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
[COLOR="#FF0000"]On Error GoTo 0[/COLOR]

End With
If Not divd Is Nothing Then
    c.Offset(, 1) = cdivd.Offset(, 1) [COLOR="#008000"]'Shouldn't "cdivd" be "divd"?[/COLOR]
[COLOR="#FF0000"]Else
    c.Offset(, 1) = "nothing was found"
End If[/COLOR]
If Not int Is Nothing Then
    c.Offset(, 2) = fdivd.Offset(, 1)[COLOR="#008000"] 'Shouldn't "fdivd" be "int"?[/COLOR]
[COLOR="#FF0000"]Else
    c.Offset(, 2) = "nothing was found"
End If[/COLOR]

Else
MsgBox "Worksheet for account " & c.Value & " doesn't exist"
End If
Set account = Nothing
Next
End Sub