CODEWITHSUNDEEP
vbaexcel
Pablo
Pablo

Reputation: 534

Extracting values from each cell using a loop and printing in another tab

I have the following:

Sub loopCell()

Dim cell As range
For Each cell In range("p10:p200")

Next cell

End Sub

I want to extract the value of every cell that my loop iterates. After that, I want to print values in another sheet and store the values in a variable/array.

Upvotes: 0

Views: 257

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Why need a loop for this?

You can directly copy/assign the values. See this example

'~~> Change `A1:A190` to the relevant range when you want the output...
Sheets("Sheet2").Range("A1:A190").Value = Sheets("Sheet1").Range("P10:P1200").Value

or

'~~> Change `A1` to the relevant range when you want the output...
Sheets("Sheet1").Range("P10:P1200").Copy Sheets("Sheet2").Range("A1")

EDIT

As per user's updated request, he wants to store the values as well. In such a scenario, you can store the values to an array and then from there you can output it to the relevant sheet

Here is an example

Dim MyAr As variant

MyAr = Sheets("Sheet1").Range("P10:P1200").Value

If you want selected values from the array then loop through the array and then output it to the relevant sheet.

'~~> looping through the array
For i = LBound(MyAr) To Ubound(MyAr)
    Debug.Print MyAr(i,1)
Next i

If you would like to output the complete array to the worksheet in one go then you can do this as well 

Sheets("Sheet2").Range("A1").Resize(Ubound(MyAr),1).Value = MyAr

Upvotes: 2

Related Questions