(Solved) – VBA find and replace row entry

  • by

I’m trying to write a function that basically does the following:

  1. Grabs a “name” cell value from workbook #1
  2. Grabs an “amount” cell value from workbook #1
  3. Opens 2nd workbook (which is a 2 column list where columnA = “Names” and columnB = “Amounts”)
  4. Scans column A of 2nd workbook for a duplicate “name” entry (from step 1)
  5. If a duplicate exists, overwrites the corresponding “amount” value in column B with the value stored from step 2
  6. If no duplicate exists, appends a new last row to the list
  7. Writes “name” in last row of column A of 2nd workbook
  8. 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")

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


Leave a Reply

Your email address will not be published. Required fields are marked *