Using a partial value in a cell to find the real expense account in another tab

[gerdgoebel]

Hi all, I need your help again.

I already learned lots from this forum, but my new "search and copy" function I have not figured out how to do in VBA.

I have an excel with tab CashExpenses and tab ExpenseAccounts.

In tab Expenses I paste my cash expenses in columns A-D. Next I run a VBA to copy values A to H, B to G, and D to K. Columns F and I, I default to a fixed value.

Here is my challenge. in column C I have a short version of my expense type for which I need to find the associated Expense account in tab ExpenseAccounts column A, then copy the full expense account back to tab CashExpenses column J

Here is an example:
In row 2 I have expense type "marketing" for which I would need to find and copy "Business Expenses:Marketing Expense" from tab ExpenseAccounts.

If an expense account is not found in tab ExpenseAccounts I would like to highlight the cell in column J with background color red (or copy text "not found" in color red)

I would like to use this excel to import into my QuickBooks app.

Thanks for your help.
Gerd

[Marc L]

According to your attachment a VBA demonstration for starters :

Sub Demo1() 
    With Sheet1.Range("J2:J" & Sheet1.[A1].CurrentRegion.Rows.Count) 
        .Formula = Replace("=IFERROR(INDEX(#,MATCH(""*""&C2&""*"",#,0)),)", "#", Sheet2.[A1].CurrentRegion.Address(, , , True)) 
        .Formula = .Value2 
         If Application.Count(.Value2) Then .SpecialCells(2, 1).Interior.Color = vbRed: .Replace 0, Empty, 1 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[gerdgoebel]

Thanks for your code, Make L

I ran into several problems when trying to incorporate it into my existing VBA.

If I comment all my VBA lines, rename the 2 tabs in the excel to Sheet1 and Sheet2 your code works as expected.

However, when I try to replace tab names sheet1 and sheet2 to CashExpenses and ExpenseAccounts your code triggers a runtime error 424 "Object required".

I changed my top when loop to also use Sheet1 and now it works with your code. But is there a reason why it does not work with the tabs CashExpenses and ExpenseAccounts?

Also, could I add your code within my top "when", either before or after I do the sorting?

Here is the code that works

Sub J3v10()
Dim Rw, Order As String, i As Long

'With Sheets("CashExpenses")
With Sheets("Sheet1")
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1

' Set fix columns
.Range("F" & i).Value = "Cash"
.Range("I" & i).Value = "CHK"
.Range("H" & i) = .Range("A" & i).Value
.Range("H" & i).NumberFormat = "mm/dd/yyyy"

.Range("K" & i) = .Range("D" & i).Value
.Range("K" & i).Font.Bold = True

' Copy Payee and apply formula
.Range("G" & i).Formula = "=PROPER(B" & i & ")"
.Range("G" & i).Font.Bold = True

Next i

' Sort by Date and time
Columns.Sort key1:=Columns("A"), Order1:=xlAscending, Header:=xlYes

End With

' With CashExpenses.Range("J2:J" & CashExpenses.[A1].CurrentRegion.Rows.Count)
' .Formula = Replace("=IFERROR(INDEX(#,MATCH(""*""&C2&""*"",#,0)),)", "#", ExpenseAccounts.[A1].CurrentRegion.Address(, , , True))
' .Formula = .Value2
' If Application.Count(.Value2) Then .SpecialCells(10, 1).Interior.Color = vbRed: .Replace 0, Empty, 1
' If Application.Count(.Value2) Then .Replace 0, Empty, 1
' End With

With Sheet1.Range("J2:J" & Sheet1.[A1].CurrentRegion.Rows.Count)
.Formula = Replace("=IFERROR(INDEX(#,MATCH(""*""&C2&""*"",#,0)),)", "#", Sheet2.[A1].CurrentRegion.Address(, , , True))
.Formula = .Value2
'If Application.Count(.Value2) Then .SpecialCells(2, 1).Interior.Color = vbRed: .Replace 0, Empty, 1
If Application.Count(.Value2) Then .Replace 0, Empty, 1

End With

End Sub

[Marc L]

It's what happens when the attachment does not well reflect the real workbook
so processing like this means you are enough confident with your Excel / VBA skills to fit any code !

As I wrote « According to your attachment » my VBA demonstration does obviously not use sheets names
but sheets CodeNames then you can easily replace each CodeName with a valid worksheet reference
so the reason why your attempt couldn't work 'cause you are confusing CodeName and worksheet name …

[gerdgoebel]

I apologize if I did something wrong. I had attached my Expenses.xlsm that I use for my QuickBooks import. It should have included the VBA I had added. So, what is wrong with my attachment? Just that I can get it corrected the next time I ask for help.

And I still don't understand what I do wrong. I changed your portion of the VBA to

'With Sheets("CashExpenses")
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1

' Set fix columns
.Range("F" & i).Value = "Cash"
.Range("I" & i).Value = "CHK"
.Range("H" & i) = .Range("A" & i).Value
.Range("H" & i).NumberFormat = "mm/dd/yyyy"

.Range("K" & i) = .Range("D" & i).Value
.Range("K" & i).Font.Bold = True

' Copy Payee and apply formula
.Range("G" & i).Formula = "=PROPER(B" & i & ")"
.Range("G" & i).Font.Bold = True

Next i

' Sort by Date and time
Columns.Sort key1:=Columns("A"), Order1:=xlAscending, Header:=xlYes

End With

With Sheets("CashExpenses").Range("J2:J" & Sheets("CashExpenses").[A1].CurrentRegion.Rows.Count)
.Formula = Replace("=IFERROR(INDEX(#,MATCH(""*""&C2&""*"",#,0)),)", "#", Sheets("ExpenseAccounts").[A1].CurrentRegion.Address(, , , True))
.Formula = .Value2
' If Application.Count(.Value2) Then .SpecialCells(10, 1).Interior.Color = vbRed: .Replace 0, Empty, 1
If Application.Count(.Value2) Then .Replace 0, Empty, 1
End With

But I still get an error. In other VBA I got from the forum I had used Sheets("xxxx").something, thus thought I have to use the same. Apparently I was wrong.

Could you let me know what I have to change?

And can I get this code moved to either before or after the sort?

[Marc L]

Which error on which codeline ? Without any information it's just a guessing challenge but not for any Excel forum …

You are not wrong if the worksheet reference is valid like you can see within your attachment
on VBE side Sheet2 is the codename of ExpenseAccounts worksheet …

So when the VBA procedure is located within the workbook containing some specific sheets
then using their CodeName is 'better' than using their name 'cause when you rename a sheet
you must amend the name within the VBA procedure but it's useless when using its CodeName
'cause it does not change when the sheet is renamed …