Help with my VBA Vloopup
 

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

Help with my VBA Vloopup
 

You might be better off asking a specific question and skipping posting your
code.

for example to determine if sheets are present

Dim sh1 as Worksheet, sh2 as Worksheet
set sh1 = Worksheets("RMICLPMI")
set sh2 = Worksheets("MGICLPMI")

if Not sh1 is nothing then
' code to process RMICLPMI
end if
if Not sh1 is nothing then
'code to process MGICLPMI
End if

Another approach

Dim sh as Worksheet
for each sh in Worksheets
if Ucase(sh.Name) = "RMICLPMI" or _
Ucase(sh.Name) = "MGICLPMI" then
' run code against sh
end if
Next

--
Regards,
Tom Ogilvy


"jln via OfficeKB.com" wrote:

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