Here is the recorded macro that i did. I need to change it to count the
number of records and match that with what the vlook up needs to do. The
amount of records changes for each file that i work on.
Sub LPMIVlookup()
'
' LPMIVlookup Macro
' Macro recorded 10/20/2006 by J922703
'
'
Range("A1:Y1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("E19").Select
ActiveWindow.SmallScroll ToRight:=14
Sheets("MGICLPMI").Select
Range("A1:O1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Modem").Select
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-24],MGICLPMI!R1C4:R635C14,11,0)"
Selection.AutoFill Destination:=Range("Z2:Z2439")
Range("Z2:Z2439").Select
Selection.Copy
Range("Z2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("AA2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-13]-RC[-1]"
Range("AA2").Select
Selection.AutoFill Destination:=Range("AA2:AA2439")
Range("AA2:AA2439").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=-4
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O2442")
Range("O2:O2442").Select
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1