1

I want to implement the following formula in to Excel cells

IF(OR(D12>0,C13=""),"",MAX(SUM($C$12:C13)-$D$9,0))

(once formula is applied I should get following result (formula implemented manually):

enter image description here

so i wrote simple macro as below , but unable to implement formula , formula retuns value as "true"

Sub adjustoldbills()

lastRow_sht4 = Sheet4.Range("A" & Rows.Count).End(xlUp).Row
Sheet4.Cells(12, 11) = ""

For i = 1 To lastRow_sht4 - 10
    If Sheet4.Cells(11 + i, 1) <> "" Then
        '=MAX(SUM($C$12:C15)-$D$9,0)
        Sheet4.Cells(12, 4).Formula = "=MAX(SUM($C$12:C" & 12 & ")-$D$9,0)"
        Sheet4.Cells(11 + i, 4).Formula = "=(if(or(D" & 11 + i > 0 & ",C" & 12 + i & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) & "," & "MAX(SUM($C$12:C" & 12 & i & ")-$D$9,0)"
    End If
Next i

End Sub

i am getting wrong result after implemented formula via vba macro as in this image:

enter image description here

how to implement formula and get value as expected.

Improve this question
1
  • have you tested my answer below ? Commented Oct 26, 2016 at 10:47

2 Answers 2

Reset to default
0

I'd go another way, with no loops and exploiting SpecialCells() method of Range object

Option Explicit

Sub adjustoldbills()

    With Sheet4
        .Range("D12").FormulaR1C1 = "=MAX(RC[-1]-R9C4,0)" 
        With .Range("A13", .Cells(.Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants)
            With .Offset(, 2).SpecialCells(xlCellTypeConstants)
                .Offset(, 1).FormulaR1C1 = "=IF(R[-1]C>0,"""",MAX(SUM(R12C[-1]:RC[-1])-R9C4,0))"
                With .Areas(.Areas.Count)
                    .Offset(, 1).Cells(.Rows.Count).Offset(1).FormulaR1C1 = "=IF(R[-1]C>0,"""",MAX(SUM(R12C[-1]:RC[-1])-R9C4,0))"
                End With
            End With
        End With
    End With
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you verymuch , i am getting proper result now thanks once again
you are welcome. Then please mark answer as accepted by clicking on the check mark beside the answer to toggle it from greyed out to filled in. thank you
Would the brave downvoter kindly explain why he didn't like this answer and give suggestions as how to improve it? Or remove the down vote if he was wrong. Thank him
-1

When trying to convert long formulas to VBA try using a String variable to help you test it.

 Dim FormulaStr              As String

 FormulaStr = "=IF(OR(D" & 11 + i & ">0,C" & 12 + i & "=" & Chr(34) & Chr(34) & ")," & _
      Chr(34) & Chr(34) & ",MAX(SUM($C$12:C" & 12 + i & ")-$D$9,0))"
 Debug.Print FormulaStr

Then, in the immediate window you will get:

=IF(OR(D12>0,C13=""),"",MAX(SUM($C$12:C13)-$D$9,0))

which is the formula you want to convert to VBA.

Now all you need to do is to add the line below:

 Sheet4.Cells(11 + i, 4).Formula = FormulaStr

If you want to skip the String variable, you can just replace your line of

Sheet4.Cells(11 + i, 4).Formula = "=(if(or(D" & 11 + i > 0 & ",C" & 12 + i & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) & "," & "MAX(SUM($C$12:C" & 12 & i & ")-$D$9,0)"

to:

Sheet4.Cells(11 + i, 4).Formula = "=IF(OR(D" & 11 + i & ">0,C" & 12 + i & "=" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) & ",MAX(SUM($C$12:C" & 12 + i & ")-$D$9,0))"
Improve this answer

1 Comment

just like @user3598756 wrote, "Would the brave downvoter kindly explain why" ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.