Vlookup VBA Circular Reference Problem

[Dokat]

I have below VBA code however its causing circular reference and not returning error message.

Is there a way around to avoid circular reference in VBA Code

Sub UpdateValues()

    OptimizeVBA True
   Dim startTime AsSingle, endTime AsSingle
    startTime = Timer
   Dim sWb As Workbook
   Dim fWs As Worksheet, sWs As Worksheet
   Dim slRow AsLong, flRow AsLong
   Dim pSKU As Range, luVal As Range
   Dim lupSKU As Range, outputCol As Range
   Dim vlookupCol AsObject
   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))
       SelectCase i
           Case 1
               Set luVal = sWs.Range("Q2:Q" & slRow)
 
       EndSelect
   '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.CloseFalse
   Set vlookupCol = Nothing
End Sub
Function BuildLookupCollection(categories As Range, values As Range)
   Dim vlookupCol AsObject, i AsLong
   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 AsLong, resArr() AsVariant
   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

 

[NARAYANK991]

Hi ,

Can you upload your workbook with data and this code in it ?

Narayan

 

[Dokat]

Hi,

Please see attached. Anytime i copy/paste new values on table Source Data!A1:M2067 i want vlookup macro to run simultaneously update column A thru C with vlookup values in column Q,S,V.

Thanks

 

[Dokat]

Sorry just realized i forgot to upload the file. Please see attached

 

[NARAYANK991]

Hi ,

When is the circular reference message displayed ?

Narayan

 

[Dokat]

I get Compile Error: Variable Nut Defined error assuming its due to circular reference.

 

[NARAYANK991]

Hi ,

A compile error , which says :

Variable not defined

is because a VBA variable has not been declared using the Dim statement.

This is a normal error when you have :

Option Explicit

in your module.

The problem was that your For ... Next loop used i as the counter variable , and this had not been declared.

If you include a statement :

Dim i As Long

this error will be taken care of.

In the attached file , I have removed the For ... Next loop , since a loop which is executed only once is an unnecessary loop.

Narayan

 

[Dokat]

Thanks Narayan...Vlookup runs but returns blank value for Column A. It should have returned vlookup values in column Q.

 

[Dokat]

i changed Psku and Lupsku range and it worked. Thanks for your help