ssturdy
07-22-2008, 05:59 AM
Hi
I am fairly new to VBA and I am trying to automate a very repetative task by using some VBA code.
The example sheet works like this. Starting at row 3 and working down.
"B3" = 12 so I need to leave 12 blanks starting with cell F3 before populating the 13th cell with the value in cell "C3"
"D3" = 1 so I need to leave 1 blank cell before populating with the value in column "E3" (This part is then repeated until the end of the 52 week cycle).
Sub forecast()
Application.ScreenUpdating = False
Dim rng As Range, cell As Range
Set rng = Range(Range("b3:b25"), _
Cells(Rows.Count, "b").End(xlUp))
For Each cell In rng
Cells(cell.Row, "b").Offset(0, Cells(cell.Row, "b") + 4).Select
ActiveCell.Value = Cells(cell.Row, "c")
ActiveCell.Offset(0, Cells(cell.Row, "d") + 1).Select
Do While ActiveCell <> Empty
ActiveCell.Value = Cells(cell.Row, "e").Value
ActiveCell.Offset(0, Cells(cell.Row, "d") + 1).Select
Loop
Next
End Sub
Current problems I am having.
If the cell value in Columns B & D are to high, I will receive an error 400.
I understand why it is happening but due to my inexperience I am unable to fix it. As the sheet only runs for 52 weeks I could do with the code only working in that range, populating no cells beyond it.
What I would like to implement
Taking the code further, I would like it to only run the code if the cells in column "A" = Order. If "A" = anything other than Order, then I would like it skip the row and move to the next.
Any help/suggestions would be much appreciated.
Thanks in advance
Steve
I am fairly new to VBA and I am trying to automate a very repetative task by using some VBA code.
The example sheet works like this. Starting at row 3 and working down.
"B3" = 12 so I need to leave 12 blanks starting with cell F3 before populating the 13th cell with the value in cell "C3"
"D3" = 1 so I need to leave 1 blank cell before populating with the value in column "E3" (This part is then repeated until the end of the 52 week cycle).
Sub forecast()
Application.ScreenUpdating = False
Dim rng As Range, cell As Range
Set rng = Range(Range("b3:b25"), _
Cells(Rows.Count, "b").End(xlUp))
For Each cell In rng
Cells(cell.Row, "b").Offset(0, Cells(cell.Row, "b") + 4).Select
ActiveCell.Value = Cells(cell.Row, "c")
ActiveCell.Offset(0, Cells(cell.Row, "d") + 1).Select
Do While ActiveCell <> Empty
ActiveCell.Value = Cells(cell.Row, "e").Value
ActiveCell.Offset(0, Cells(cell.Row, "d") + 1).Select
Loop
Next
End Sub
Current problems I am having.
If the cell value in Columns B & D are to high, I will receive an error 400.
I understand why it is happening but due to my inexperience I am unable to fix it. As the sheet only runs for 52 weeks I could do with the code only working in that range, populating no cells beyond it.
What I would like to implement
Taking the code further, I would like it to only run the code if the cells in column "A" = Order. If "A" = anything other than Order, then I would like it skip the row and move to the next.
Any help/suggestions would be much appreciated.
Thanks in advance
Steve