PDA

View Full Version : Help With - For Next Loop & Possible Inclusion of If Statement



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

Bob Phillips
07-22-2008, 06:52 AM
As best I understand



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

If cell.Value = 12 Then

cell.Offset(0, cell.Value + 4).Resize(, 1).Value = ""
cell.Offset(0, cell.Value + 15).Value = cell.Offset(0, 1).Value

ElseIf cell.Offset(0, 2).Value = 1 Then

cell.Offset(0, 4).Value = cell.offest(0, 3).Value
End If
Next
End Sub

ssturdy
07-22-2008, 08:05 AM
Hi

Thanks for your reply

Unfortunately, the code does not work and that's probably because
Im missing something or I haven't explained myself to well.

I have attached the example sheet again with my existing VBA code executed so you can see how the values in columns B - E drive the data.

Column B = How many initial blank weeks (cells) there are.
Column C = First value after the blank cells.

Column D = Additional blank cells.
Column E = 2nd value after additional blank cells.
(This is repeated until the end).

The values in columns B, C, D & E change as you work down the rows.

My code works to a degree but does not handle situations when the
offset value found in column D is greater 256 ( which is the amount of columns in excel 2000). This is when I get the error 400 message.

My other problem was making the script skip a row when the cell
in column A = No Order.

Hoping this is more clearer and your help so far is very much appreciated.


Thanks again


Steve