I have two main functions, the first is search_bank. It searches through the Credits, Type, and store columns, cell by cell and figures out if we have a match or not.If there is a match, it returns True and as a side effect changes the color of the matched cell.
The second sub I'm using to test the first function. The problem I'm having is that I'm getting a Run time error '424': Object required with no indication of where the issue is.
Here is the first function:
Function search_bank(Store As String, amount As Double, Amex As Boolean) As Boolean
Dim m_store As Range
Dim m_type As Range
Dim Credit_Amt_Col As Range
Set m_store = bank_sheet.Range("1:1").Find("M_STORE")
Set m_type = bank_sheet.Range("1:1").Find("M_TYPE")
Set Credit_Amt_Col = bank_sheet.Range("1:1").Find("Credit Amt")
search_bank = False
Dim i As Long
For i = 1 To 9000
If Not search_bank Then
Dim store_cell As Range
Dim type_cell As Range
Dim credit_cell As Range
Set store_cell = Worksheets(2).Cells(i, m_store.Column)
Set type_cell = Worksheets(2).Cells(i, m_type.Column)
Set credit_cell = Worksheets(2).Cells(i, Credit_Amt_Col.Column)
If InStr(UCase(store_cell.Value), UCase(Store)) > 0 And credit_cell.Value = amount Then
If store_cell.Interior.ColorIndex <> 46 Then
If Amex And InStr(UCase(type_cell.Value), UCase("amex deposit")) Then
store_cell.Interior.ColorIndex = 46
search_bank = True
End If
If Not Amex And InStr(UCase(type_cell.Value), UCase("Credit Card Deposit")) Then
store_cell.Interior.ColorIndex = 46
search_bank = True
End If
End If
End If
End If
Next i
End Function
and here is the tester:
Sub Tester()
Dim x As Boolean
x = search_bank("ctc", 38.4, True)
Debug.Print (x)
End Sub
I have tried using 'set' on the tester:
Sub Tester()
Dim x As Boolean
Set x = search_bank("ctc", 38.4, True)
Debug.Print (x)
End Sub
And even declaring the variable before passing them in the tester (I'm not very used to VBA but for a moment I believed it was just so ancient, it needed things to be declared before they're passed)
Sub Tester()
Dim x As Boolean
Dim store As String
Dim Amount As Double
Dim amex As Boolean
store = "ctc"
Amount = 38.4
amex = True
x = search_bank(store, Amount, amex)
Debug.Print (x)
End Sub
There's lots of good commentary under your OP, and with @BrandonBarney's answer too, but here's my two cents:
Cent one: The biggest thing I see is you never declare blank_sheet yet try to use it while setting a range object. This is where your error is coming from. It's looking to do Range("1:1").Find("M_STORE"), but doesn't know what bank_sheet is.
Cent two: A quick way to have this pointed out to you is to always use Option Explicit at the top of your code. That ensures that any variable you use is explicitly declared. I.e.:
Option Explicit
Function search_bank(Store As String, amount As Double, Amex As Boolean) As Boolean
Dim m_store As Range
Dim m_type As Range
Dim Credit_Amt_Col As Range
''''' New code here: ''''''
Dim bank_sheet as Worksheet
Set bank_sheet = Worksheets("Bank Sheet") ' change to whatever the name is.
'''''''''''''''''''''''''''
Set m_store = bank_sheet.Range("1:1").Find("M_STORE")
Set m_type = bank_sheet.Range("1:1").Find("M_TYPE")
Set Credit_Amt_Col = bank_sheet.Range("1:1").Find("Credit Amt")
' etc. etc.
Option Explicit will also help if you ever accidentally have a typo. So if you ever did bank_sheeet.Range("A:A") it'll error out and ask you to declare bank_sheeet. Or, of course, you'll realize it's a typo and then just fix it.
Bonus cent: You can save a few lines by combining your Dims:
Dim m_store as Range, m_type as Range, Credit_Amt_Col as Range can all be on one line.
(Note: Doing Dim m_store, m_type, Credit_Amt_Col as Range will not set all three to Range type. It'll make m_store and m_type a Variant since it's not declared. Only Credit_Amt_Col would be a Range in that case. So you still have to explicitly state the type for each variable).