Variable Column in a loop maco

[Chris8630]

Hi all

I suspect I've missed something basic in this but I'm trying to get the macro to work across a variable number of columns which are defined in A1. The loop will always start in column 28 and then go out the specified number of times

Sub RefreshPurchasing()
Dim a, b, MAXVALUE, lcol As Long
Sheets("Purchasing Output").Select
lcol = 28
MAXVALUE = Sheets("Purchasing Output").Range("A1") * 2
For b = 1 To MAXVALUE
Range(Columns(lcol) & 2).Select
Range((ActiveCell.Column) & 2) = "=IFERROR(INDEX(SheetNames," & (ActiveCell.Column) & "2), """")"
lcol = lcol + 1
b = b + 1
Next b
b = MAXVALUE
Application.ScreenUpdating = True
End Sub

If there is a better way to achieve refreshing the sheet names in the cells or improving the above I'll take it all on board.

Thanks in advance

 

[Joe4]

One issue I see right off the bat is that "ActiveCell.Column" returns the column number, and not the column letter, and "Range" definitely wants a letter.
Note that in VBA you can also use "Cells" to refer to ranges. With "Cells", you can use the column letter OR the column number. The format of cells is Cells(row, column)

So, if you wanted to refer to cell Z10 in VBA, all three of the following would do that (they are all equivalent):
Range("Z10")
Cells(10, "Z")
Cells(10, 26)

With that in mind, you can fix this line:

Range(Columns(lcol) & 2).Select

by using this:

Cells(2, Columns(lcol)).Select

though I am not sure that you need this row anyway (usually not necessary to select ranges in VBA, and I don't see what benefit doing that does in your code).

And this:

Range((ActiveCell.Column) & 2) = "=IFERROR(INDEX(SheetNames," & (ActiveCell.Column) & "2), """")"

could be rewritten something like this:

Cells(2, ActiveCell.Column).Formula = "=IFERROR(INDEX(SheetNames," & Cells(2, ActiveCell.Column).Address, """")"

Note: There may be other errors (in logic or in code) in your posting. I cannot be sure, as I cannot quite tell for certain exactly what it is that you are trying to do, and how your data is structured.