Looping through a range & getting values

[sergv]

I am having a difficulty of looping through a range and getting values for each cell in each row. For example, I have a range B1:D2 with following values B1, C1, D1 in first row and values B2, C2, D2 in second row (for simplicity sake). What I would like to do is loop throuh each cell in each row and assign it to cells A1 & A2 respectively, with each value separated by a new line.
I can loop through all the cells, but having difficulty looping through all value in each row.

Thanks!

[Ken Johnson]

Hi sergv,
I could be wrong, but it looks to me like you want A1 to have all the
values in B1,C1 and D1, with an in-cell line break (the equivalent of
Alt + Enter) between each value, so that A1 will look like (using your
suggested values)...

B1
C1
D1

and then similarly for A2 so that it will look like ...

B2
C2
D2

If I am correct then you could use the following macro that does
exactly that using the range of cells you select either before running
the macro or when the inputbox appears...

Public Sub RowToPreviousCell()
Dim rgRowCells As Range
Dim iFirstRow As Long
Dim iLastRow As Long
Dim iFirstColumn As Integer
Dim iLastColumn As Integer
Dim iColumnCounter As Integer
Dim iRowCounter As Long
Dim strCellText As String
Set rgRowCells = Application.InputBox( _
prompt:="Please Select Cells Who's Values" & Chr(10) _
& "Are To Be Stacked Into" & Chr(10) _
& "The Previous Column's Cells.", _
Default:=Selection.Address, _
Type:=8)
If rgRowCells.Cells(1, 1).Column = 1 Then
MsgBox _
"There are no cells to the left of the selected range!" _
& Chr(10) & "Try Again."
Exit Sub
End If
iFirstRow = rgRowCells.Cells(1, 1).Row
iLastRow = iFirstRow + rgRowCells.Rows.Count - 1
iFirstColumn = rgRowCells.Cells.Column
iLastColumn = iFirstColumn + rgRowCells.Columns.Count - 1
For iRowCounter = iFirstRow To iLastRow
strCellText = ""
For iColumnCounter = iFirstColumn To iLastColumn
strCellText = strCellText _
& Cells(iRowCounter, iColumnCounter) _
& IIf(iColumnCounter = iLastColumn, "", Chr(10))
Next iColumnCounter
Cells(iRowCounter, iFirstColumn - 1).Value = strCellText
Next iRowCounter
End Sub

Ken Johnson

[Tom Ogilvy]

in A1 put in

=b1&char(10)&c1&char(10)&d1
then drag fill down the column

Select the column and do Format =>Cells, go to the alignment tab and select
wrap text.

to replace the formulas with hard coded values, select the column and do
Edit=>Copy, then Edit=>Paste special and select values.

--
Regards,
Tom Ogilvy


"sergv" wrote:

>
> I am having a difficulty of looping through a range and getting values
> for each cell in each row. For example, I have a range B1:D2 with
> following values B1, C1, D1 in first row and values B2, C2, D2 in
> second row (for simplicity sake). What I would like to do is loop
> throuh each cell in each row and assign it to cells A1 & A2
> respectively, with each value separated by a new line.
> I can loop through all the cells, but having difficulty looping through
> all value in each row.
>
> Thanks!
>
>
> --
> sergv
> ------------------------------------------------------------------------
> sergv's Profile: http://www.excelforum.com/member.php...o&userid=26927
> View this thread: http://www.excelforum.com/showthread...hreadid=523427
>
>