Insert a Vlookup into a VBA at the end

S

StillUnderstanding

Board Regular
Joined
Jan 30, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I wonder if anyone might be able to help me with this.

I have a VBA that runs on a worksheet and every time I run it, it clears formulas from the sheet. I am looking to have it write the formula back into the cell.

Here is what I have done:- Range("z2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$T$100,10,FALSE)), """")"

The code should go into cell Z2 and then look like this =IFERROR(VLOOKUP($Y2,$C$7:$T$100,10,FALSE), "")

The problem is that it just wont work, I get runtime error 1004 "Unable to get the match property of the worksheetFunction class"

Can anyone help me with this please!


Thanks.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
The one line of code you have supplied would not give that error. Please post all the code.
 
Upvote 0
Hi @Fluff, here is the fill code.

VBA Code: 
Option Explicit
Option Compare Text



Sub Lookup_UID()
Application.ScreenUpdating = False
Dim wSht As Worksheet, crow As Long, frow As Long, i As Long, wThis As Worksheet
Dim searchCode As String


Set wThis = Sheet14
searchCode = Trim(wThis.Range("B3"))
If Len(searchCode) = 0 Then
MsgBox "Please enter your Unique ID into cell B3 to search", vbOKOnly
ActiveSheet.Range("B3").Select
Exit Sub
End If



wThis.Range("A7:Ak" & Rows.Count) = Empty
wThis.Range("A7:Ak" & Rows.Count).ClearFormats
 

crow = 7

     For Each wSht In ThisWorkbook.Worksheets
    Dim Rng As Range
        
    Dim CnumA As Integer
    Dim CnumB As Integer
    Dim CnumC As Integer
    Dim CnumD As Integer
    Dim CnumE As Integer
    Dim CnumF As Integer
    Dim CnumG As Integer
    Dim CnumH As Integer
    Dim CnumI As Integer
    Dim CnumJ As Integer
    Dim CnumK As Integer
    Dim CnumL As Integer
    Dim CnumM As Integer
    Dim CnumN As Integer
    Dim CnumO As Integer
    Dim CnumP As Integer
    Dim CnumQ As Integer
    Dim CnumR As Integer
    Dim CnumS As Integer
    Dim CnumT As Integer
    Dim CnumU As Integer
    Dim CnumV As Integer
    Dim Cnumw As Integer
    Dim Cnumx As Integer
    Dim Cnumy As Integer
    Dim Cnumz As Integer
    Dim CnumAA As Integer
    Dim CnumAB As Integer
    Dim CnumAC As Integer
    Dim CnumAD As Integer
    Dim Cnumae As Integer
    Dim CnumAf As Integer
    Dim CnumAg As Integer
    Dim CnumAh As Integer
    Dim CnumAI As Integer
    Dim CnumAJ As Integer
    Dim CnumAk As Integer
    
    Dim LookCnum As Integer
    
    Dim ColA As String
    Dim ColB As String
    Dim ColC As String
    Dim ColD As String
    Dim ColE As String
    Dim ColF As String
    Dim ColG As String
    Dim ColH As String
    Dim ColI As String
    Dim ColJ As String
    Dim ColK As String
    Dim ColL As String
    Dim ColM As String
    Dim ColN As String
    Dim ColO As String
    Dim ColP As String
    Dim ColQ As String
    Dim ColR As String
    Dim ColS As String
    Dim ColT As String
    Dim ColU As String
    Dim ColV As String
    Dim Colw As String
    Dim Colx As String
    Dim Coly As String
    Dim Colz As String
    Dim ColAA As String
    Dim ColAB As String
    Dim ColAC As String
    Dim ColAD As String
    Dim Colae As String
    Dim ColAf As String
    Dim ColAg As String
    Dim ColAh As String
    Dim ColAI As String
    Dim ColAJ As String
    Dim ColAk As String
    
    
    
    Dim LookCol As String
    
    ColA = Cells(6, 1).Value
    ColB = Cells(6, 2).Value
    ColC = Cells(6, 3).Value
    ColD = Cells(6, 4).Value
    ColE = Cells(6, 5).Value
    ColF = Cells(6, 6).Value
    ColG = Cells(6, 7).Value
    ColH = Cells(6, 8).Value
    ColI = Cells(6, 9).Value
    ColJ = Cells(6, 10).Value
    ColK = Cells(6, 11).Value
    ColL = Cells(6, 12).Value
    ColM = Cells(6, 13).Value
    ColN = Cells(6, 14).Value
    ColO = Cells(6, 15).Value
    ColP = Cells(6, 16).Value
    ColQ = Cells(6, 17).Value
    ColR = Cells(6, 18).Value
    ColS = Cells(6, 19).Value
    ColT = Cells(6, 20).Value
    ColU = Cells(6, 21).Value
    ColV = Cells(6, 22).Value
    Colw = Cells(6, 23).Value
    Colx = Cells(6, 24).Value
    Coly = Cells(6, 25).Value
    Colz = Cells(6, 26).Value
    ColAA = Cells(6, 27).Value
    ColAB = Cells(6, 28).Value
    ColAC = Cells(6, 29).Value
    ColAD = Cells(6, 30).Value
    Colae = Cells(6, 31).Value
    ColAf = Cells(6, 32).Value
    ColAg = Cells(6, 33).Value
    ColAh = Cells(6, 34).Value
    ColAI = Cells(6, 35).Value
    ColAJ = Cells(6, 36).Value
    ColAk = Cells(6, 37).Value
    
    Set Rng = Range("a6:ak6")
    Application.CutCopyMode = False 'You only need the headers and not all the table
    
    'variable used to filter data
    LookCol = Cells(2, 2).Value
    
    LookCnum = Application.WorksheetFunction.Match(LookCol, Rng, 0)
    
    LookCol = Split(Cells(1, LookCnum).Address, "$")(1)
    
    'looking for column numbers
    CnumA = Application.WorksheetFunction.Match(ColA, Rng, 0)
    CnumB = Application.WorksheetFunction.Match(ColB, Rng, 0)
    CnumC = Application.WorksheetFunction.Match(ColC, Rng, 0)
    CnumD = Application.WorksheetFunction.Match(ColD, Rng, 0)
    CnumE = Application.WorksheetFunction.Match(ColE, Rng, 0)
    CnumF = Application.WorksheetFunction.Match(ColF, Rng, 0)
    CnumG = Application.WorksheetFunction.Match(ColG, Rng, 0)
    CnumH = Application.WorksheetFunction.Match(ColH, Rng, 0)
    CnumI = Application.WorksheetFunction.Match(ColI, Rng, 0)
    CnumJ = Application.WorksheetFunction.Match(ColJ, Rng, 0)
    CnumK = Application.WorksheetFunction.Match(ColK, Rng, 0)
    CnumL = Application.WorksheetFunction.Match(ColL, Rng, 0)
    CnumM = Application.WorksheetFunction.Match(ColM, Rng, 0)
    CnumN = Application.WorksheetFunction.Match(ColN, Rng, 0)
    CnumO = Application.WorksheetFunction.Match(ColO, Rng, 0)
    CnumP = Application.WorksheetFunction.Match(ColP, Rng, 0)
    CnumQ = Application.WorksheetFunction.Match(ColQ, Rng, 0)
    CnumR = Application.WorksheetFunction.Match(ColR, Rng, 0)
    CnumS = Application.WorksheetFunction.Match(ColS, Rng, 0)
    CnumT = Application.WorksheetFunction.Match(ColT, Rng, 0)
    CnumU = Application.WorksheetFunction.Match(ColU, Rng, 0)
    CnumV = Application.WorksheetFunction.Match(ColV, Rng, 0)
    Cnumw = Application.WorksheetFunction.Match(Colw, Rng, 0)
    Cnumx = Application.WorksheetFunction.Match(Colx, Rng, 0)
    Cnumy = Application.WorksheetFunction.Match(Coly, Rng, 0)
    Cnumz = Application.WorksheetFunction.Match(Colz, Rng, 0)
    CnumAA = Application.WorksheetFunction.Match(ColAA, Rng, 0)
    CnumAB = Application.WorksheetFunction.Match(ColAB, Rng, 0)
    CnumAC = Application.WorksheetFunction.Match(ColAC, Rng, 0)
    CnumAD = Application.WorksheetFunction.Match(ColAD, Rng, 0)
    Cnumae = Application.WorksheetFunction.Match(Colae, Rng, 0)
    CnumAf = Application.WorksheetFunction.Match(ColAf, Rng, 0)
    CnumAg = Application.WorksheetFunction.Match(ColAg, Rng, 0)
    CnumAh = Application.WorksheetFunction.Match(ColAh, Rng, 0)
    CnumAI = Application.WorksheetFunction.Match(ColAI, Rng, 0)
    CnumAJ = Application.WorksheetFunction.Match(ColAJ, Rng, 0)
    CnumAk = Application.WorksheetFunction.Match(ColAk, Rng, 0)
    
        
    'Convert To Column Letter
    ColA = Split(Cells(1, CnumA).Address, "$")(1)
    ColB = Split(Cells(1, CnumB).Address, "$")(1)
    ColC = Split(Cells(1, CnumC).Address, "$")(1)
    ColD = Split(Cells(1, CnumD).Address, "$")(1)
    ColE = Split(Cells(1, CnumE).Address, "$")(1)
    ColF = Split(Cells(1, CnumF).Address, "$")(1)
    ColG = Split(Cells(1, CnumG).Address, "$")(1)
    ColH = Split(Cells(1, CnumH).Address, "$")(1)
    ColI = Split(Cells(1, CnumI).Address, "$")(1)
    ColJ = Split(Cells(1, CnumJ).Address, "$")(1)
    ColK = Split(Cells(1, CnumK).Address, "$")(1)
    ColL = Split(Cells(1, CnumL).Address, "$")(1)
    ColM = Split(Cells(1, CnumM).Address, "$")(1)
    ColN = Split(Cells(1, CnumN).Address, "$")(1)
    ColO = Split(Cells(1, CnumO).Address, "$")(1)
    ColP = Split(Cells(1, CnumP).Address, "$")(1)
    ColQ = Split(Cells(1, CnumQ).Address, "$")(1)
    ColR = Split(Cells(1, CnumR).Address, "$")(1)
    ColS = Split(Cells(1, CnumS).Address, "$")(1)
    ColT = Split(Cells(1, CnumT).Address, "$")(1)
    ColU = Split(Cells(1, CnumU).Address, "$")(1)
    ColV = Split(Cells(1, CnumV).Address, "$")(1)
    Colw = Split(Cells(1, Cnumw).Address, "$")(1)
    Colx = Split(Cells(1, Cnumx).Address, "$")(1)
    Coly = Split(Cells(1, Cnumy).Address, "$")(1)
    Colz = Split(Cells(1, Cnumz).Address, "$")(1)
    ColAA = Split(Cells(1, CnumAA).Address, "$")(1)
    ColAB = Split(Cells(1, CnumAB).Address, "$")(1)
    ColAC = Split(Cells(1, CnumAC).Address, "$")(1)
    ColAD = Split(Cells(1, CnumAD).Address, "$")(1)
    Colae = Split(Cells(1, Cnumae).Address, "$")(1)
    ColAf = Split(Cells(1, CnumAf).Address, "$")(1)
    ColAg = Split(Cells(1, CnumAg).Address, "$")(1)
    ColAh = Split(Cells(1, CnumAh).Address, "$")(1)
    ColAI = Split(Cells(1, CnumAI).Address, "$")(1)
    ColAJ = Split(Cells(1, CnumAJ).Address, "$")(1)
    ColAk = Split(Cells(1, CnumAk).Address, "$")(1)
 
    frow = wSht.Range("Af" & Rows.Count).End(xlUp).Row
    
    For i = 2 To frow
        If wSht.Range(LookCol & i) = searchCode Then
            wThis.Range("A" & crow) = wSht.Range(ColA & i)
            wThis.Range("A" & crow).Cells.Interior.Color = wSht.Range(ColA & i).Cells.Interior.Color
            wThis.Range("A" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("B" & crow) = wSht.Range(ColB & i)
            wThis.Range("B" & crow).Cells.Interior.Color = wSht.Range(ColB & i).Cells.Interior.Color
            wThis.Range("B" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("C" & crow) = wSht.Range(ColC & i)
            wThis.Range("C" & crow).Cells.Interior.Color = wSht.Range(ColC & i).Cells.Interior.Color
            wThis.Range("C" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("d" & crow) = wSht.Range(ColD & i)
            wThis.Range("D" & crow).Cells.Interior.Color = wSht.Range(ColD & i).Cells.Interior.Color
            wThis.Range("D" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                
            wThis.Range("e" & crow) = wSht.Range(ColE & i)
            wThis.Range("E" & crow).Cells.Interior.Color = wSht.Range(ColE & i).Cells.Interior.Color
            wThis.Range("E" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("f" & crow) = wSht.Range(ColF & i)
            wThis.Range("F" & crow).Cells.Interior.Color = wSht.Range(ColF & i).Cells.Interior.Color
            wThis.Range("F" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("g" & crow) = wSht.Range(ColG & i)
            wThis.Range("G" & crow).Cells.Interior.Color = wSht.Range(ColG & i).Cells.Interior.Color
            wThis.Range("G" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("h" & crow) = wSht.Range(ColH & i)
            wThis.Range("H" & crow).Cells.Interior.Color = wSht.Range(ColH & i).Cells.Interior.Color
            wThis.Range("H" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("i" & crow) = wSht.Range(ColI & i)
            wThis.Range("I" & crow).Cells.Interior.Color = wSht.Range(ColI & i).Cells.Interior.Color
            wThis.Range("I" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("j" & crow) = wSht.Range(ColJ & i)
            wThis.Range("J" & crow).Cells.Interior.Color = wSht.Range(ColJ & i).Cells.Interior.Color
            wThis.Range("J" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("k" & crow) = wSht.Range(ColK & i)
            wThis.Range("K" & crow).Cells.Interior.Color = wSht.Range(ColK & i).Cells.Interior.Color
            wThis.Range("K" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("l" & crow) = wSht.Range(ColL & i)
            wThis.Range("L" & crow).Cells.Interior.Color = wSht.Range(ColL & i).Cells.Interior.Color
            wThis.Range("L" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("m" & crow) = wSht.Range(ColM & i)
            wThis.Range("M" & crow).Cells.Interior.Color = wSht.Range(ColM & i).Cells.Interior.Color

             wThis.Range("M" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("n" & crow) = wSht.Range(ColN & i)
            wThis.Range("N" & crow).Cells.Interior.Color = wSht.Range(ColN & i).Cells.Interior.Color
            wThis.Range("N" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("o" & crow) = wSht.Range(ColO & i)
            wThis.Range("O" & crow).Cells.Interior.Color = wSht.Range(ColO & i).Cells.Interior.Color
            wThis.Range("O" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("p" & crow) = wSht.Range(ColP & i)
            wThis.Range("P" & crow).Cells.Interior.Color = wSht.Range(ColP & i).Cells.Interior.Color
            wThis.Range("P" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("q" & crow) = wSht.Range(ColQ & i)
            wThis.Range("Q" & crow).Cells.Interior.Color = wSht.Range(ColQ & i).Cells.Interior.Color
            wThis.Range("Q" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("r" & crow) = wSht.Range(ColR & i)
            wThis.Range("R" & crow).Cells.Interior.Color = wSht.Range(ColR & i).Cells.Interior.Color
            wThis.Range("R" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("S" & crow) = wSht.Range(ColS & i)
            wThis.Range("S" & crow).Cells.Interior.Color = wSht.Range(ColS & i).Cells.Interior.Color
            wThis.Range("S" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("T" & crow) = wSht.Range(ColT & i)
            wThis.Range("T" & crow).Cells.Interior.Color = wSht.Range(ColT & i).Cells.Interior.Color
            wThis.Range("T" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("U" & crow) = wSht.Range(ColU & i)
            wThis.Range("U" & crow).Cells.Interior.Color = wSht.Range(ColU & i).Cells.Interior.Color
            wThis.Range("U" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("V" & crow) = wSht.Range(ColV & i)
            wThis.Range("V" & crow).Cells.Interior.Color = wSht.Range(ColV & i).Cells.Interior.Color
            wThis.Range("V" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("W" & crow) = wSht.Range(Colw & i)
            wThis.Range("W" & crow).Cells.Interior.Color = wSht.Range(Colw & i).Cells.Interior.Color
            wThis.Range("W" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("X" & crow) = wSht.Range(Colx & i)
            wThis.Range("X" & crow).Cells.Interior.Color = wSht.Range(Colx & i).Cells.Interior.Color
            wThis.Range("X" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("Y" & crow) = wSht.Range(Coly & i)
            wThis.Range("Y" & crow).Cells.Interior.Color = wSht.Range(Coly & i).Cells.Interior.Color
            wThis.Range("Y" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("Z" & crow) = wSht.Range(Colz & i)
            wThis.Range("Z" & crow).Cells.Interior.Color = wSht.Range(Colz & i).Cells.Interior.Color
            wThis.Range("Z" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AA" & crow) = wSht.Range(ColAA & i)
            wThis.Range("AA" & crow).Cells.Interior.Color = wSht.Range(ColAA & i).Cells.Interior.Color
            wThis.Range("AA" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AB" & crow) = wSht.Range(ColAB & i)
            wThis.Range("AB" & crow).Cells.Interior.Color = wSht.Range(ColAB & i).Cells.Interior.Color
            wThis.Range("AB" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AC" & crow) = wSht.Range(ColAC & i)
            wThis.Range("AC" & crow).Cells.Interior.Color = wSht.Range(ColAC & i).Cells.Interior.Color
            wThis.Range("AC" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AD" & crow) = wSht.Range(ColAD & i)
            wThis.Range("AD" & crow).Cells.Interior.Color = wSht.Range(ColAD & i).Cells.Interior.Color
            wThis.Range("AD" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AE" & crow) = wSht.Range(Colae & i)
            wThis.Range("AE" & crow).Cells.Interior.Color = wSht.Range(Colae & i).Cells.Interior.Color
            wThis.Range("AE" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AF" & crow) = wSht.Range(ColAf & i)
            wThis.Range("AF" & crow).Cells.Interior.Color = wSht.Range(ColAf & i).Cells.Interior.Color
            wThis.Range("AF" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AG" & crow) = wSht.Range(ColAg & i)
            wThis.Range("AG" & crow).Cells.Interior.Color = wSht.Range(ColAg & i).Cells.Interior.Color
            wThis.Range("AG" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            wThis.Range("AH" & crow) = wSht.Range(ColAh & i)
            wThis.Range("AH" & crow).Cells.Interior.Color = wSht.Range(ColAh & i).Cells.Interior.Color
            wThis.Range("AH" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("AI" & crow) = wSht.Range(ColAI & i)
            wThis.Range("AI" & crow).Cells.Interior.Color = wSht.Range(ColAI & i).Cells.Interior.Color
            wThis.Range("AI" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("AJ" & crow) = wSht.Range(ColAJ & i)
            wThis.Range("AJ" & crow).Cells.Interior.Color = wSht.Range(ColAJ & i).Cells.Interior.Color
            wThis.Range("AJ" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
                 wThis.Range("Ak" & crow) = wSht.Range(ColAk & i)
            wThis.Range("Ak" & crow).Cells.Interior.Color = wSht.Range(ColAk & i).Cells.Interior.Color
            wThis.Range("Ak" & crow).Cells.BorderAround _
                LineStyle:=xlContinuous, _
                Weight:=xlThin
            crow = crow + 1
        End If
    Next i


If Trim(wThis.Range("D3")) = "Y" Then
    Set wSht = Sheet2
    
        
      ColA = Cells(6, 1).Value
    ColB = Cells(6, 2).Value
    ColC = Cells(6, 3).Value
    ColD = Cells(6, 4).Value
    ColE = Cells(6, 5).Value
    ColF = Cells(6, 6).Value
    ColG = Cells(6, 7).Value
    ColH = Cells(6, 8).Value
    ColI = Cells(6, 9).Value
    ColJ = Cells(6, 10).Value
    ColK = Cells(6, 11).Value
    ColL = Cells(6, 12).Value
    ColM = Cells(6, 13).Value
    ColN = Cells(6, 14).Value
    ColO = Cells(6, 15).Value
    ColP = Cells(6, 16).Value
    ColQ = Cells(6, 17).Value
    ColR = Cells(6, 18).Value
    ColS = Cells(6, 19).Value
    ColT = Cells(6, 20).Value
    ColU = Cells(6, 21).Value
    ColV = Cells(6, 22).Value
    Colw = Cells(6, 23).Value
    Colx = Cells(6, 24).Value
    Coly = Cells(6, 25).Value
    Colz = Cells(6, 26).Value
    ColAA = Cells(6, 27).Value
    ColAB = Cells(6, 28).Value
    ColAC = Cells(6, 29).Value
    ColAD = Cells(6, 30).Value
    Colae = Cells(6, 31).Value
    ColAf = Cells(6, 32).Value
    ColAg = Cells(6, 33).Value
    ColAh = Cells(6, 34).Value
        
    Set Rng = Range("Raw_Data_Headers").RefersToR1C1 = _
        "=Lookup!R6C1:R6C37"
    Application.CutCopyMode = False 'You only need the headers and not all the table
    
    'variable used to filter data
    
    
    LookCnum = Application.WorksheetFunction.Match(LookCol, Rng, 0)
    
    LookCol = Split(Cells(1, LookCnum).Address, "$")(1)
    
    'looking for column numbers
    CnumA = Application.WorksheetFunction.Match(ColA, Rng, 0)
    CnumB = Application.WorksheetFunction.Match(ColB, Rng, 0)
    CnumC = Application.WorksheetFunction.Match(ColC, Rng, 0)
    CnumD = Application.WorksheetFunction.Match(ColD, Rng, 0)
    CnumE = Application.WorksheetFunction.Match(ColE, Rng, 0)
    CnumF = Application.WorksheetFunction.Match(ColF, Rng, 0)
    CnumG = Application.WorksheetFunction.Match(ColG, Rng, 0)
    CnumH = Application.WorksheetFunction.Match(ColH, Rng, 0)
    CnumI = Application.WorksheetFunction.Match(ColI, Rng, 0)
    CnumJ = Application.WorksheetFunction.Match(ColJ, Rng, 0)
    CnumK = Application.WorksheetFunction.Match(ColK, Rng, 0)
    CnumL = Application.WorksheetFunction.Match(ColL, Rng, 0)
    CnumM = Application.WorksheetFunction.Match(ColM, Rng, 0)
    CnumN = Application.WorksheetFunction.Match(ColN, Rng, 0)
    CnumO = Application.WorksheetFunction.Match(ColO, Rng, 0)
    CnumP = Application.WorksheetFunction.Match(ColP, Rng, 0)
    CnumQ = Application.WorksheetFunction.Match(ColQ, Rng, 0)
    CnumR = Application.WorksheetFunction.Match(ColR, Rng, 0)
    CnumS = Application.WorksheetFunction.Match(ColS, Rng, 0)
    CnumT = Application.WorksheetFunction.Match(ColT, Rng, 0)
    CnumU = Application.WorksheetFunction.Match(ColU, Rng, 0)
    CnumV = Application.WorksheetFunction.Match(ColV, Rng, 0)
    
        
    'Convert To Column Letter
    ColA = Split(Cells(1, CnumA).Address, "$")(1)
    ColB = Split(Cells(1, CnumB).Address, "$")(1)
    ColC = Split(Cells(1, CnumC).Address, "$")(1)
    ColD = Split(Cells(1, CnumD).Address, "$")(1)
    ColE = Split(Cells(1, CnumE).Address, "$")(1)
    ColF = Split(Cells(1, CnumF).Address, "$")(1)
    ColG = Split(Cells(1, CnumG).Address, "$")(1)
    ColH = Split(Cells(1, CnumH).Address, "$")(1)
    ColI = Split(Cells(1, CnumI).Address, "$")(1)
    ColJ = Split(Cells(1, CnumJ).Address, "$")(1)
    ColK = Split(Cells(1, CnumK).Address, "$")(1)
    ColL = Split(Cells(1, CnumL).Address, "$")(1)
    ColM = Split(Cells(1, CnumM).Address, "$")(1)
    ColN = Split(Cells(1, CnumN).Address, "$")(1)
    ColO = Split(Cells(1, CnumO).Address, "$")(1)
    ColP = Split(Cells(1, CnumP).Address, "$")(1)
    ColQ = Split(Cells(1, CnumQ).Address, "$")(1)
    ColR = Split(Cells(1, CnumR).Address, "$")(1)
    ColS = Split(Cells(1, CnumS).Address, "$")(1)
    ColT = Split(Cells(1, CnumT).Address, "$")(1)
    ColU = Split(Cells(1, CnumU).Address, "$")(1)
    ColV = Split(Cells(1, CnumV).Address, "$")(1)
 
    frow = wSht.Range("AJ" & Rows.Count).End(xlUp).Row
    
    For i = 2 To frow
        If wSht.Range(LookCol & i) = searchCode Then
            wThis.Range("A" & crow) = wSht.Range(ColA & i)
            wThis.Range("B" & crow) = wSht.Range(ColB & i)
            wThis.Range("C" & crow) = wSht.Range(ColC & i)
            wThis.Range("d" & crow) = wSht.Range(ColD & i)
            wThis.Range("e" & crow) = wSht.Range(ColE & i)
            wThis.Range("f" & crow) = wSht.Range(ColF & i)
            wThis.Range("g" & crow) = wSht.Range(ColG & i)
            wThis.Range("h" & crow) = wSht.Range(ColH & i)
            wThis.Range("i" & crow) = wSht.Range(ColI & i)
            wThis.Range("j" & crow) = wSht.Range(ColJ & i)
            wThis.Range("k" & crow) = wSht.Range(ColK & i)
            wThis.Range("l" & crow) = wSht.Range(ColL & i)
            wThis.Range("m" & crow) = wSht.Range(ColM & i)
            wThis.Range("n" & crow) = wSht.Range(ColN & i)
            wThis.Range("o" & crow) = wSht.Range(ColO & i)
            wThis.Range("p" & crow) = wSht.Range(ColP & i)
            wThis.Range("q" & crow) = wSht.Range(ColQ & i)
            wThis.Range("r" & crow) = wSht.Range(ColR & i)
            wThis.Range("S" & crow) = wSht.Range(ColS & i)
            wThis.Range("T" & crow) = wSht.Range(ColT & i)
            wThis.Range("U" & crow) = wSht.Range(ColU & i)
            wThis.Range("V" & crow) = wSht.Range(ColV & i)
            crow = crow + 1
        End If
    Next i
    
  ActiveSheet.Protect
End If
Application.ScreenUpdating = True
Range("A6:ai50").WrapText = True

Range("a6:al50").HorizontalAlignment = xlCenter

Range("j:j,y:y").Select
    Selection.NumberFormat = "dd/mm/yy;@"
Range("b3").Select
     Application.Calculation = xlAutomatic
    
Next wSht

         '---------------------------------------------------
      Dim lastrow As Long

lastrow = Cells(Rows.Count, "c").End(xlUp).Row
    
    ActiveSheet.Range("c6:c" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("y1:y4"), _
    Unique:=True
     Range("U7:Ak7").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
      
    End With
    Selection.Font.Bold = True
    
    '---------------------------------------------
     Range("U6:Ak6").Select
    Selection.Delete Shift:=xlUp
    Range("U7:Ak8").Select
    Selection.Delete Shift:=xlUp
      
    Range("U7:U41").Select
    Selection.ColumnWidth = 5.73
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
        
        Range("$u$6").Value = "Blank"
    Range("$u$6").Select
    With Selection.Font
        .Color = -11480942
        .TintAndShade = 0
    Range("U7").Select
    
     End With
    Selection.Font.Bold = True
    
    Range("z2").Formula = "=IFERROR(VLOOKUP($Y2,$C$7:$T$100,10,FALSE)), """")"
    '-----------------------------------------------
   Range("B3").Select
   MsgBox "Process Complete" & vbNewLine & _
        crow - 7 & " Records found"
        'reset find variable
    Range("B3").Select
    

End


        End With
        
    




End Sub
 
Upvote 0
Thanks for that which line does it fail on?
 
Upvote 0
Not with the error message you said. Although you have an extra closing bracket.
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
279
Peter_SSs
Peter_SSs
A
  • Question
IF function
Replies
8
Views
261
Peter_SSs
Peter_SSs
L
  • Question
Run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class
Replies
5
Views
539
dreid1011
dreid1011
R
  • Solved
Vlookup help for comma separated values
2
Replies
13
Views
287
Peter_SSs
Peter_SSs
gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
143
gtd526
gtd526

Forum statistics

Threads
1,215,054
Messages
6,122,901
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top