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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

Hello Mike,

Change: Set xrng = Range("a:b")

To: Set xrng = Range( a &":" & b)

Sincerely,
Leith Ross

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