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