FOR i =

[lecet444]

what is the alternative if you are trying to use it for columns, i would like to fill columns across as oppose to rows down!!

thank You,

 

[MrKowz]

You can name the variable whatever you want (just don't start with numbers, use special characters, or choose reserved words like function names, properties, etc).
It is always wise to declare your variables before using them, i.e.

Dim i as Integer
Dim j as Integer
...

Another recommendation is to always DIM your loop variables as Long. Not only will it prevent potential issues if your variable gets beyond 32,768, but I do believe that any variable dimmed as an Integer ends up using the same amount of memory as a Long variable, so there is no performance issues when using Long vs Integer.

 

[lecet444]

bk2.Activate
Application.CutCopyMode = False
bk3.Activate
 
Dim ccell As Integer
Dim myrow As Long
'Dim mycol As Long
'For mycol = 4 To 6
'Cells(4, mycol).Value = mycol
Dim clast As Long
clast = bk3.Range("B" & bk2.Cells.Rows.Count).End(xlUp).Row
For myrow = 3 To clast

ccell = Sheets("raw data").Range("b" & myrow).Value
 

Worksheets("raw data").Range("c" & myrow).Select
ActiveCell.FormulaR1C1 = "='F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R33C19"
'Worksheets("raw data").Range("mycol" & myrow).Select
'ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C3:R30C3,MATCH(" & mycol & ",'F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C4:R30C4,0))),"""",(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C3:R30C3,MATCH(" & mycol & ",'F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C4:R30C4,0))))"
 
 'ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C3:R30C3,MATCH(R1C4,'F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C4:R30C4,0))),"""",(INDEX('F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C3:R30C3,MATCH(R1C4,'F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R16C4:R30C4,0))))"
 
 Next myrow
 'Next mycol

I still cant seem to get it to work....

 

[Joe4]

jonmo1's reply in post #5 shows you how you can next two loops, one for rows and the other for columns. You can easily flip them, if you want to next the row loop inside of the column loop.

 

[lecet444]

still not sure how to fill columns and rows at the same time, i could do one column at a time, but to jump to the next and fill. I cant figure out how to use myrow and mycol....maybe someone could supply me with an example

 

[lecet444]

jonmo1's reply in post #5 shows you how you can next two loops, one for rows and the other for columns. You can easily flip them, if you want to next the row loop inside of the column loop.

I think if there is a bit longer example i could understand it better. If you can help

 

[Joe4]

Let's take jonmo1's example, but pick two different number sets to see a little more clearly how it works:

Sub MyLoopTest
 
    Dim MyRow as Integer
    Dim MyCol as Integer
 
    For MyRow = 10 to 20
        For MyCol = 1 to 5
            Cells(MyRow,MyCol)= "Row " & MyRow & ":Col " & MyCol
        Next MyCol
    Next MyRow
 
End Sub

Now, go into the VB Editor and Cut and Paste this code there. Resize the VB Editor size to about one-quarter of your screen so you can see your worksheet in the background. Now, highlight the first line of the code, and press the F8 key. This will execute one line of the code at a time. If you keep pressing F8, you can see your code manually go through and populate one cell at a time. So you should see how the code is working.

If you like, you can try the same thing with the code flipped around for rows and columns, i.e.

Sub MyLoopTest2
 
    Dim MyRow as Integer
    Dim MyCol as Integer
 
    For MyCol = 1 to 10
        For MyRow = 50 to 55
            Cells(MyRow,MyCol)= "Row " & MyRow & ":Col " & MyCol
        Next MyRow
    Next MyCol
 
End Sub

 

[lecet444]

ok what does this mean

Cells(MyRow,MyCol)= "Row " & MyRow & ":Col " & MyCol

is this a formula...I dont get how i can put a formula in there

 

[Joe4]

is this a formula...I dont get how i can put a formula in there

This is example is just placing text and the values of the MyCol and MyRow variables from the loop in the appropriate cells so you can see what is going on.
If you want to enter a formula, you would just use the same ".FormulaR1C1" logic as you have in your code, i.e.

Cells(MyRow,MyCol).FormulaR1C1 =...

 

[lecet444]

ohhh that was what was confusing me....thank you

 

[lecet444]

the F8 function starts from the begging of my code. anyway i could start just from that part. thanks