I’m trying to write a function that basically does the following:
- Grabs a “name” cell value from workbook #1
- Grabs an “amount” cell value from workbook #1
- Opens 2nd workbook (which is a 2 column list where columnA = “Names” and columnB = “Amounts”)
- Scans column A of 2nd workbook for a duplicate “name” entry (from step 1)
- If a duplicate exists, overwrites the corresponding “amount” value in column B with the value stored from step 2
- If no duplicate exists, appends a new last row to the list
- Writes “name” in last row of column A of 2nd workbook
- Writes “amount” in last row of column B of 2nd workbook
Essentially, this function will update a list of names with their corresponding amounts. If a name already exists on the list, this script will find the duplicate entry in column A, and update the associated amount in column B of the same row. If a name doesn’t exist on this list, the function will add a new row to the list and then write the stored “name” value in column A, and the stored “amount” value in column B.
I’ve got steps 1-3 working but I’m having some trouble creating the loops for 4-8. Here’s what I currently have for code… any help would be appreciated!
Sub Open_Updator() Dim proj_name As String Dim amount As Double Dim updator As Workbook Dim updator_sheet As Worksheet Dim LastRow As Long Dim arr() As Variant Dim R As Long Dim C As Long Dim Destination As Range proj_name_raw = ThisWorkbook.Sheets("Cover").Range("C7").Value proj_name = Left(proj_name_raw, Len(proj_name_raw) - 3) amount = ThisWorkbook.Sheets("Summary").Range("O110").Value Set updator = Workbooks.Open(Filename:="C:UsersXXXXXDesktopupdator.xlsx") updator.Activate Set updator_sheet = Sheets("Table") LastRow = Cells(Rows.Count, "A").End(xlUp).Row arr = Range("A1:B" & LastRow) Set Destination = Range("A1") For R = 1 To UBound(arr, 1) For C = 1 To UBound(arr, 2) If arr(R, C) = proj_name Then arr(R, C) = arr(R, 1) Next C Next R Destination.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr End Sub