VBA Help...

[Jav_uk8]

Hi All,

VBA help

I have a total column in row 664 (this will be variant week to week)

I need the VBA to calculate L664 / K664 but like I mentioned the above, it will not be fixed to this row every week.

What I have so far is:

Range("N1").End(xlDown).End(xlDown).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "% of Collection Rate"

Range("O1").End(xlDown).End(xlDown).Offset(2, 0).Select

Hope this makes sense?

Cheers

Jav

 

[Norie]

Perhaps.

    .Value = "% of Collection Rate"
    .Offset(, 1).FormulaR1C1 = "=R[-2]C[-3]/R[-2]C[-4]"
End With

 

[Fluff]

How about something like

Sub DivTotals()
   With Range("L" & Rows.Count).End(xlUp)
      .Offset(, 3).Value = .Value / .Offset(, 1).Value
   End With
End Sub

 

[Jav_uk8]

Hi Norie,

The vba crashes on the .Value and .Offset with the 'Compile error'

 

[Norie]

Oops, kind of missed an important part - the first line of the code.

With Range("N" & Rows.Count).End(xlUp).Offset(2, 0)
    .Value = "% of Collection Rate"
    .Offset(, 1).FormulaR1C1 = "=R[-2]C[-3]/R[-2]C[-4]"
End With

 

[Jav_uk8]

Hi Fluff / Norie,

Unfortunately neither work - For this week the formula will be in cell 0666 (which will be the sum L664 / K664)

 

[Jav_uk8]

Sorry the example picture I have attached doesn't seem to be working

 

[Norie]

How did the posted code no work?

Errors?

Formulas/values not going where expected?

 

[Jav_uk8]

Hi Norie,

I have got the formula to work!!!

My problem now is how do I format the cell into a percentage cell?

My VBA is below:

With Range("M" & Rows.Count).End(xlUp).Offset(2, 0)
.Value = "% of Collection Rate"
.Offset(, 1).FormulaR1C1 = "=R[-2]C[-2]/R[-2]C[-3]"

 

[Norie]

Try this, change the formatting. e.g decimal places, as required.

With Range("M" & Rows.Count).End(xlUp).Offset(2, 0)
    .Value = "% of Collection Rate"
    With .Offset(, 1)
        .FormulaR1C1 = "=R[-2]C[-2]/R[-2]C[-3]"
        .NumberFormat = "0.00%"
    End With
End With