CODEWITHSUNDEEP
vbaloopsexcel
James
James

Reputation: 509

Mathematical equation in vba

I have mathematical equation which I am performing in sheet "Accrued Expenses" on Range("E7"). The formula is intend to loop till the lastrow in column C The two key sheets are "Start page" and "Accrued Expenses" .

The problem is that I am not able to get into VBA code. It works using the Excel macro recorder, but it wont be efficient for maintenance. My equation and code below. 

=('Accrued Expenses'!C7*'Start page'!$F$5)/'Start page'!$F$13*'Accrued Expenses'!D7

In Excel recorder code and with a loop:

Option Explicit
Sub Calculating_Accruedexpense()
    Sheets("Accrued Expenses").Select
    Dim LastRow As Long
    LastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).row
    Range("E7").Select
    Do Until ActiveCell.row = LastRow + 1
        If IsEmpty(ActiveCell) Then
            ActiveCell.FormulaR1C1 = _
                "=('Accrued Expenses'!RC[-2]*'Start page'!R5C6)/'Start page'!R13C6*'Accrued Expenses'!RC[-1]"
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Excel Recorder line:

ActiveCell.FormulaR1C1 = _
            "=('Accrued Expenses'!RC[-2]*'Start page'!R5C6)/'Start page'!R13C6*'Accrued Expenses'!RC[-1]"

Upvotes: 0

Views: 1730

Answers (1)

Alex P
Alex P

Reputation: 12497

Try this:

Sub Calculating_AccruedExpense()
    Dim lastRow As Long, cl As Range

    lastRow = Worksheets("Accrued Expenses").Range("C" & Rows.Count).End(xlUp).Row

    For Each cl In Range("E7:E" & lastRow)
        If IsEmpty(cl) Then
            cl = (cl.Offset(0, -1) * Worksheets("Start page").Range("F5")) / Worksheets("Start page").Range("F13") * cl.Offset(0, -2)
        End If
    Next cl
End Sub

Upvotes: 1

Related Questions