add formula to complicated code



I  hope  find  the assistance from  the  experts   to  add  the  specific formula  every time  I run  the  code  it  inserts  three columns  after  match with two  files  then  bring  and  sum  the  values  and  calculate  in  the  last  column  what  i want  when  run  the  macro  and  insert  the three columns in  the  last  column QTY= precede column QTY+ PURCHASE- SALES  I  put  the  formula  in COL J to  understand  what  I want  

the  formula  should  be  in every time  insert the three columns 

actually  I  can't  deal  with like  this  complicated the  code  to  mod it  

so  any  help will be truly  appreciate 



Hi Halk and welcome to the Forum

I don't see a formula in column G (apart from thr SUM cells)
John_Ru (rep: 1072) Apr 27, '21 at 5:52 am
my apology   in COL J in file  output mod
Halk (rep: 2) Apr 27, '21 at 6:02 am
absolutey   it's  not  mine  ,  if  this  is  mine  I  don't  ask  assistance  to  mod  what  I  need   anyway  thanks  for your  feedback
Halk (rep: 2) Apr 27, '21 at 11:13 am
Add to Discussion


Selected Answer


In the attached file I've given a solution based on running another macro once your existing macro has completed. This new macro (in Module 2) is called by a new line I've added into Sub test(), shown in bold in the extract below:

Set dic = Nothing
    OverwriteQty 'call sub to change the last Qty column
End Sub
This new line calls the following sub (with comments to explain what's happening):
Sub OverwriteQty()

Dim LastCol As Integer, LastRow As Integer, n As Integer

With Sheets(1)
    LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column 'find last column for row 2
    LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row 'find last row for column B (2)

    For n = 3 To LastRow 'go down last row
        With .Cells(n, LastCol)
            If Not .HasFormula Then 'if it's not a Total...
                .Value = .Offset(0, -3).Value + .Offset(0, -2).Value - .Offset(0, -1).Value '...make value= prev Qty + Purchase -Sales
            End If
        End With
    Next n
End With

End Sub
Note that I've also corrected the spellings to CATEGORY and MANUFACTURE in the code by changing this line in Private Sub GetData:
            rs.Open "Select `CATEGORY`, `BRAND`, `TYPE`, `MANUFACTURE`, Sum(`Purchase`), Sum(`Sales`) From `PR$` " & _
            "In '" & ThisWorkbook.Path & "\" & fn & "' 'Excel 12.0;''HDR:=Yes;''' Group By `CATEGORY`, `BRAND`, `TYPE`, `MANUFACTURE`;", cn, 3
            If Err.Number <> 0 Then msg = fn & " has problem": Exit Do
That means your source files MUST have those correct spellings in the headings (and I've attached the two revised files, which must be in the same directory as the main file).

Finally, I've moved the button to run the macro- it's now top left and states "Update from files" rather than OK.

Hope this works for you.



many  thanks  for  a great  work  you  give  me  a big  favor  
just  I  would  understand   if I  want  the  formula shows  in  last  column  actaully  i try  mod  this  line  but  it  doesn't success 
               .Formula = .Offset(0, -3).Value + .Offset(0, -2).Value - .Offset(0, -1).Value '...make value= prev Qty + Purchase -Sales
Halk (rep: 2) Apr 28, '21 at 5:17 am

Thanks for selecting my Answer.

If you really want the formula in the Qty column, change that line for this:
.Formula = "= " & .Offset(0, -3).Address & " + " & .Offset(0, -2).Address & " - " & .Offset(0, -1).Address '...make value= prev Qty + Purchase -Sales
Run the macro (with columns H:J deleted) and you'll see J3 has = $G$3 + $H$3 - $I$3 etc.
John_Ru (rep: 1072) Apr 28, '21 at 5:28 am
every  thing  works  excellantly  
many  thank  for  provide  me  the    great solution 
Halk (rep: 2) Apr 28, '21 at 5:37 am
Add to Discussion

The code below will not enter the formulas you ask for but it will draw the totals. The reason is that your entire worksheet has no formulas. Therefore I think you need the numbers. Should I be mistaken the change would be almost negligible but the result would take a little longer to appear because formulas must be written to the sheet one by one whereas numbers are written wholesale in the code below.

Private Sub AddTotals(Ws As Worksheet)
    ' 227

    Dim Data        As Variant                  ' all of CurrentRegion
    Dim Totals      As Variant
    Dim TotalClm    As Long                     ' last column on the right
    Dim R           As Long                     ' loop counter: Rows
    Dim Rstart      As Long                     ' first row of subtotal calculation
    Dim Rend        As Long                     ' last row of subtotal calculation
    Dim i           As Long                     ' loop counter: index of Data

    With Ws
        ' last used column in row 2
        TotalClm = .Cells(2, .Columns.Count).End(xlToLeft).Column
        Data = .Range(.Cells(1, 1), .Cells(.Rows.Count, TotalClm).End(xlUp)).Value
        ReDim Totals(1 To UBound(Data))
        Totals(2) = "QTY"
        Rstart = 3                              ' start in row 3
        Rend = Rstart
        For R = Rstart To UBound(Data)
            If InStr(1, Data(R, 2), "total", vbTextCompare) Then
                For i = Rstart To (Rend - 1)
                    Totals(R) = Totals(R) + Totals(i)
                Next i
                Rstart = R + 1
                Rend = R
                Totals(R) = Data(R, TotalClm - 3) + Data(R, TotalClm - 2) - Data(R, TotalClm - 1)
            End If
            Rend = Rend + 1
        Next R
        .Cells(1, TotalClm).Resize(UBound(Totals)).Value = Application.Transpose(Totals)
    End With
End Sub

The code loops through all rows of the first sheet in your Output Mod wortkbook and calculates the numbers in column J, differently depending upon whether or not the word "TOTAL" appears in column B. It then writes the calculated totals to column J, over-writing whatever was there before.

"Column J" is always the last used column on the right (as measured in row 2). So, as you add more columns, previous ones aren't touched.

My code is in a separate procedure which you can paste at the bottom of your Module1 already existing in the Output Mod workbook. You can use this sub to call the procedure by itself for testing or even use. Remove the word "Private" from the declaration line if you want to cal the sub from the workbook UI.

Private Sub Test_AddTotals()
    AddTotals Sheets(1)
End Sub

Of course, your objective is to have the totals added when the 3 columns are. That's a little bit more tricky. I didn't want to run your entire code. So I make a guess. Add the one line needed at the location in your existing procedure indicated by bold type below.

Private Sub OutPut(wsName As String, x, dic As Object)
    Dim i As Long, ii As Long, iii As Long, n As Long
    Dim a, b, e, s, txt As String, temp, R As Range
    Application.ScreenUpdating = False
    With Sheets(1).Cells(1).CurrentRegion
        .Columns(.Columns.Count - 2).Resize(, 3).AutoFill _
        Destination:=.Columns(.Columns.Count - 2).Resize(, 6)
        With .Cells(1).CurrentRegion.Offset(2).Resize(.Rows.Count - 2)
            .Columns(.Columns.Count - 2).Resize(, 3).ClearContents
            a = .Value: .ClearContents: .Borders.LineStyle = xlNone
            .Font.Bold = False
            ReDim b(1 To Rows.Count, 1 To UBound(a, 2))
            .Interior.ColorIndex = xlNone
            AddTotals Sheets(1)

            For i = 1 To UBound(a, 1)

The point is that "Calumn J" must already exist when the procedure is called, and the data for it, too. And it must not be called in a loop. If the location I suggest is too early, place a break point in the code, run your procedure and insert my procedure call at the correct location.

By the way, the spelling errors in your worksheet headers are hard programmed in your procedure GetData. If you change the spelling in the code you can also change it in the worksheets.

            rs.Open "Select `CATOGERY`, `BRAND`, `TYPE`, `MONAFACTURE`, Sum(`Purchase`), Sum(`Sales`) From `PR$` " & _
            "In '" & ThisWorkbook.Path & "\" & fn & "' 'Excel 12.0;''HDR:=Yes;''' Group By `CATOGERY`, `BRAND`, `TYPE`, `MONAFACTURE`;", cn, 3

Note that you can use Edit > Find or Edit > Replace in the VB Editor to find all occurrencues of, for instance, "MONAFACTURE" and substitute the word for something more English.



thanks  for  a  great  assistance   but  Private Sub Test_AddTotals()
it  doesn't  work  and  no  error  so  can  you  see  the  file  i  edited  and  add  modul2  and  put  your  suggestion   ,  where    I made  mistake?
Halk (rep: 2) Apr 27, '21 at 11:10 pm
No mistake, Halk. In your file all pre-existing formulas are replaced with totals. They do look the same but they were put there by my code. Remove some or all of the totals - but be sure to leave row 2 untouiched - then run Test_AddTotals again to see its handiwork.
Variatus (rep: 4549) Apr 28, '21 at 3:32 am
actually  I  did  it   but  the  last  row  it  doesn't  show  all  the  the  values in total  rows  it only  shows  in  the  last  column in J  except   total  row for catogery OZ-L  and  it  supposes  when  i run macro  repeatedly insert  a new  three last column  and  bring  the  values  from files  report1,2  but  it  doesn't  happen anything 
Halk (rep: 2) Apr 28, '21 at 4:56 am
Obviously, you didn't read my answer. It contains all the information you need. It's not possible to run, let alone modify, complicated code without paying attention.
Variatus (rep: 4549) Apr 28, '21 at 6:48 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login