VBA code for summiation in MS Excel

[Srikanth H N]

Hello There,

I am wiritng a macro and got struck badly.
I have a data from Cell C4 to Dxxx (column D is given as xxx because each day the number of rows get changed).
In column E, i have given the below code

Sub Test()
Dim ws As Worksheet
    Dim lRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        .Range("E4:E" & lRow).Formula = "=If(D4>=0,0,D4)"
        .Range("E4:E" & lRow).Value = .Range("E4:E" & lRow).Value
        End With
End Sub

Now I am trying to give a Formula in column F but getting struck here...
If Cell E4<>0,Sum($E$3:E4)
when it's comes to Cell E5 and if E5<>0,sum($E$3:E5)

This forumla should be there in all cells which has values in Column E

As said, the number of rows differs each time.

Any assistance is highly appreciated!!!

Thanks!

[TMS]

Please:

1) use code tags to comply with the forum rules and
2) upload a sample workbook

Regards, TMS

[Olly]

Add in the line in red:

Sub Test()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
    lRow = .Range("D" & .Rows.Count).End(xlUp).Row
    .Range("E4:E" & lRow).Formula = "=If(D4>=0,0,D4)"
    .Range("E4:E" & lRow).Value = .Range("E4:E" & lRow).Value
    .Range("F4:F" & lRow).Formula = "=If(E4<>0,sum(E$3:E4),"""")"
End With
End Sub

[Srikanth H N]

Perfect!!! TMS~ Appreciate your very quick assistance!!!
It's my bad, I did not use code tags to comply with the forum rules and upload a sample workbook.
Going forward will ensure to follow these...

But Thanks a ton for providing solution... It's working fine.

Thanks Much

[TMS]

Please note that the solution did not come from me, it was from OllyXLS

[David A Coop]

Hi Srikanth,

Your post is not very clear, but I think you are saying that the If Cell E4<>0,Sum($E$3:E4) code is going to be in another macro (or part of the macro). If it is set up the same way as the one you posted, it won't work because the macro converts it to a value BEFORE writing to the cell. (the .value = .value code).

Try removing the .value =.value code from the WITH statement. This will cause the formula to be written to the cell instead of the value. I assume you don't want the formula there, so AFTER the with statement, select the range and convert it to values. Something like this:

    Range("E3:E" & lrow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Hope this helps!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[TMS]

I would appreciate it if you would edit the original post in this thread and add code tags to comply with forum rules. Strictly speaking, we are not supposed to answer questions that do not comply with the forum rules.

Adding code tags makes it easier for contributors to copy the code, if necessary. Uploading a sample workbook, whilst not always essential, will ensure that contribitors can see what you are working with and the solution is more likely to be fit for purpose.


Regards, TMS




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Srikanth H N]

Hi OllyXLS,

Thanks for your assitance. Have 1 more question...

I am trying to give background clor for those cells that are less than 500K, but getting struck. Any assistance!!!

here is the code which i have got struck...

Sub Test()
Dim ws As Worksheet
    Dim lRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        .Range("E4:E" & lRow).Formula = "=If(D4>=0,0,D4)"
        '.Range("F4").Formula = "=SUM(" & Range(Cells(4, -1), Cells(i)).Address(True, False) & ")"
        .Range("E4:E" & lRow).Value = .Range("E4:E" & lRow).Value
        .Range("F4:F" & lRow).Formula = "=If(E4<>0,sum(E$3:E4),"""")"
        .Range("F4:E" & lRow).Value = .Range("F4:F" & lRow).Value
        If (Range("F4:F" & lRow).Value) <= 500000 Then
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99481185338908E-02
        .PatternTintAndShade = 0
    End With
    Else
    Range("A1").Select
    End If
    End With
End Sub

Thanks!