Syntax to Set variable range

[Mike Fogleman]

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

 

[Mike Fogleman]

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

 

[Dave Peterson]

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.)

 

[Leith Ross]

Hello Mike,

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

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

Sincerely,
Leith Ros