Is it possible to loop over N columns when N is a number the user entered?

[monsefoster]

Hi, I was wondering if it's possible to ask the user a number (I know how to do this) and then, beginning with let's say G column, looping over each column depending on the number the user entered?

Example:

User Input = 4

[TABLE="width: 500"]
<tbody>[TR]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Item_G1[/TD]
[TD]Item_H1[/TD]
[TD]Item_I1[/TD]
[TD]Item_J1[/TD]
[TD]Item_...n1[/TD]
[/TR]
[TR]
[TD]Item_G2[/TD]
[TD]Item_H2[/TD]
[TD]Item_I2[/TD]
[TD]Item_J2[/TD]
[TD]Item_...n2[/TD]
[/TR]
[TR]
[TD]Item_G3[/TD]
[TD]Item_H3[/TD]
[TD]Item_I3[/TD]
[TD]Item_J3[/TD]
[TD]Item_...n3[/TD]
[/TR]
[TR]
[TD]Item_G4[/TD]
[TD]Item_H4[/TD]
[TD]Item_I4[/TD]
[TD]Item_J4[/TD]
[TD]Item_...n4[/TD]
[/TR]
</tbody>[/TABLE]


Meaning, Input a number and then depending on the number looping also on that letter+the number of letters. So, After it's done with all the G's items looping over the H's items, and so on... N numbers could be the input. I saw something similar a while back but now I can't find it

 

[FormR]

Something like this?

Sub tt()
Dim inputNum As Long, startCol As Long, i As Long
Dim c As Range

startCol = 7 'Column G
inputNum = 4 'User Input

For i = startCol To inputNum + startCol
    For Each c In Cells(1, i).Resize(Cells(Rows.Count, i).End(xlUp).Row)
        Debug.Print c.Address
     Next c
Next i

End Sub

 

[monsefoster]

That seems to work, thank you, but why if I use an input box and press 6, it shows

$G$1$G$2
$G$3
$G$4
$G$5
$G$6
$G$7
$G$8
$G$9
$G$10
$H$1
$I$1
$J$1
$J$2
$J$3
$J$4
$K$1
$K$2
$K$3
$K$4
$K$5
$K$6
$K$7
$K$8
$K$9
$K$10
$K$11
$K$12
$K$13
$K$14
$K$15
$K$16
$K$17
$K$18
$K$19
$K$20
$K$21
$K$22
$L$1
$L$2
$L$3
$L$4

Shouldn't it go just G,H,I,J,K,L? and some defined range, like 10 each?

 

[Jonmo1]

That code made 2 loops (1 inside another)
1 Loop loops each cell in the row based on the begin column for x number of columns based on the userinput.
But inside that loop, is another loop that goes through each cell in each given column found by the main loop.

Perhaps that should just be

For i = startCol To inputNum + startCol
        Debug.Print Cells(1,i).Address
Next i

 

[FormR]

It's looping from row 1 to the last populated cell of each column.

If you want to loop to some pre-determined end row of each column change this line:

For Each c In Cells(1, i).Resize(Cells(Rows.Count, i).End(xlUp).Row)

to:

For Each c In Cells(1, i).Resize(10)

If this (or what Jonmo suggested) is not what you need, it may be better for you to explain the end goal in a bit more detail.

EDIT in red

 

[monsefoster]

Thank you, I had the other loop already and this turned out to be a little confusing. I'm going to try both of your suggestions and see if it works.

 

[monsefoster]

Thank you guys again. I did this:

        Dim firstSite As Long, i As Long
        Dim Site As Range
        Dim cantidad_sitios As Variant
        firstSite = 7 'First Site 'G Letter
         cantidad_sitios = InputBox("Por Favor ingrese cantidad de sitios en Lista de Materiales: ", "Cantidad de Sitios")
        For i = firstSite To (cantidad_sitios - 1) + firstSite ' Sites-1 otherwise It takes 1 column more
           ' Getting Site Name & Code
            SiteName = MaterialListSheet.Range(Split(Cells(1, i).Address, "$")(1) & 


    "4").Value
                SiteCode = MaterialListSheet.Range(Split(Cells(1, i).Address, "$")(1) & "3").Value
    'Split(Cells(1, i).Address, "$")(1) returns just the column letter 'G,H,I,J...
    'And I needed just the letter number to access (G3,H3,I3...) and get some values.
    
    'Insert loop of rows here. 
        Next i

I couldn't have done it without you. Thank you very very much