How to get VBA search to return the row of data if the last cell in the range has no data in it

[StillUnderstanding]

Hi Everyone,

So I would be grateful if anyone could help me with a bug on my VBA.

So the below sub allows me to go to a lookup sheet and enter in a column name to search in Cell B2 and the value in cell B3, then it matches the value with the column it then returns the row of data, in this instance it returns all the data from A to to AH. The problem that I am having is that if I have no data in cell AH then it wont return any data at all.

If I have for example, data in A and AH but nothing in the middle then it returns the full row.

Would anyone be able to help?

Thanks!


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 = Sheet1
searchCode = Trim(wThis.Range("B3"))
ActiveSheet.Unprotect
wThis.Range("A6:Ah" & Rows.Count) = Empty

crow = 6

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 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 LookCol As String

ColA = Cells(5, 1).Value
ColB = Cells(5, 2).Value
ColC = Cells(5, 3).Value
ColD = Cells(5, 4).Value
ColE = Cells(5, 5).Value
ColF = Cells(5, 6).Value
ColG = Cells(5, 7).Value
ColH = Cells(5, 8).Value
ColI = Cells(5, 9).Value
ColJ = Cells(5, 10).Value
ColK = Cells(5, 11).Value
ColL = Cells(5, 12).Value
ColM = Cells(5, 13).Value
ColN = Cells(5, 14).Value
ColO = Cells(5, 15).Value
ColP = Cells(5, 16).Value
ColQ = Cells(5, 17).Value
ColR = Cells(5, 18).Value
ColS = Cells(5, 19).Value
ColT = Cells(5, 20).Value
ColU = Cells(5, 21).Value
ColV = Cells(5, 22).Value
Colw = Cells(5, 23).Value
Colx = Cells(5, 24).Value
Coly = Cells(5, 25).Value
Colz = Cells(5, 26).Value
ColAA = Cells(5, 27).Value
ColAB = Cells(5, 28).Value
ColAC = Cells(5, 29).Value
ColAD = Cells(5, 30).Value
Colae = Cells(5, 31).Value
ColAf = Cells(5, 32).Value
ColAg = Cells(5, 33).Value
ColAh = Cells(5, 34).Value

Set rng = Range("Raw_Data_Headers") '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)


'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)

frow = wSht.Range("Ah" & 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
crow = crow + 1
End If
Next i


If Trim(wThis.Range("D3")) = "Y" Then
Set wSht = Sheet2


ColA = Cells(5, 1).Value
ColB = Cells(5, 2).Value
ColC = Cells(5, 3).Value
ColD = Cells(5, 4).Value
ColE = Cells(5, 5).Value
ColF = Cells(5, 6).Value
ColG = Cells(5, 7).Value
ColH = Cells(5, 8).Value
ColI = Cells(5, 9).Value
ColJ = Cells(5, 10).Value
ColK = Cells(5, 11).Value
ColL = Cells(5, 12).Value
ColM = Cells(5, 13).Value
ColN = Cells(5, 14).Value
ColO = Cells(5, 15).Value
ColP = Cells(5, 16).Value
ColQ = Cells(5, 17).Value
ColR = Cells(5, 18).Value
ColS = Cells(5, 19).Value
ColT = Cells(5, 20).Value
ColU = Cells(5, 21).Value
ColV = Cells(5, 22).Value
Colw = Cells(5, 23).Value
Colx = Cells(5, 24).Value
Coly = Cells(5, 25).Value
Colz = Cells(5, 26).Value
ColAA = Cells(5, 27).Value
ColAB = Cells(5, 28).Value
ColAC = Cells(5, 29).Value
ColAD = Cells(5, 30).Value
Colae = Cells(5, 31).Value
ColAf = Cells(5, 32).Value
ColAg = Cells(5, 33).Value
ColAh = Cells(5, 34).Value

Set rng = Range("Raw_Data_Headers") '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)


'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("AL" & 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("A5:aa50").WrapText = True
Range("e5:e50, f5:f50").NumberFormat = "dd-mm-yyyy;@"
Range("o660, p650, q6:q50, r6:r50").Clear
Range("a5:al50").HorizontalAlignment = xlCenter

Next wSht
MsgBox "Process Complete" & vbNewLine & _
crow - 6 & " Records found"
'reset find variable
End







End Sub

 

[Saurabhj]

Hi,

Please share your data (lookup sheet) also using XL2BB. It will help us to copy the data in our excel sheet and check your code.

Thanks,
Saurabh