I have a data set of three columns and 6000 rows, these columns represent the
last three months of sales actuals. In each column if a sale occurred then a
number is in the cell, if not then the cell is blank. In another column I
would like to perform a calculation using the most recent data. So if a sale
occurred in last month then a number will be in the appropriate cell and I
will use it, if the cell for last month is blank then I would like to use the
data from the previous month, and so on. I wrote some simple code, but it is
not working. The cell locations are correct as the data set is very large.

Sub Netback()
'
' Netback Macro
' Macro recorded 7/18/2008 by Sasol NA User

Range("bc81").Select
ActiveCell.Select

Do Until IsEmpty(ActiveCell([0], [-52]).Value)
ActiveCell.Offset(1).Select
If ActiveCell([0], [-13]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-13]/R[0]C[-37]"
End If
If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0],
[-14]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-14]/R[0]C[-38]"
End If
If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0],
[-14]).Value = IsBlank Then
ElseIf ActiveCell([0], [-15]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-15]/R[0]C[-39]"
Else: ActiveCell.Value = 0
End If
Loop
End Sub

Help me find my obvious error.

Code is not the way I would write it but no doubt you are in the learning
stages so I have edited your code so that you will be able to understand it.

You left out the Offset in both the Do Until and the If tests. Also I think
that it is better to only perform the one test at each If and then send code
to a label to skip the other If tests after it finds a true test.

Also need to select the starting cell and advance to next cell just before
Loop command so that the loop exits when the test for loop is true otherwise
it will perform one last loop without data.

Without testing, I don't think that the values used in creating formulas
match the values required for the offset. I think that they are one out but
I'll stand correcting on that.

Sub Netback()

'
' Netback Macro
' Macro recorded 7/18/2008 by Sasol NA User

Range("bc82").Select 'Select the cell to start

Do Until IsEmpty(ActiveCell.Offset([0], [-52]).Value)

If ActiveCell.Offset([0], [-13]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-13]/R[0]C[-37]"
GoTo FinishedFormula
End If
If ActiveCell.Offset([0], [-14]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-14]/R[0]C[-38]"
GoTo FinishedFormula
End If
If ActiveCell.Offset([0], [-15]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-15]/R[0]C[-39]"
Else: ActiveCell.Value = 0
End If

FinishedFormula:
ActiveCell.Offset([1], [0]).Select
Loop
End Sub

--
Regards,

OssieMac


"kmcwi" wrote:

I have a data set of three columns and 6000 rows, these columns represent the
last three months of sales actuals. In each column if a sale occurred then a
number is in the cell, if not then the cell is blank. In another column I
would like to perform a calculation using the most recent data. So if a sale
occurred in last month then a number will be in the appropriate cell and I
will use it, if the cell for last month is blank then I would like to use the
data from the previous month, and so on. I wrote some simple code, but it is
not working. The cell locations are correct as the data set is very large.

Sub Netback()
'
' Netback Macro
' Macro recorded 7/18/2008 by Sasol NA User

Range("bc81").Select
ActiveCell.Select

Do Until IsEmpty(ActiveCell([0], [-52]).Value)
ActiveCell.Offset(1).Select
If ActiveCell([0], [-13]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-13]/R[0]C[-37]"
End If
If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0],
[-14]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-14]/R[0]C[-38]"
End If
If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0],
[-14]).Value = IsBlank Then
ElseIf ActiveCell([0], [-15]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-15]/R[0]C[-39]"
Else: ActiveCell.Value = 0
End If
Loop
End Sub

Help me find my obvious error.

Thanks for your help, it works great.

"OssieMac" wrote:

Code is not the way I would write it but no doubt you are in the learning
stages so I have edited your code so that you will be able to understand it.

You left out the Offset in both the Do Until and the If tests. Also I think
that it is better to only perform the one test at each If and then send code
to a label to skip the other If tests after it finds a true test.

Also need to select the starting cell and advance to next cell just before
Loop command so that the loop exits when the test for loop is true otherwise
it will perform one last loop without data.

Without testing, I don't think that the values used in creating formulas
match the values required for the offset. I think that they are one out but
I'll stand correcting on that.

Sub Netback()

'
' Netback Macro
' Macro recorded 7/18/2008 by Sasol NA User

Range("bc82").Select 'Select the cell to start

Do Until IsEmpty(ActiveCell.Offset([0], [-52]).Value)

If ActiveCell.Offset([0], [-13]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-13]/R[0]C[-37]"
GoTo FinishedFormula
End If
If ActiveCell.Offset([0], [-14]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-14]/R[0]C[-38]"
GoTo FinishedFormula
End If
If ActiveCell.Offset([0], [-15]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-15]/R[0]C[-39]"
Else: ActiveCell.Value = 0
End If

FinishedFormula:
ActiveCell.Offset([1], [0]).Select
Loop
End Sub

--
Regards,

OssieMac


"kmcwi" wrote:

I have a data set of three columns and 6000 rows, these columns represent the
last three months of sales actuals. In each column if a sale occurred then a
number is in the cell, if not then the cell is blank. In another column I
would like to perform a calculation using the most recent data. So if a sale
occurred in last month then a number will be in the appropriate cell and I
will use it, if the cell for last month is blank then I would like to use the
data from the previous month, and so on. I wrote some simple code, but it is
not working. The cell locations are correct as the data set is very large.

Sub Netback()
'
' Netback Macro
' Macro recorded 7/18/2008 by Sasol NA User

Range("bc81").Select
ActiveCell.Select

Do Until IsEmpty(ActiveCell([0], [-52]).Value)
ActiveCell.Offset(1).Select
If ActiveCell([0], [-13]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-13]/R[0]C[-37]"
End If
If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0],
[-14]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-14]/R[0]C[-38]"
End If
If ActiveCell([0], [-13]).Value = IsBlank And ActiveCell([0],
[-14]).Value = IsBlank Then
ElseIf ActiveCell([0], [-15]).Value 0 Then
ActiveCell.FormulaR1C1 = "=R[0]C[-15]/R[0]C[-39]"
Else: ActiveCell.Value = 0
End If
Loop
End Sub

Help me find my obvious error.