0

Could you help me with the solution to this issue, I'm a beginner?:

2 dynamic columns "F:G", I need to sum each column and then subtract the result without using Macro recording- (I do not wish the user to view the formula)

I've tried the below code, but it doesn't return the result of subtracting column F-G, it returns the subtraction of the last cell on the last row. 

Sub testsum()
    Dim B As Integer
    Dim W As Integer
    Dim LastCell As String

    ActiveSheet.Select
    Range("F3").Select
         Selection.End(xlDown).Select
         LastCell = ActiveCell.Address(False, False)
         ActiveCell.Offset(1, 0).Select
         ActiveCell.Value = "=sum(f3:" & LastCell & ")"

    Range("G3").Select
         Selection.End(xlDown).Select
         LastCell = ActiveCell.Address(False, False)
         ActiveCell.Offset(1, 0).Select
         ActiveCell.Value = "=sum(G3:" & LastCell & ")"

    Worksheets(1).Range("G1").Value = B - W

End Sub

I wish to recreate the result of =Sum(F:F)-Sum(G:G) on a different Worksheet.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
Paula Yankova
  • 1
  • 2
  • 1
    You ___really___ need to [avoid using `.Select`](https://stackoverflow.com/q/10714251/5781745) in your code. And using `ActiveCell` is _okay_ if your actually have a legitimate purpose for using the ActiveCell, but using because you had VBA select it is just... not good. – K.Dᴀᴠɪs Jul 17 '19 at 17:39
  • So let me see if I am understanding exactly what you are wanting to do. You would like to first sum up the entire column F (excluding rows 1-2), sum up G, and then subtract them? Also, what's the purpose of adding the formula `=SUM(...)` to the worksheet - do you require this or was this just to help you store your variable? – K.Dᴀᴠɪs Jul 17 '19 at 17:45
  • Hello K. Davis, – Paula Yankova Jul 17 '19 at 19:20
  • Hello, if you can answer my questions in my 2nd comment I would be happy to assist, and also fix the issues in the first comment in the process. Do you require the formula to be present on the sheet or were you just using it to store a value for your code? – K.Dᴀᴠɪs Jul 17 '19 at 19:29
  • Hello K. Davis, Thank you for your reply. What I have is these 2columns, Since the data is imported the columns are dynamic, but I always need this result. col F G 50 62.5 50 100 50 100 50 0 250 0 500 1000 50 100 =SUM(F89:F95) =SUM(G89:G95) =F96-G96 I"m trying to recreate the same process with VBA. since I don't want the formulas to be visible for the user, therefore recording a macro doesn't really work for me. These are my first steps in independent VBA coding, and i would really appreciate the advice even for future reference :). Thanks again – Paula Yankova Jul 17 '19 at 19:39
  • not sure if the reply is clear: column F & G have 7 values each – Paula Yankova Jul 17 '19 at 19:44
  • Is all of this contained on the same worksheet? So the two columns you are adding and the end result? Or are the columns on a different sheet than the result? I see your result is supposed to go to `Worksheets(1)` – K.Dᴀᴠɪs Jul 17 '19 at 19:49

1 Answers1

0

It is recommended that you avoid using .Select in your code. There may be a few edge cases where you may need to use it, but the average VBA user will likely never have to encounter a single instance where they'd have to use .Select in their lifetime.

First, we want to set the worksheet to a variable that contains the columns we are going to be looking at. Then we want to assign two separate ranges - one for the F column and one for G. It's easier to manage your ranges this way, and generally makes the code cleaner and easier to read.

Then sum the ranges to their own variables, and the end result would be subtracting them.

Sub TestSum()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    Dim RngF As Range, RngG As Range
    Set RngF = ws.Range(ws.Range("F3"), ws.Cells(lastRow(ws, "F"), "F"))
    Set RngG = ws.Range(ws.Range("G3"), ws.Cells(lastRow(ws, "G"), "G"))

    Dim SumF As Long, SumG As Long
    SumF = Application.WorksheetFunction.Sum(RngF)
    SumG = Application.WorksheetFunction.Sum(RngG)

    ws.Range("G1").Value = SumF - SumG

End Sub

Function lastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
    lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

However, you can create your own custom formula - better known as a UDF (User Defined Function). I understand you do not want the users to see the original formula, but I am not sure how you feel about one that hides everything and you simply only see the name.

Everything will be the same as above - except instead of having the Sub apply the value to the worksheet, you will assign the value to the function itself.

Function getSum() As Long

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    Dim RngF As Range, RngG As Range
    Set RngF = ws.Range(ws.Range("F3"), ws.Cells(lastRow(ws, "F"), "F"))
    Set RngG = ws.Range(ws.Range("G3"), ws.Cells(lastRow(ws, "G"), "G"))

    Dim SumF As Long, SumG As Long
    SumF = Application.WorksheetFunction.Sum(RngF)
    SumG = Application.WorksheetFunction.Sum(RngG)

    getSum = SumF - SumG

End Function

Function lastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
    lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Then on your worksheet in G1, you would type the following formula:

=getSum()

The benefit of this method is that if a value changes in either column, this would automatically update without any additional intervention.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
  • To answer your question - no, the result will be directed to another worksheet. Thanks a lot for your solution, i will try the first thing tomorrow – Paula Yankova Jul 17 '19 at 21:05