PDA

View Full Version : Solved: vlookup problems



CatDaddy
05-08-2012, 10:42 AM
only a few matches...ive written an extensive macro to pull data from multiple sources which works fine, but when i try to reorganize them using vlookups on the "names" page only a few of the names seems to be recognized...the code is just brute force, let me know if you can think of anything to help

thanks in advance
Sub vlookups()
Dim cell As Range
Dim lr As Long
ThisWorkbook.Sheets("names").Activate
lr = Range("A65536").End(xlUp).Row
'TOTALS
Range("B2:B" & lr).Value = Sheets("raw").Range("C2").Value
Range("D2:D" & lr).Value = Sheets("raw").Range("G2").Value
Range("F2:F" & lr).Value = Sheets("raw").Range("K2").Value
Range("H2:H" & lr).Value = Sheets("raw").Range("O2").Value
Range("J2:J" & lr).Value = Sheets("raw").Range("Q2").Value
Range("L2:L" & lr).Value = Sheets("raw").Range("U2").Value
Range("N2:N" & lr).Value = Sheets("raw").Range("Y2").Value
Range("P2:P" & lr).Value = Sheets("raw").Range("AC2").Value
Range("R2:R" & lr).Value = Sheets("raw").Range("AG2").Value
Range("T2:T" & lr).Value = Sheets("raw").Range("AK2").Value
Range("V2:V" & lr).Value = Sheets("raw").Range("AO2").Value
Range("X2:X" & lr).Value = Sheets("raw").Range("AS2").Value
'VLOOKUPS
For Each cell In Range("A2:A" & lr)
cell.Offset(0, 2).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!A2:B30,2,0)"
cell.Offset(0, 2).Value = cell.Offset(0, 2).Value

cell.Offset(0, 4).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!E2:F30,2,0)"
cell.Offset(0, 4).Value = cell.Offset(0, 4).Value

cell.Offset(0, 6).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!I2:J30,2,0)"
cell.Offset(0, 6).Value = cell.Offset(0, 6).Value

cell.Offset(0, 8).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!M2:N30,2,0)"
cell.Offset(0, 8).Value = cell.Offset(0, 8).Value

cell.Offset(0, 10).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!M2:P30,4,0)"
cell.Offset(0, 10).Value = cell.Offset(0, 10).Value

cell.Offset(0, 12).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!S2:T30,2,0)"
cell.Offset(0, 12).Value = cell.Offset(0, 12).Value

cell.Offset(0, 14).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!W2:X30,2,0)"
cell.Offset(0, 14).Value = cell.Offset(0, 14).Value

cell.Offset(0, 16).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!AA2:AB30,2,0)"
cell.Offset(0, 16).Value = cell.Offset(0, 16).Value

cell.Offset(0, 18).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!AE2:AF30,2,0)"
cell.Offset(0, 18).Value = cell.Offset(0, 18).Value

cell.Offset(0, 20).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!AI2:AJ30,2,0)"
cell.Offset(0, 20).Value = cell.Offset(0, 20).Value

cell.Offset(0, 22).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!AM2:AN30,2,0)"
cell.Offset(0, 22).Value = cell.Offset(0, 22).Value

cell.Offset(0, 24).Formula = "=VLOOKUP(" & Chr(34) & cell.Text & Chr(34) & ",raw!AQ2:AR30,2,0)"
cell.Offset(0, 24).Value = cell.Offset(0, 24).Value
Next cell
End Sub

Bob Phillips
05-08-2012, 11:10 AM
See if this works better

Sub vlookups()

Dim cell As Range
Dim lr As Long

With ThisWorkbook.Sheets("names")

lr = .Range("A" & .Rows.Count).End(xlUp).Row

'TOTALS
.Range("B2:B" & lr).Value = Sheets("raw").Range("C2").Value
.Range("D2:D" & lr).Value = Sheets("raw").Range("G2").Value
.Range("F2:F" & lr).Value = Sheets("raw").Range("K2").Value
.Range("H2:H" & lr).Value = Sheets("raw").Range("O2").Value
.Range("J2:J" & lr).Value = Sheets("raw").Range("Q2").Value
.Range("L2:L" & lr).Value = Sheets("raw").Range("U2").Value
.Range("N2:N" & lr).Value = Sheets("raw").Range("Y2").Value
.Range("P2:P" & lr).Value = Sheets("raw").Range("AC2").Value
.Range("R2:R" & lr).Value = Sheets("raw").Range("AG2").Value
.Range("T2:T" & lr).Value = Sheets("raw").Range("AK2").Value
.Range("V2:V" & lr).Value = Sheets("raw").Range("AO2").Value
.Range("X2:X" & lr).Value = Sheets("raw").Range("AS2").Value

'VLOOKUPS

For Each cell In .Range("A2:A" & lr)

cell.Offset(0, 2).Value = DoTheLookup(cell.Text, "raw!A2:B30")
cell.Offset(0, 4).Value = DoTheLookup(cell.Text, "raw!E2:F30")
cell.Offset(0, 6).Value = DoTheLookup(cell.Text, "raw!I2:J30")
cell.Offset(0, 8).Value = DoTheLookup(cell.Text, "raw!M2:N30")
cell.Offset(0, 10).Value = DoTheLookup(cell.Text, "raw!M2:P30")
cell.Offset(0, 12).Value = DoTheLookup(cell.Text, "raw!S2:T30")
cell.Offset(0, 14).Value = DoTheLookup(cell.Text, "raw!W2:X30")
cell.Offset(0, 16).Value = DoTheLookup(cell.Text, "raw!AA2:AB30")
cell.Offset(0, 18).Value = DoTheLookup(cell.Text, "raw!AE2:AF30")
cell.Offset(0, 20).Value = DoTheLookup(cell.Text, "raw!AI2:AJ30")
cell.Offset(0, 22).Value = DoTheLookup(cell.Text, "raw!AM2:AN30")
cell.Offset(0, 24).Value = DoTheLookup(cell.Text, "raw!AQ2:AR30")
Next cell
End With
End Sub

Private Function DoTheLookup(cellText As String, rng As String) As Variant
DoTheLookup = ThisWorkbook.Sheets("names").Evaluate("=VLOOKUP(TRIM(""" & cellText & """)," & rng & ",2,0)")
End Function

CatDaddy
05-08-2012, 11:16 AM
genius...can you tell me what i was doing wrong?

CatDaddy
05-08-2012, 11:30 AM
also thank you so much

CatDaddy
05-08-2012, 11:34 AM
also also yours worked 800x faster than mine