Ok here is the code that was posted to my org question with a change. I
need to make it so that the rows are counted and also look to see if both
RMICLPMI and MGICLPMI sheets are in the work book and if only one of them is
there then just run that part of the code.

Sub LPMIVlookup()

Dim myR1 As Range
Dim myR2 As Range
Dim myR3 As Range
Dim myR4 As Range
Dim rng1 As Range
'If Work sheets = ("RMICLPMI") Then
'Set myR1 = Sheets("Sheet Name").Range("A1:Y1")
'Set myR1 = Range(myR1, myR1.End(xlDown))
'myR1.Sort Key1:=myR1.Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
'OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
'DataOption1:=xlSortNormal
'Else
'If Work sheets = ("MGICLPMI") Then
Set myR2 = Sheets("MGICLPMI").Range("A1:O1")
Set myR2 = Cells(Rows.Count, A).End(xlUp).Row
myR2.Sort Key1:=myR2.Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Else
Set myR3 = Sheets("Modem").Cells(Rows.Count, A).End(xlUp).Row
myR3.FormulaR1C1 = "=VLOOKUP(RC[-24],MGICLPMI!R2C4:R1171C14,11,0)"
myR3.Copy
myR3.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
myR3.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Set myR4 = Sheets("Modem").Cells(Rows.Count, A).End(xlUp).Row
myR4.FormulaR1C1 = "=RC[-13]-RC[-1]"
myR4.Copy
Sheets("Modem").Range("N2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Set myR4 = Sheets("Modem").Cells(Rows.Count, A).End(xlUp).Row
myR4.FormulaR1C1 = "=RC[-2]+RC[-1]"
myR4.Copy
Sheets("Modem").Range("O2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

--
Message posted via http://www.officekb.com