Mulit-column number formatting

[MikeM]

I'm trying to format a sequence of columns, but without success. I've used:

Sub x()

Dim i As Integer, iCol As Integer

For i = 1 To 47
iCol = 12 + 7 * (i - 1)
Range(Cells(, iCol), Cells(, iCol + 2)).NumberFormat = "0.00"
Next i
End Sub

I've tried columns(icol, icol+2) as well. Any ideas?

TIA,
Mike

 

[MikeM]

Of course, should be "multi-column."

 

[Dave Peterson]

How about:

Option Explicit
Sub x()

Dim i As Long
dim iCol As Integer

For i = 1 To 47
iCol = 12 + 7 * (i - 1)
activesheet.cells(1,icol).resize(1,2).entirecolumn.NumberFormat = "0.00"
Next i
End Sub

You must be using xl2007 for all those columns, right?

 

[MikeM]

Yes, I have Excel 2007. I tried your fixt, but it didn't seem to work. So
instead I used

Columns(iCol).NumberFormat = "0.00"
Columns(iCol + 1).NumberFormat = "0.00"
Columns(iCol + 2).NumberFormat = "0.00"

Why can't multiple columns be referenced?

 

[Dave Peterson]

I resized the range to 2 columns. I see that you wanted 3 columns.

Changing the .resize(1,2) to .resize(1,3) should work ok.

But I don't know why it didn't work for you. It worked fine for me in xl2003
(until I ran out of columns).

Maybe you could explain how it failed.