Loop through Columns
 

Hi,

I have a spreadsheet that is formatted the following way:

Jan Feb Mar Apr May
Jun
Actual Actual Actual Actual Forecast Forecast
6000 8000 6500 7500 8000
6000

I need to run a macro each month that loops thru the columes and
highlights the last Actual column and pastes formulas and formatting
into the first Forecast column. The number of columns change each month
as we forecast out further.

Any help would be greatly appreciated.

Thanks

Sally

Loop through Columns
 

You can use the following VBA code to identify the next empty column, set
the value of xr to be the row where the data resides, you can loop this
value if there are many rows to test.

xr = 1
xNextEmptyCol = Cells(xr, Columns.Count).End(xlToLeft).Column + 1

then use the value to set the fomula into the column eg...

Cells(xr,xNextEmptyCol).Formula = "your formula"



--
Cheers
Nigel



"Sally" wrote in message
ups.com...
Hi,

I have a spreadsheet that is formatted the following way:

Jan Feb Mar Apr May
Jun
Actual Actual Actual Actual Forecast Forecast
6000 8000 6500 7500 8000
6000

I need to run a macro each month that loops thru the columes and
highlights the last Actual column and pastes formulas and formatting
into the first Forecast column. The number of columns change each month
as we forecast out further.

Any help would be greatly appreciated.

Thanks

Sally

Loop through Columns
 

Thanks Nigel,

The cell actually has text in it though. I need to loop by column until
"Actual" changes to "Forecast"

Any ideas?

Thanks

Sally

Nigel wrote:
You can use the following VBA code to identify the next empty column, set
the value of xr to be the row where the data resides, you can loop this
value if there are many rows to test.

xr = 1
xNextEmptyCol = Cells(xr, Columns.Count).End(xlToLeft).Column + 1

then use the value to set the fomula into the column eg...

Cells(xr,xNextEmptyCol).Formula = "your formula"



--
Cheers
Nigel



"Sally" wrote in message
ups.com...
Hi,

I have a spreadsheet that is formatted the following way:

Jan Feb Mar Apr May
Jun
Actual Actual Actual Actual Forecast Forecast
6000 8000 6500 7500 8000
6000

I need to run a macro each month that loops thru the columes and
highlights the last Actual column and pastes formulas and formatting
into the first Forecast column. The number of columns change each month
as we forecast out further.

Any help would be greatly appreciated.

Thanks

Sally

Loop through Columns
 

Hi Sally
Try this.....

Sub NextForecast()
Dim xc As Integer, xr As Long

xr = 1
For xc = 1 To 255
If Trim(UCase(Cells(xr, xc))) = "FORECAST" Then Exit For
Next xc

MsgBox "First Column with Forecast: " & xc

End Sub

xr refers to the row in which the headings appear. I show the result in a
message box but you can use this value in your formula


--
Cheers
Nigel



"Sally" wrote in message
oups.com...
Thanks Nigel,

The cell actually has text in it though. I need to loop by column until
"Actual" changes to "Forecast"

Any ideas?

Thanks

Sally

Nigel wrote:
You can use the following VBA code to identify the next empty column, set
the value of xr to be the row where the data resides, you can loop this
value if there are many rows to test.

xr = 1
xNextEmptyCol = Cells(xr, Columns.Count).End(xlToLeft).Column + 1

then use the value to set the fomula into the column eg...

Cells(xr,xNextEmptyCol).Formula = "your formula"



--
Cheers
Nigel



"Sally" wrote in message
ups.com...
Hi,

I have a spreadsheet that is formatted the following way:

Jan Feb Mar Apr May
Jun
Actual Actual Actual Actual Forecast Forecast
6000 8000 6500 7500 8000
6000

I need to run a macro each month that loops thru the columes and
highlights the last Actual column and pastes formulas and formatting
into the first Forecast column. The number of columns change each month
as we forecast out further.

Any help would be greatly appreciated.

Thanks

Sally