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,

 

[Jonmo1]

You can still use a For i = loop structure...


For i = 1 to 10
Cells(1,i).Value = i
Next i

that will put the numbers 1 through 10 in row 1 column A through J

 

[lecet444]

Thank you, I shall try it

 

[lecet444]

this is probably a very stupid question,
i have already used for i = 1 to 9

can i use for j
For j= 1 to 10
Cells(1,j).Value = j
Next j

 

[Jonmo1]

Sure, but when you start having multiple variables, you might give them descriptive names to help keep track of what each variable is for.
Like, MyCol and MyRow...

For MyRow = 1 To 10
    For MyCol = 1 To 5
        Cells(MyRow,MyCol).Value = MyRow & " - " & MyCol
    Next MyCol
Next MyRow

 

[lecet444]

Thank You, you've been more than helpful, and really good example

Mike

 

[Joe4]

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
...

 

[Jonmo1]

In addition to what Joe said,

It is also wise to put this at the top of your module.

Option Explicit

This forces you to declare your variables.
It also helps to prevent mistakes like Misspelling things (a major cause of many headaches)

 

[Joe4]

It is also wise to put this at the top of your module.

Option Explicit

This forces you to declare your variables.

An excellent suggestion. Really helps with debugging errors.

 

[lecet444]

thank you for your feedback. really helpful.

if I want to run them side by sid eg

Dim ccell As Integer
Dim i As Integer
Dim clast As Long
clast = bk3.Range("B" & bk2.Cells.Rows.Count).End(xlUp).Row
For i = 3 To clast

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

Worksheets("raw data").Range("c" & i).Select
ActiveCell.FormulaR1C1 = "='F:\Engineering\Michael\Excel\INVENTORY\PO\[" & ccell & ".xls]PO'!R33C19"
[COLOR=red]Worksheets("raw data").Range("d" & i).Select[/COLOR]
[COLOR=red]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))))"
[/COLOR] Next i

how would i have that code (one in red) jump to the next column and then the next and so on since it has i in it, so it goes down. I want it to go down and across