Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Set variable range
I can't seem to nail the syntax to set a range from two cells.
Excerpt: Option Explicit Public prng As Range Sub USFilter() Dim a As String, b As String, vl As String Dim rw As Long Dim drng As Range, xrng As Range, c As Range Set drng = Worksheets("US Analysis").Range("H1") '...some other code rw = Cells(Rows.Count, drng.Column).End(xlUp).row a = drng.Offset(1, -1).Address b = drng.Offset(rw - 1, -1).Address Set xrng = Range("a:b") 'can't get this right For Each c In xrng vl = c.Offset(0, 1) c.Value = WorksheetFunction.VLookup(vl, prng, 2, False) Next c End Sub The VLookup works fine if I set the xrng manually. The reference to drng will change each time this sub is ran according to ...some other code. Any input would be appreciated. Thnx Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Set variable range
Never mind. I been seeing too many trees to find the forest.
Set xrng = Range(drng.Offset(1, -1), drng.Offset(rw - 1, -1)) Works Mike F "Mike Fogleman" wrote in message ... I can't seem to nail the syntax to set a range from two cells. Excerpt: Option Explicit Public prng As Range Sub USFilter() Dim a As String, b As String, vl As String Dim rw As Long Dim drng As Range, xrng As Range, c As Range Set drng = Worksheets("US Analysis").Range("H1") '...some other code rw = Cells(Rows.Count, drng.Column).End(xlUp).row a = drng.Offset(1, -1).Address b = drng.Offset(rw - 1, -1).Address Set xrng = Range("a:b") 'can't get this right For Each c In xrng vl = c.Offset(0, 1) c.Value = WorksheetFunction.VLookup(vl, prng, 2, False) Next c End Sub The VLookup works fine if I set the xrng manually. The reference to drng will change each time this sub is ran according to ...some other code. Any input would be appreciated. Thnx Mike F |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Set variable range
Maybe just drop the addresses and use the cells themselves would be easier:
Option Explicit Sub USFilter() Dim aCell As Range Dim bCell As Range Dim vl As String Dim rw As Long Dim dRng As Range Dim xRng As Range Dim c As Range Dim pRng As Range Set pRng = Worksheets("sheet1").Range("a:e") With Worksheets("US Analysis") Set dRng = .Range("H1") '...some other code rw = .Cells(.Rows.Count, dRng.Column).End(xlUp).Row Set aCell = dRng.Offset(1, -1) Set bCell = dRng.Offset(rw - 1, -1) Set xRng = .Range(aCell, bCell) For Each c In xRng vl = c.Offset(0, 1) c.Value = Application.VLookup(vl, pRng, 2, False) Next c End With End Sub ps. I changed your worksheetfunction.vlookup() to application.vlookup(). If you use worksheetfunction.vlookup(), then your code will explode if no match is found. If you use application.vlookup(), you'll just get the #n/a error returned. I also changed the Dim's to one per line--personal preference only. (I find it easier to change stuff that way.) Mike Fogleman wrote: I can't seem to nail the syntax to set a range from two cells. Excerpt: Option Explicit Public prng As Range Sub USFilter() Dim a As String, b As String, vl As String Dim rw As Long Dim drng As Range, xrng As Range, c As Range Set drng = Worksheets("US Analysis").Range("H1") '...some other code rw = Cells(Rows.Count, drng.Column).End(xlUp).row a = drng.Offset(1, -1).Address b = drng.Offset(rw - 1, -1).Address Set xrng = Range("a:b") 'can't get this right For Each c In xrng vl = c.Offset(0, 1) c.Value = WorksheetFunction.VLookup(vl, prng, 2, False) Next c End Sub The VLookup works fine if I set the xrng manually. The reference to drng will change each time this sub is ran according to ...some other code. Any input would be appreciated. Thnx Mike F -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax to Set variable range
Hello Mike, Change: Set xrng = Range("a:b") To: Set xrng = Range( a &":" & b) Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=49245 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting a range of rows based on a variable; syntax error | Excel Discussion (Misc queries) | |||
macro syntax for selecting variable range | Excel Discussion (Misc queries) | |||
SaveAs syntax when using a variable | Excel Programming | |||
Syntax for variable search | Excel Programming | |||
Syntax for variable search | Excel Programming |