VBA If statement with ranges and copy

andrewdp3 · Jun 29, 2011

I've been trying different pieces of code to figure this one out but to no avail. I have a listbox on a form that populates when users select an option button. When they select something from the listbox and click ok the code makes a copy of a template sheet and places in workbook. It also renames the sheet with the name of the selected item in the listbox. Last it takes the same name selected in the listbox and adds it to the next empty spot on a sheet called "Main" (Column B).

All of this works fine except I need one final piece to the code for everything to work. The idea would be an "if" statement but I'm unsure exactly how?

If the sum of D4:D75 is greater than the sum of E4:E75 of the same sheet that is created and named. I would like to subtract the sum of E4:E75 from the sum of D4:D75 and place that answer on the current row of main sheet in the D column.

Else subtract the sum of E4:E75 from the sum of D4:D75 and place that answer on the current row of the main sheet in the E column? Is this possible??

Sheets("Main").Cells(curRow, 4).Formula = Sum(Sheets(Me.lbxAccount.Value).Range("E4:E75"))

Ralajer · Jun 29, 2011

If I am reading your requirements correctly you need something along lines of the following.

Code:

Sub ifPart()
    Dim MainWS As Worksheet
    Dim ufGenWS As Worksheet
    Dim sumTotal_D As Double
    Dim sumtotal_E As Double
    
    Set MainWS = Sheets("Main")
    Set ufGenWS = Sheet(Me.lbxAccount.Value)
    
    With Application.WorksheetFunction
        sumTotal_D = .Sum(ufGenWS.Range("D4:D75"))
        sumtotal_E = .Sum(ufGenWS.Range("E4:E75"))
    End With

    If sumTotal_D > sumtotal_E Then
        MainWS.Cells(curRow, 4).Value = sumTotal_D - sumtotal_E
    Else
        MainWS.Cells(curRow, 4).Value = sumtotal_E - sumTotal_D
    End If
End Sub

To be clear you want to place a number in Column D/E at curRow of the main sheet based on the comparison of the sums on the generated sheet. That is what the above should do.

The code at the end of the post suggests you may want a formula which seem different then what you wrote text.

andrewdp3 · Jun 29, 2011

I apologize for any confusion. I will be entering numbers down various columns in the new sheets that are created from the template. So basically I need the formula to be written into the cells so that when I change numbers on the one sheet it calculates and keeps up to date numbers. On the generated sheet I will have values debits and credits and a debits/credits column on the main sheet. So if the actual overall value of the new sheet is a debit I need it to be in the debit side of the main sheet and the same if credit.

Ralajer · Jun 29, 2011

Sorry, I am still not sure I get what you want. Could you lay it out with explicitly and with example of the expected result.

Code:

If sumTotal_D > sumTotal_E Then
    Do This 
    Sheet Main cell D curRow will equal ???
    Sheet Generated cell D XX will equal ???
Else
    Do This
    Sheet Main cell E curRow will equal ???
    Sheet Generated cell E XX will equal ???
End If

Or whatever the case may be.

VBA If statement with ranges and copy

andrewdp3

New Member

Excel Facts

Ralajer

Active Member

andrewdp3

New Member

Ralajer

Active Member

Similar threads

Forum statistics

Share this page

VBA If statement with ranges and copy

andrewdp3

New Member

Excel Facts

Ralajer

Active Member

andrewdp3

New Member

Ralajer

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock