CODEWITHSUNDEEP
vbaexcelrow
mcadamsjustin
mcadamsjustin

Reputation: 361

Get the value of a cell in range, store it a variable, then next cell, next row (Excel)

I'd like to get the value of a cell in a range and store it as a variable. And then set the value of another cell in another range as that variable. Then move on to the cell. After all the cells in the row are captured, I'd like to move to the next row and start again with the first cell and first variable. I can't figure out how to move to the next cell and then set the value in the new range to the variable. Here's what I got so far

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim card1 As String
Dim card2 As String
Dim card3 As String
Dim card4 As String
Dim card5 As String
Dim valueRng As Range

Set rng = Range("A2:D4")
Set valueRng = ("F2:I4")


For Each row In rng.Rows
 For Each cell In row.Cells
 card1 = cell.Value
 'Set first variable
 'Code here for setting value of for first cell in valueRng

'Move to second cell
'get value
'set variable card2
'Set value of second cell in valueRng

Next cell  
Next row

Upvotes: 0

Views: 45411

Answers (1)

Rory
Rory

Reputation: 34035

Use row and column counters instead of For each:

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim card1 As String
Dim card2 As String
Dim card3 As String
Dim card4 As String
Dim card5 As String
Dim valueRng As Range
Dim x as long
Dim y as long

Set rng = Range("A2:D4")
Set valueRng = Range("F2:I4")


For x = 1 to rng.Rows.Count
   for y = 1 to rng.Columns.Count

 card1 = rng.cells(x, y).Value
 'Set first variable
 'Code here for setting value of for first cell in valueRng
 valueRng.Cells(x, y).Value = card1

Next y  
Next x

Note that there are far simpler ways of populating one array from another when they are the same size and shape.

Upvotes: 3

Related Questions