# add formula to complicated code

0

hello

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

### Discussion

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
John
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

0

Halk

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.

### Discussion

John
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
Halk

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
0

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
Else
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()
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

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.

### Discussion

variatus
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