S
sbitaxi
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'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