Excel VBA - Query Sheet for " " return MsgBox

[tmteast]

Hello, I am an IT Tech for a small business and rarely have to work with VBA. In this case I have spent about an hour reading and trying things and I am not getting the desired result. I am hoping this is a pretty basic request and am very thankful for the help.

I have a data set (Expenses) from our cloud accounting software (ParkStreet) that we are migrating to our on premise db (QB). I have a Macro that is reading a Table on another sheet. It Finds and Replaces things like Vendor Names, Accounts, Classes, etc. Making sure they are a perfect match before going into the On-Premise system.

I have a couple of queries I want to run after that initial operation is complete.

If
"Chargebacks" = found
Then
Prompt MsgMox "Chargebacks were found in this dataset. Review Data BEFORE importing."

If
"Other Distributor Charges" = found
Then
Prompt MsgMox "Other Dist Charges were found in this dataset. Review Data BEFORE importing."

Here is the code as it sits now:

Sub AccountsPStoQB()
'
' AccountsPStoQB Macro
'

'
   
    'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
'SOURCE: [URL='http://www.TheSpreadsheetGuru.com/the-code-vault']www.TheSpreadsheetGuru.com/the-code-vault[/URL]

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
  Set tbl = Worksheets("InventoryItems").ListObjects("Table2")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
 
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> tbl.Parent.Name Then
         
          sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
       
        End If
      Next sht
  Next x
 
    If Sheets
End Sub

 

[NdNoviceHlp]

Hi tmteast and Welcome to the Board! You can give this a trial. HTH. Dave

Sub test()
Dim Rng as Range, sht as Worksheet
For Each sht In ThisWorkbook.Sheets
If InStr(1, LCase(sht.UsedRange), LCase("Chargebacks"), 1) Then
For Each Rng In sht.UsedRange
If InStr(1, LCase(Rng.Text), LCase("chargebacks"), 1) Then
MsgBox "Chargebacks were found in this dataset." & vbCrLf & _
"Sheet: " & sht.Name & " Row:" & Rng.Row & " Column: " & Rng.Column
End If
'Exit For
Next Rng
'Exit For
End If
Next sht
End Sub

 

[tmteast]

Thanks for your help. I get an error when I run it. Screenshot attached.

 

[NdNoviceHlp]

My bad. I should have tested it more thoroughly. That code didn't like cells with numbers. This seems to work. Dave

Sub test()
Dim Rng As Range, sht As Worksheet
For Each sht In ThisWorkbook.Sheets
For Each Rng In sht.UsedRange
If InStr(1, LCase(Rng.Text), LCase("chargebacks"), 1) Then
MsgBox "Chargebacks were found in this dataset." & vbCrLf & _
"Sheet: " & sht.Name & " Row:" & Rng.Row & " Column: " & Rng.Column
End If
'Exit For
Next Rng
'Exit For
Next sht
End Sub

 

[tmteast]

This worked. Thank you for your support!

Truly helpful

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome. Be safe. Dave