Hello:

I'm using the following code to dynamically assign addresses based on
field headers for a report. They are address fields that are broken up
into 5 columns that I concatenate into 1 column. When I use
Intersect(ActiveSheet.UsedRange, Range("B:B, Z:Z")) it works fine, but
if I change to Intersect(ActiveSheet.UsedRange, Range(HAdd, BAdd)) it
treats it as Range(B:Z), how do I change this?

Here's the code!

Sub headername()
Dim MyCell As Range
Dim Rng As Range
Dim LRow As Integer
Dim HAdd As Range
Dim BAdd As Range

LRow = LastRow(ActiveSheet)
Set Rng = Range("A2:FF" & LRow)

'* Dynamically assigns addresses for HAdd and BAdd

'* Set HAdd
Set HAdd = Cells.Find(What:="HomeAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
HAdd = (HAdd.EntireColumn.Address)

'* Set BAdd
Set BAdd =
Cells.Find(What:="BusinessAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
BAdd = (BAdd.EntireColumn.Address)

'* Concatenates 5 address columns for Each HAdd and BAdd
For Each MyCell In Intersect(ActiveSheet.UsedRange,
Range(HAdd, BAdd))
MyCell.Formula = MyCell.Value & " " _
& MyCell.Offset(0, 1).Value & " " _
& MyCell.Offset(0, 2).Value & " " _
& MyCell.Offset(0, 3).Value & " " _
& MyCell.Offset(0, 4).Value
MyCell.Formula = LTrim(MyCell.Formula)
MyCell.Formula = RTrim(MyCell.Formula)
Next
End Sub

I think the problem is the location of the comma. Notice in this...

Range("B:B, Z:Z")

the comma is part of the text (it is inside the quote marks), but in this...

Range(HAdd, BAdd)

the comma is not part of the text. My guess is that this will do what you
want...

Range(HAdd & "," & BAdd)

But that may not be your entire problem as you have declared HAdd and BAdd
to be ranges, but you assigned String values (the Address property of the
range found by your Find function call) to them. Remove the Address property
call (and those surrounding parentheses... using parentheses not required by
syntax can sometimes cause unexpected results, though that is not the case
here I don't think) and use a Set statement (because we are now working with
a real Range)...

Set HAdd = HAdd.EntireColumn
......
Set BAdd = BAdd.EntireColumn

although you can just tack the EntireColumn property right onto the returned
range from the Find function call (omitting the extra Set statement in the
process) and that should work fine too....

Set HAdd = Cells.Find(What:="HomeAddressLine1", After:=ActiveCell, _
LookIn:=xlFormulas, Lookat:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).EntireColumn
......
Set BAdd = Cells.Find(What:="BusinessAddressLine1", After:=ActiveCell, _
LookIn:=xlFormulas, Lookat:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).EntireColumn

Rick


wrote in message
...
Hello:

I'm using the following code to dynamically assign addresses based on
field headers for a report. They are address fields that are broken up
into 5 columns that I concatenate into 1 column. When I use
Intersect(ActiveSheet.UsedRange, Range("B:B, Z:Z")) it works fine, but
if I change to Intersect(ActiveSheet.UsedRange, Range(HAdd, BAdd)) it
treats it as Range(B:Z), how do I change this?

Here's the code!

Sub headername()
Dim MyCell As Range
Dim Rng As Range
Dim LRow As Integer
Dim HAdd As Range
Dim BAdd As Range

LRow = LastRow(ActiveSheet)
Set Rng = Range("A2:FF" & LRow)

'* Dynamically assigns addresses for HAdd and BAdd

'* Set HAdd
Set HAdd = Cells.Find(What:="HomeAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
HAdd = (HAdd.EntireColumn.Address)

'* Set BAdd
Set BAdd =
Cells.Find(What:="BusinessAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
BAdd = (BAdd.EntireColumn.Address)

'* Concatenates 5 address columns for Each HAdd and BAdd
For Each MyCell In Intersect(ActiveSheet.UsedRange,
Range(HAdd, BAdd))
MyCell.Formula = MyCell.Value & " " _
& MyCell.Offset(0, 1).Value & " " _
& MyCell.Offset(0, 2).Value & " " _
& MyCell.Offset(0, 3).Value & " " _
& MyCell.Offset(0, 4).Value
MyCell.Formula = LTrim(MyCell.Formula)
MyCell.Formula = RTrim(MyCell.Formula)
Next
End Sub

Range is is ofteen misunderstood. It takes two arguments. The second is
optional. If I specify only the first argument then it is the range defined
by that address. If I specify both then it is the rectangular area between
the 2 addresses.

Use this...
Intersect(HAdd.Parent.UsedRange, Union(HAdd, Badd))

Note that I used Hadd.Parent in place of activesheet. Activesheet is kind of
dangerous as code changes can alter the active sheet and generate an error on
this line. By using the parent of the range Hadd which is the sheet that Hadd
is on we avoid this potential problem...
--
HTH...

Jim Thomlinson


" wrote:

Hello:

I'm using the following code to dynamically assign addresses based on
field headers for a report. They are address fields that are broken up
into 5 columns that I concatenate into 1 column. When I use
Intersect(ActiveSheet.UsedRange, Range("B:B, Z:Z")) it works fine, but
if I change to Intersect(ActiveSheet.UsedRange, Range(HAdd, BAdd)) it
treats it as Range(B:Z), how do I change this?

Here's the code!

Sub headername()
Dim MyCell As Range
Dim Rng As Range
Dim LRow As Integer
Dim HAdd As Range
Dim BAdd As Range

LRow = LastRow(ActiveSheet)
Set Rng = Range("A2:FF" & LRow)

'* Dynamically assigns addresses for HAdd and BAdd

'* Set HAdd
Set HAdd = Cells.Find(What:="HomeAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
HAdd = (HAdd.EntireColumn.Address)

'* Set BAdd
Set BAdd =
Cells.Find(What:="BusinessAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
BAdd = (BAdd.EntireColumn.Address)

'* Concatenates 5 address columns for Each HAdd and BAdd
For Each MyCell In Intersect(ActiveSheet.UsedRange,
Range(HAdd, BAdd))
MyCell.Formula = MyCell.Value & " " _
& MyCell.Offset(0, 1).Value & " " _
& MyCell.Offset(0, 2).Value & " " _
& MyCell.Offset(0, 3).Value & " " _
& MyCell.Offset(0, 4).Value
MyCell.Formula = LTrim(MyCell.Formula)
MyCell.Formula = RTrim(MyCell.Formula)
Next
End Sub