VBA Looping through a table and copying values

[cdell80]

I have a table of randomly dispersed numbers and want to loop through and copy the row name, column name, and value to new locations on a different worksheet. If the value is blank, I can just skip over it. I am fairly new to VBA and am just trying to get a sense of how to set this up. When copying the values to the new worksheet, they can just be put in order such as A1, B1, C1. Thanks for the help! Here is an example below:

	1	2	3	4
A	2
B			3	1
C	4	4		1
D				1

<tbody>
</tbody>

 

[ChrisM]

Couple ways to handle this, here's one:

Dim rCell as Range
Dim i as Long

i = 1

For Each rCell in Worksheets("yoursheet").Range("A1:D4")
      If rCell <> "" Then
            Worksheets("DestinationSheet").Range("A" & i) = rCell.Value
            i = i +1
      End If
Next rCell

 

[cdell80]

This worked very well, thanks Chris!

Another question. If I want to pull the row name for all values in that row, how would that be coded? I would try an offset, but the offset would change based on the column that the value is in.

Thanks again for the advice.

 

[ChrisM]

what do you mean by row name? the row number? the value in column A?

 

[cdell80]

what do you mean by row name? the row number? the value in column A?

For any value in column "A" I want a corresponding cell next to the destination of the other values in the table to state "A". If the table value is in row B I want that new row to state "B"

 

[ChrisM]

Ok, this will output column letter in column A and value in column B:

Dim rCell as Range
Dim i as Long

i = 1

For Each rCell in Worksheets("yoursheet").Range("A1:D4")
      If rCell <> "" Then
            Worksheets("DestinationSheet").Range("A" & i) = Left(rCell.address(1,0), InStr(1, rCell.address(1,0), "$") - 1)
            Worksheets("DestinationSheet").Range("B" & i) = rCell.Value
            i = i +1
      End If
Next rCell