Run Macro When Cell Value Changes or Range VBA

[Dokat]

Hi,

I am trying to run a vlookup macro when i copy paste new values on the A2:A3000, it runs vlookup macro and and updates the values.

Here is worksheet Change event code i am using

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B3000")) Is Nothing Then
Call Mymacro
End If
End Sub

Here is the code i am using for vlookup but getting "Compile Error: Ambiguous name detected: BuildLookupCollection" error.

Not sure what's causing the error. Attached is the sample workseet.

Sub UpdateValues()

    OptimizeVBA True
    Dim startTime As Single, endTime As Single
    startTime = Timer
    Dim sWb As Workbook
    Dim fWs As Worksheet, sWs As Worksheet
    Dim slRow As Long, flRow As Long
    Dim pSKU As Range, luVal As Range
    Dim lupSKU As Range, outputCol As Range
    Dim vlookupCol As Object
    Set sWb = ActiveWorkbook
    Set sWs = sWb.Sheets("Source Data")
    Set fWs = ThisWorkbook.Sheets("Source Data")
    slRow = sWs.Cells(Rows.Count, 4).End(xlUp).Row
    flRow = fWs.Cells(Rows.Count, 4).End(xlUp).Row
    Set pSKU = sWs.Range("A2:A" & slRow)
    Set lupSKU = fWs.Range("P2:P" & flRow)
    For i = 1 To 1
        Set outputCol = fWs.Range(fWs.Cells(2, i), fWs.Cells(flRow, i))
        Select Case i
            Case 1
                Set luVal = sWs.Range("Q2:Q" & slRow)
   
        End Select
    'Build Collection
       Set vlookupCol = BuildLookupCollection(pSKU, luVal)
    'Lookup the values
       VLookupValues lupSKU, outputCol, vlookupCol
    Next i
    endTime = Timer
    Debug.Print (endTime - startTime) & " seconds have passed [VBA]"
    OptimizeVBA False
    sWb.Close False
    Set vlookupCol = Nothing
End Sub
Function BuildLookupCollection(categories As Range, values As Range)
    Dim vlookupCol As Object, i As Long
    Set vlookupCol = CreateObject("Scripting.Dictionary")
    For i = 1 To categories.Rows.Count
        vlookupCol.Item(CStr(categories(i))) = values(i)
    Next i
    Set BuildLookupCollection = vlookupCol
End Function
Sub VLookupValues(lookupCategory As Range, lookupValues As Range, vlookupCol As Object)
    Dim i As Long, resArr() As Variant
    ReDim resArr(lookupCategory.Rows.Count, 1)
    For i = 1 To lookupCategory.Rows.Count
        resArr(i - 1, 0) = vlookupCol.Item(CStr(lookupCategory(i)))
    Next i
    lookupValues = resArr
End Sub
Sub OptimizeVBA(isOn As Boolean)
    Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not (isOn)
    Application.ScreenUpdating = Not (isOn)
    ActiveSheet.DisplayPageBreaks = Not (isOn)
End Sub

Can someone help me with the error.

 

[GraH - Guido]

Hi Dokat, the error is rather explicit. You have two function named "BuildLookupCollection". I haven't looked, but if they are the same, delete one. If they are different, then you need to rename one. And be sure to call the functions in a correct way in your subs.