VBA Iterate through cells

Question

I'm looking for a way to iterate through the cells in the worksheet to paste in values.

The code posted below will not recognize the cell locations. Is there a way to loop the cell location to progressively paste the information into the desired spaces? I want the code to paste the value into A1:C1 first and then move over 4 spaces and post to E1:G1 and so on.

Unsure how to iterate the letters to move the cell over.

Sub test()

Dim x As Integer
Dim y As Integer
Dim InputSheet As Worksheet
Dim myBook As Workbook

Set myBook = Excel.ActiveWorkbook
Set InputSheet = myBook.Sheets("InputSheet")

For y = 0 To 5
    x = 4
    InputSheet.Range("A1 + 4*y : C1 + 4*y").Value = x
Next y

End Sub

[`.Offset()` and `.Resize()`](https://www.businessprogrammer.com/power-excel-vba-secret-avoid-using-select/) are your friends. — John Alexiou, Feb 25 '18 at 03:27

score 2 · Answer 1 · answered Dec 30 '16 at 00:32

2

I'd try using "Cells" to do this.

Sub test()

Dim x As Integer
Dim y As Integer
Dim InputSheet As Worksheet
Dim myBook As Workbook

Set myBook = Excel.ActiveWorkbook
Set InputSheet = myBook.Sheets("InputSheet")

For y = 0 To 5
    x = 4
    InputSheet.Range(InputSheet.Cells(1, 4 * y + 1), InputSheet.Cells(1, 4 * y + 3)).Value = x
Next y

End Sub

answered Dec 30 '16 at 00:32

Rubikkon

36
4

That's good to know I can translate the cell locations straight to integers instead of dealing with the letters. Especially when they cross over to AA. – SgtYui Dec 30 '16 at 00:44

score 2 · Answer 2 · answered Dec 30 '16 at 00:37

2

You could use the Offset function.

For i = 0 to 5
    x = 4
    InputSheet.Range("A1:C1").Offset(0, i * 4) = x
Next i

answered Dec 30 '16 at 00:37

Steven Harrison

31
4

Luke Kot-Zaniewski · Accepted Answer · 2016-12-30T01:22:38.853

1

You are telling the workbook to literally look for range "A1 + 4*y : C1 + 4*y" which obviously isn't a valid address. You need to evaluate the numerical expression outside the string, convert back to a string (either explicitly using Cstr or you can put the expression in parentheses and let the compiler do it for you as VBA is dynamically typed which is an important concept you might want to look up. Basically it's able to figure out from the context that it's dealing with a string type variable) and finally tack it back onto your address for this to work.

Since you seem to be new to vba/coding I would advise you to figure out how to use breakpoints and watches to see how your machine actually evaluates your variables.

InputSheet.Range("A" & (1+4*y) & ":C" & (1+4*y)).Value = x

edited Dec 30 '16 at 01:22

answered Dec 30 '16 at 00:30

Luke Kot-Zaniewski

1,161
11
12

1

Thanks for the info, the breakpoints and watches definitely seem like better ways to identify issues in the code. Before I would be adding msgbox throughout the code to check values of variables to see where things went wrong. – SgtYui Dec 30 '16 at 00:43
Hey we all started there :-) figuring out how to debug in the most efficient way is a big milestone (and I still have trouble with it sometimes haha) Good luck!! – Luke Kot-Zaniewski Dec 30 '16 at 00:51
1

FWIW `Cstr` wrapper is not needed here @SgtYui – Scott Holtzman Dec 30 '16 at 01:06
@Sott Holtzman you are right! Didnt know so I instictively played it safe but was clearly not needed here. More reading http://stackoverflow.com/questions/11595226/how-do-i-convert-an-integer-to-a-string-in-excel-vba seems like cstr is pretty useless method. – Luke Kot-Zaniewski Dec 30 '16 at 01:20
Noo. using string math as in `.Range("A" & (1+4*y) & ":C" & (1+4*y))` is not best practice. It is _much_ better to do `.Range("A1").Offset(4*y,0).Resize(1,3)` because you are keeping the location and size as integers. – John Alexiou Feb 25 '18 at 03:04

score 0 · Answer 4 · edited Feb 25 '18 at 01:53

I did the following code because I was having some runtime errors as well. Hope it helps.

Dim wb As Workbook

Dim ws As Object

Set wb = Workbooks("Libro2")

Set ws = wb.Sheets("Hoja1")

For i = 1 To 10000

        Dim strrangoa As String
        Dim strrangob As String
            'Creating a string variable replaces selecting the cell
            strrangoa = "A" & i
            strrangob = "B" & i
            'If the active cell does not have data, it exits the loop
            If ws.Range(strrangoa).Value = "" Then
                GoTo Salir
            Else
            'The data from cells in Column A are passed to Column B
                ws.Range(strrangob).Value = ws.Range(strrangoa).Value
            End If

Next

Salir:

End Sub

John Alexiou · Answer 5 · 2018-02-25T03:24:59.380

Ok, every other answer here has failed to use the .Resize() function to select a range of cells which is the recommended way.

Option Explicit

Sub Test()

    Dim x As Integer
    Dim y As Integer
    Dim InputSheet As Worksheet
    Dim myBook As Workbook

    Set myBook = Excel.ActiveWorkbook
    Set InputSheet = myBook.Sheets("InputSheet")

    For y = 0 To 5
        x = 4
        ' Start for "A1", move down 4*y rows, and select
        ' one row and 3 columns to set the value
        InputSheet.Range("A1").Offset(4*y, 0).Resize(1, 3).Value = x

        ' Similar to using the `.Cells()` method
        ' InputSheet.Range("A1").Cells(4*y+1, 1).Resize(1, 3).Value = x
    Next y       

End Sub

PS. You can also do things like

InputSheet.Range("A2").Resize(1,3).Value = Array(1,2,3)

To set cells A2,B2,C2 to 1,2,3

or

InputSheet.Range("A2").Resize(3,1).Value = _ 
          WorksheetFunction.Transpose(Array(1,2,3))

To set cells A2,A3,A4 to 1,2,3

Read here from Microsoft on how to use .Resize(), .Offset() and .Cells().

VBA Iterate through cells

5 Answers5