Is it possible to use a variable in a variable?

[sbitaxi]

I'm sure my terminology is not correct, but the intention is there. I
have the following statement which combines cell values from several
into one.

For Each MyCell In
Range(PrefBAdd).SpecialCells(xlCellTypeVisible)
Do Until i = DestCols + 1
If MyCell.Row > 1 Then
Intersect(Range(HAdd), Rows(MyCell.Row)).Value
= Intersect(Rows(MyCell.Row), BAdd).Value _
& " " & Intersect(Rows(MyCell.Row),
BAdd.Offset(0, 1)).Value & " " _
& Intersect(Rows(MyCell.Row),
BAdd.Offset(0, 2)).Value & " " _
& Intersect(Rows(MyCell.Row),
BAdd.Offset(0, 3)).Value & " " _
& Intersect(Rows(MyCell.Row),
BAdd.Offset(0, 4)).Value
MyCell.Offset(0, -8).Value = MyCell.Offset(0,
6).Value
MyCell.Offset(0, -7).Value = MyCell.Offset(0,
7).Value
MyCell.Offset(0, -6).Value = MyCell.Offset(0,
8).Value
MyCell.Offset(0, -5).Value = MyCell.Offset(0,
9).Value
End If
i = i + 1
Exit Do
Loop
Next


Rather than using all those offsets, I'd prefer to use some form of
cross reference, it will make the code more flexible and easier to
apply to different data sets with different numbers of columns. Here
is my idea

Set FoundCell = Rng.Find(What:="MyHeaderRowValue"....
Set Hdr = Range(Cells(MyCell.Row, FoundCell.Column)

And then used in the above code like -

Hdr.Value = MyCell.Offset(0, 6).Value

How do I make the Hdr something that is dynamically assigned without
setting it again and again in a loop?

 

[Joel]

You ned to use nexted for loops


For Each MyCell In _
Range(PrefBAdd).SpecialCells(xlCellTypeVisible)

Do Until i = DestCols + 1
If MyCell.Row > 1 Then
set FirstCol = Intersect(Range(HAdd), Rows(MyCell.Row))
Data = ""
For ColOffset = 0 To 4
If ColOffset = 0 Then
Data = FirstCol.Value
Else
Data = Data & " " & _
FirstCol.Offset(0, ColOffset)
Next ColOffset
FirstCol.Value = Data

For ColOffset = 0 To 3

MyCell.Offset(0, ColOffset - 8).Value = _
MyCell.Offset(0, ColOffset + 6).Value
Next ColOffset

End If

i = i + 1
Exit Do
Loop
Next

 

[sbitaxi]

You ned to use nexted for loops

       For Each MyCell In _
           Range(PrefBAdd).SpecialCells(xlCellTypeVisible)

          Do Until i = DestCols + 1
             If MyCell.Row > 1 Then
                set FirstCol = Intersect(Range(HAdd), Rows(MyCell.Row))
                Data = ""
                For ColOffset = 0 To 4
                   If ColOffset = 0 Then
                      Data = FirstCol.Value
                   Else
                      Data = Data & " " & _
                         FirstCol.Offset(0, ColOffset)
                Next ColOffset
                FirstCol.Value = Data

                For ColOffset = 0 To 3

                   MyCell.Offset(0, ColOffset - 8).Value = _
                      MyCell.Offset(0, ColOffset + 6).Value
                Next ColOffset

             End If

             i = i + 1
             Exit Do
          Loop
       Next











- Show quoted text -

Hi Joel:

That cleans up the code and gives me a few ideas to take it further.
I'll report back later with my final solution. Thanks for your help!


S

 

[Joel]

You had a BAddr and HAddr. I didn't notice that difference I think you may
need to make tow changes


from
Data = FirstCol.Value
to
Data = BAdd.Value

from
Data = Data & " " & _
FirstCol.Offset(0, ColOffset)
to
Data = Data & " " & _
BAddr.Offset(0, ColOffset)

 

[sbitaxi]

You had a BAddr and HAddr.  I didn't notice that difference I think youmay
need to make tow changes

from
                  Data = FirstCol.Value
to
                  Data = BAdd.Value

from
                      Data = Data & " " & _
                         FirstCol.Offset(0, ColOffset)
to
                      Data = Data & " " & _
                         BAddr.Offset(0, ColOffset)








- Show quoted text -

Joel:

I should have read your code more carefully, as soon as I started to
manipulate it, I realized that it encompassed the changes I was going
to make. Thank you so much, this does exactly what I want!


Steven

 

[sbitaxi]

Joel:

I should have read your code more carefully, as soon as I started to
manipulate it, I realized that it encompassed the changes I was going
to make. Thank you so much, this does exactly what I want!

Steven- Hide quoted text -

- Show quoted text -

Joel:

This is my final code -

For Each MyCell In
Range(PrefBAdd).SpecialCells(xlCellTypeVisible)
Do Until i = DestCols + 1
If MyCell.Row > 1 Then
Set FirstCol = Intersect(Range(HAdd),
Rows(MyCell.Row))
Set SecCol = Intersect(Range(BAdd),
Rows(MyCell.Row))
Data = ""
For ColOffset = 0 To 4
If ColOffset = 0 Then
Data = SecCol.Value
Else
Data = Data & " " & _
SecCol.Offset(0, ColOffset)
End If
Next ColOffset

FirstCol.Value = Data
FirstCol.Formula = LTrim(FirstCol.Formula)
FirstCol.Formula = RTrim(FirstCol.Formula)

For ColOffset = 0 To 3
FirstCol.Offset(0, ColOffset + 5).Value = _
MyCell.Offset(0, ColOffset + 6).Value
Next ColOffset
End If
i = i + 1
Exit Do
Loop
Next