VBA to mark items in a different workbook as paid

[tonywatsonhelp]

Hi Everyone,

Bit stuck with this,

please help.

I have two workbooks

1 called "Sales Doc" Sheet is "Invoices"

the other I need to only call this workbook sheet "Raw Data"

All I want is a macro called "Mark_AS_Paid"

that when I run, takes all the Invoice numbers from "This workbook" sheet "Raw Data" Column A

Finds them in Workbook "Sales Doc" Sheet "Invoices" Column A and marks that row in column T as "Yes" and Column U as "Today"

I'm struggling as there are different workbooks and I don't know how many Rows of Data there will be in "Invoices"

please help

Tony

 

[gallen]

Can the invoice number from "raw data" be found more than once on "Invoices"?

 

[BarryL]

try the below. it assumes headers on both and a max of 10,000 rows of data. both can be changed easily

Sub Mark_As_Paid()
Dim a, i, b(1 To 10000)
Dim lr, lr2 As Long, wb, book As Workbook, l As Long
Set wb = ThisWorkbook
Set book = Workbooks("Book14.xlsm")
Let lr = wb.Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row
Let lr2 = book.Sheets("Invoices").Range("A" & Rows.Count).End(xlUp).Row
a = wb.Sheets("Raw Data").Range("A2:A" & lr)
For i = LBound(a) To UBound(a)
b(i) = a(i, 1)
Debug.Print b(i)
Next
For l = 2 To lr2
For i = LBound(a) To UBound(a)
If book.Sheets("Invoices").Range("A" & l) = b(i) Then book.Sheets("Invoices").Range("B" & l) = "Paid"
Next
Next
End Sub

 

[gallen]

As I spent the time writing it:

Sub Mark_As_Paid()
    'This sub only works if Sales Doc is already open. Not too difficult to open it if not. Let me know if you need that
    
    'Always easier to define as variables
    Dim wsInvoices As Worksheet, wsRawData As Worksheet
    Dim c As Range, rFind As Range, rInvoices As Range
    
    'Set the worksheet variables
    
    Set wsRawData = ThisWorkbook.Worksheets("Raw Data")
    Set wsInvoices = Workbooks("Sales Doc.xlsx").Worksheets("Invoices")
    
    'Assuming invoices start on row2
    Set rInvoices = wsInvoices.Range(wsInvoices.Range("A2"), wsInvoices.Range("A" & Rows.Count).End(xlUp))
    
    'loop through all cells in col A starting at row 2 (change where needed)
    For Each c In wsRawData.Range(wsRawData.Range("A2"), wsRawData.Range("A" & Rows.Count).End(xlUp))
        Set rFind = rInvoices.Find(c)
        If Not rFind Is Nothing Then
            wsInvoices.Cells(rFind.Row, 20) = "Yes"
            wsInvoices.Cells(rFind.Row, 21) = "Today"
        End If
    Next c
    
    'Tidy Up
    Set rFind = Nothing
    Set rInvoices = Nothing
    Set wsRawData = Nothing
    Set wsInvoices = Nothing
End Sub

 

[tonywatsonhelp]

Thank you BarryL
That will do just nicely