VBA Looping through a table and copying values

cdell80

New Member
Joined
Jun 22, 2015
Messages
18
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:
1234
A2
B31
C441
D1

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
Couple ways to handle this, here's one:

Code:
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

New Member
Joined
Jun 22, 2015
Messages
18
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
what do you mean by row name? the row number? the value in column A?
 

cdell80

New Member
Joined
Jun 22, 2015
Messages
18
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
Ok, this will output column letter in column A and value in column B:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,508
Messages
5,596,555
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top