Member Avatar for methuselah90

I have 2 spreadsheets - uat.xlsx and main.xlsx - both in the same directory.

main.xlsx

[Click Here](null)

uat.xlsx

Click Here

I am trying to copy across information from uat.xlsx to main.xlsx, by accomplishing the following in VBA (the VBA code will be stored on main.xlsx and launched by button click):

If column A in main.xlsx matches column B in uat.xlsx, then check to see if there is anything in column J-Lin main.xlsx matches anything in column A on uat.xlsx for the user found, if these two conditions are met then:

on the matching row in main.xlsx, update column M on main.xlsx with "Yes"

once this is done, grab the first 10 characters of column D in uat.xlsx, then on the same matching row on main.xlsx, update column Q with the values

once this is done, check column C in uat.xlsx, if the value is "I already have", then on the same matching row on main.xlsx, update column N with "Physical", if the value is "Required", then on the same matching row on main.xlsx, , update column N with "Virtual"

This only needs to happen once per match - ignore any duplicate matches on uat.xlsx (i.e. on a new row)

Secondly - I need to eliminate any differences in "case" by making standardising all the values checked to either lowercase/uppercase.

How would I articulate this as VBA code?

  • 2 Contributors
  • 1 Reply
  • 265 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by Stuugie

Dani AI

Generated

Quick plan for (and in reply to ): a VLookup-style formula can work for simple one-to-one matches, but when you must check multiple columns per row, ignore duplicates, normalize case and write results back, a short VBA routine is more reliable. The macro below assumes the code lives in main.xlsx, uat.xlsx is in the same folder (or already open), both sheets have headers in row 1, and comparisons are case-insensitive with leading/trailing spaces trimmed.

Algorithm in plain terms: scan uat.xlsx and build a case-insensitive map from the user key (uat column B) to (a) the first uat row found for that user (to grab D and C) and (b) a set of that user’s uat column A values. Then loop main.xlsx rows; for each main row where A matches a uat user, test columns J–L against that user’s set of uat A values. On the first hit set M = "Yes", Q = first 10 chars of uat.D, and N = "Physical" or "Virtual" depending on uat.C. The code uses late-bound Scripting.Dictionary so no reference is required.

Option Explicit

Sub MergeUATtoMain()
    Dim wbMain As Workbook, wbUAT As Workbook
    Dim wsMain As Worksheet, wsUAT As Worksheet
    Dim dictFirst As Object, dictAvals As Object, innerDict As Object
    Dim lastMain As Long, lastUAT As Long
    Dim i As Long, j As Long, firstRowIdx As Long
    Dim userKey As String, val As String, cellVal As String
    Dim uatPath As String

    On Error GoTo Cleanup
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set wbMain = ThisWorkbook
    Set wsMain = wbMain.ActiveSheet           ' change to specific sheet if needed

    On Error Resume Next
    Set wbUAT = Workbooks("uat.xlsx")
    On Error GoTo 0
    If wbUAT Is Nothing Then
        If wbMain.Path = "" Then
            MsgBox "Save main workbook first so macro can find uat.xlsx", vbExclamation: GoTo Cleanup
        End If
        uatPath = wbMain.Path & "\uat.xlsx"
        If Dir(uatPath) = "" Then MsgBox "Cannot find uat.xlsx in folder.", vbExclamation: GoTo Cleanup
        Set wbUAT = Workbooks.Open(uatPath)
    End If

    Set wsUAT = wbUAT.ActiveSheet            ' change to correct sheet name if needed

    Set dictFirst = CreateObject("Scripting.Dictionary")
    Set dictAvals = CreateObject("Scripting.Dictionary")

    lastUAT = wsUAT.Cells(wsUAT.Rows.Count, "B").End(xlUp).Row
    For i = 2 To lastUAT
        userKey = UCase(Trim(CStr(wsUAT.Cells(i, "B").Value)))
        If Len(userKey) > 0 Then
            If Not dictFirst.Exists(userKey) Then dictFirst.Add userKey, i
            If Not dictAvals.Exists(userKey) Then
                Set innerDict = CreateObject("Scripting.Dictionary")
                dictAvals.Add userKey, innerDict
            Else
                Set innerDict = dictAvals(userKey)
            End If
            cellVal = UCase(Trim(CStr(wsUAT.Cells(i, "A").Value)))
            If Len(cellVal) > 0 Then If Not innerDict.Exists(cellVal) Then innerDict.Add cellVal, True
        End If
    Next i

    lastMain = wsMain.Cells(wsMain.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastMain
        userKey = UCase(Trim(CStr(wsMain.Cells(i, "A").Value)))
        If dictAvals.Exists(userKey) Then
            Set innerDict = dictAvals(userKey)
            For j = 10 To 12   ' J to L
                val = UCase(Trim(CStr(wsMain.Cells(i, j).Value)))
                If Len(val) > 0 Then
                    If innerDict.Exists(val) Then
                        firstRowIdx = dictFirst(userKey)
                        wsMain.Cells(i, "M").Value = "Yes"
                        wsMain.Cells(i, "Q").Value = Left(CStr(wsUAT.Cells(firstRowIdx, "D").Value), 10)
                        Select Case UCase(Trim(CStr(wsUAT.Cells(firstRowIdx, "C").Value)))
                            Case "I ALREADY HAVE": wsMain.Cells(i, "N").Value = "Physical"
                            Case "REQUIRED": wsMain.Cells(i, "N").Value = "Virtual"
                        End Select
                        Exit For
                    End If
                End If
            Next j
        End If
    Next i

Cleanup:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
End Sub

Quick tips: test on a copy of the files first. If your data sits on named sheets, replace ActiveSheet with the exact worksheet names. If performance becomes an issue for very large files, load ranges into arrays instead of reading cells repeatedly. Back up before running and make sure macros are enabled.

Member Avatar for Stuugie

Have you tried a VLookup? It's not VBA but whenever someone wants to find matches, that's the first thing I think of.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.