Referencing and Formulas

[Melpp]

Hi,

I am trying to create a loop that will add automatic referencing. This is a sample formula that I would enter in the regular Excel "='Ant (AUC)'!B4/'Ant (peaks)'!B4" . How can I automate this for a series of rows and columns in Excel Macros?I tried the following code with no luck.

For j = 2 To 16
For i = 1 To 4
b = i + Row_No
area = Worksheets("Ant (AUC)").Range(j & b).Value
peak = Worksheets("Ant (peaks)").Range(j & b).Value
Range(j & b) = area / peak
Next i
Next j

 

[Andrew Poulsom]

Try:

Sub Test()
    Const Row_No As Long = 3
    Dim j As Long, i As Long
    Dim B As Long
    Dim area, peak
    For j = 2 To 16
        For i = 1 To 4
            B = i + Row_No
            area = Worksheets("Ant (AUC)").Cells(j, B).Value
            peak = Worksheets("Ant (peaks)").Cells(j, B).Value
            Cells(j, B).Value = area / peak
        Next i
    Next j
End Sub

You hadn't defined Row_No so I used a constant.

 

[Melpp]

I get an 'overflow' error for the following line:
Cells(j, B).Value = area / peak

 

[Andrew Poulsom]

That's probably because of divide by zero. Make sure that all the source cells contain a value other than zero. Or use an error trap.