Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > vLOOK UP INTO vba
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default vLOOK UP INTO vba
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default vLOOK UP INTO vba
Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub LPMIVlookup2()
' LPMIVlookup Macro
' Macro recorded 10/20/2006 by J922703

Dim myR1 As Range
Dim myR2 As Range
Dim myR3 As Range
Dim myR4 As Range

Set myR1 = 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

Set myR2 = Sheets("MGICLPMI").Range("A1:O1")
Set myR2 = Range(myR2, myR2.End(xlDown))
myR2.Sort Key1:=myR2.Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set myR3 = Sheets("Modem").Range("Z2:Z2439")
myR3.FormulaR1C1 = "=VLOOKUP(RC[-24],MGICLPMI!" & myR2.Address(True, True, xlR1C1) & ",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").Range("AA2:AA2439")
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").Range("O2:O2442")
myR4.FormulaR1C1 = "=RC[-2]+RC[-1]"
myR4.Copy
Sheets("Modem").Range("O2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


"jln via OfficeKB.com" <u25956@uwe wrote in message news:68651609d3f49@uwe...
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default vLOOK UP INTO vba
The line

Set myR1 = Range("A1:Y1")

Should also have the sheet name:

Set myR1 = Sheets("Sheet Name").Range("A1:Y1")

Sorry about that - it will default to the active sheet otherwise....


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub LPMIVlookup2()
' LPMIVlookup Macro
' Macro recorded 10/20/2006 by J922703

Dim myR1 As Range
Dim myR2 As Range
Dim myR3 As Range
Dim myR4 As Range

Set myR1 = 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

Set myR2 = Sheets("MGICLPMI").Range("A1:O1")
Set myR2 = Range(myR2, myR2.End(xlDown))
myR2.Sort Key1:=myR2.Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set myR3 = Sheets("Modem").Range("Z2:Z2439")
myR3.FormulaR1C1 = "=VLOOKUP(RC[-24],MGICLPMI!" & myR2.Address(True, True, xlR1C1) & ",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").Range("AA2:AA2439")
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").Range("O2:O2442")
myR4.FormulaR1C1 = "=RC[-2]+RC[-1]"
myR4.Copy
Sheets("Modem").Range("O2").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


"jln via OfficeKB.com" <u25956@uwe wrote in message news:68651609d3f49@uwe...
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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default vLOOK UP INTO vba
Hey i started looking at this today and wasnt sure what is beeing done in the
vlookup, also is there a way for it to see if the sheet is there. Sometime I
dont have both sheets.

Bernie Deitrick wrote:
The line

Set myR1 = Range("A1:Y1")

Should also have the sheet name:

Set myR1 = Sheets("Sheet Name").Range("A1:Y1")

Sorry about that - it will default to the active sheet otherwise....

HTH,
Bernie
MS Excel MVP

Try the macro below.

[quoted text clipped - 106 lines]
:=False, Transpose:=False
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlook up value is #N/A Hong Excel Worksheet Functions 4 January 5th 09 09:13 PM
Vlook up Martha Excel Discussion (Misc queries) 2 January 16th 08 07:45 PM
VLOOK-pivot table expanding want to update vlook automatically CrimsonPlague29 Excel Worksheet Functions 0 August 8th 07 09:44 PM
VLOOK-pivot table expanding want to update vlook automatically CrimsonPlague29 Excel Worksheet Functions 0 August 8th 07 09:44 PM
VLOOK UP Biruzz Excel Discussion (Misc queries) 1 December 8th 06 09:16 AM


All times are GMT +1. The time now is 04:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement