VBA Help - match column values and update column if matched

[ianawwalker]

Hello,

I have a data set that I need to update every month or bi-monthly and I am attempting to create a vba to have this data updated. Essentially, I have other VBA that puts my data into a worksheet ("US_CBtoRecords") and if the column value is within this worksheet i need to update my other worksheet ("Records") to reflect "Y" in column AH. From researching and looking online i've created a vba that i believe should work to help me find the match and then update the column, but continue to get an error showing that my subscript is out of range. I'm attempting to but both worksheets into arrays, which is my first time doing this, and then only change the value in column AH for the corresponding match. Below is my vba, any help is much appreciated!

Sub testing2()
'testing sub


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Dim i As Long
Dim j As Long
Dim LRow1 As Long
Dim LRow2 As Long
Dim varray As Variant
Dim varray2 As Variant

LRow1 = Worksheets("Records").Range("A" & Rows.Count).End(xlUp).Row
LRow2 = Worksheets("US_CBtoRecords").Range("A" & Rows.Count).End(xlUp).Row
varray = Application.Transpose(Worksheets("Records").Range("A2:A" & LRow1).Value)
varray2 = Application.Transpose(Worksheets("US_CBtoRecords").Range("A2:A" & LRow2).Value)

For i = 1 To LRow1
    For j = 1 To LRow2
        If varray(i) = varray2(j) Then
            varray(i) = "Y"
            Exit For
        End If
    Next j
Next i

Worksheets("Records").Range("AH2:AH" & LRow1).Value = Application.Transpose(varray)
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[ianawwalker]

For anyone that is looking for this, i was able to find an answer that works for me. i needed to add in other columns to update, which is why here are 3 sections.

Sub testing2()
'testing sub


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    Dim wsRecords As Worksheet
    Dim wsUS_CBtoRecords As Worksheet
    Dim i As Long, j As Long
    Dim LastRowRecords As Long
    Dim LastRowUS_CBtoRecords As Long
    Dim RecordsData As Variant
    Dim US_CBtoRecordsData As Variant

    ' Set references to worksheets
    Set wsRecords = ThisWorkbook.Worksheets("Records")
    Set wsUS_CBtoRecords = ThisWorkbook.Worksheets("US_CBtoRecords")

    ' Find the last row in each worksheet
    LastRowRecords = wsRecords.Cells(wsRecords.Rows.Count, "A").End(xlUp).Row
    LastRowUS_CBtoRecords = wsUS_CBtoRecords.Cells(wsUS_CBtoRecords.Rows.Count, "A").End(xlUp).Row

    ' Load data from "Records" and "US_CBtoRecords" into arrays
    RecordsData = wsRecords.Range("A2:AM" & LastRowRecords).Value
    US_CBtoRecordsData = wsUS_CBtoRecords.Range("A2:G" & LastRowUS_CBtoRecords).Value

    ' Loop through each row in "Records" array
    For i = 1 To UBound(RecordsData, 1)
        ' Get the value in column A of "Records"
        Dim recordValue As Variant
        recordValue = RecordsData(i, 1)

        ' Loop through each row in "US_CBtoRecords" array
        For j = 1 To UBound(US_CBtoRecordsData, 1)
            ' Check for matches in columns C, E, and G of "US_CBtoRecords"
            If recordValue = US_CBtoRecordsData(j, 3) Then
                RecordsData(i, 19) = "Y" ' Column S is the 19th column
            ElseIf recordValue = US_CBtoRecordsData(j, 5) Then
                RecordsData(i, 39) = "Y" ' Column AM is the 39th column
            ElseIf recordValue = US_CBtoRecordsData(j, 7) Then
                RecordsData(i, 39) = "Y" ' Column AM is the 39th column
            End If
        Next j
    Next i

    ' Write the updated data back to the "Records" worksheet
    wsRecords.Range("A2:AM" & LastRowRecords).Value = RecordsData

 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub