Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop same formula for different columns? | Excel Programming | |||
how to loop thru cell in 2 columns | Excel Programming | |||
loop over columns | Excel Discussion (Misc queries) | |||
loop through columns | Excel Programming | |||
Loop 20 columns Help! | Excel Programming |