Reserve the Horizontal line numbers and information , but the calculation result is change

Printable View

08-27-2020, 08:33 AM
wk9128

1 Attachment(s)

Reserve the Horizontal line numbers and information , but the calculation result is change

Good morning, can anyone help, thanks in advance

How does VBA remove the Horizontal line numbers, but reserve the Horizontal line numbers and information without deleting

Attachment file 3 places is filled with color areas, and VBA code execution is required

ROW(16:34) can be inserted or deleted without affecting the calculation Total result
ROW(1:16) is fixed, From Row(16:16) to ROW(34:34) is not fixed, delete or add insert at any time

ROW(A16:A34) ITEM serial number will automatically change according to QTY column when user key in
Only rows G and I are input by the user , No information or only one J row will be blank
Column J is based on column G and column I = G * J ,
G35 Cell SUM(G16:G34) Horizontal line numbers must be automatic deducted
J35 cell SUM(J16:J34) Horizontal line numbers must be automatic deducted

Example :
Suppose the user fills in 3 in Cell G20, user fills in 15.25 in Cell I20 , the serial number of Cell A20 serial number will automatic become 4, and the original 4 of cell A21 will automatically become 5
At this time, G35 original is 7 , will automatically calculates 10,J20 automatically calculates 45.75
At this time, J35 original is 310 , will automatically calculates 355.75

Cross post
https://www.excelforum.com/excel-pro...is-change.html
08-27-2020, 03:51 PM
DocAElstein

1 Attachment(s)

Hi wk9128
Welcome to ExcelFox

I am not sure if I fully understand all that you want. For example, I don't 100% understand what "….horizontal line numbers must be automatic deducted…" means. I will take a guess that it means that numbers that are striked through should be ignored as far as the sum calculations are concerned.

I will try to do something to help get you started.

_ 1)
'Column J is based on column G and column I = G * J , ( I expect you mean I * G )
J= I * G can be satisfied, I think, with simple formulas. So we don't need VBA, ( but we could "write" the formulas in with VBA if you like. ).
This macro can go in any module. It only needs to be run once, possibly.

Code:

Sub wk9128WriteInAFormula() ' https://excelfox.com/forum/showthread.php/2622-Reserve-the-Horizontal-line-numbers-and-information-but-the-calculation-result-is-change Rem 1 worksheets data info Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets.Item(1) Rem 2 "write" things into cells Let Ws.Range("J16:J34").Value = "=If(I16="""","""",G16*I16)" End Sub

_2) The rest I think will need to be done with an Event type coding that kicks in every time anything is put in column G or I
I don't think there is anything difficult in this, it is just a bit of tedious looping down rows and adding or not if a value is there depending on if the cell value is striked through. It is less of an exercise in how to do it, rather its just a lot of tedious simple work to write the coding…

One problem I have is that I don't know how you did your strike through, and I don't know how to determine if a row is or is not striked through via VBA, at least not if you strike it through as you have done.
So I hit a brick wall at this point.

What I have done instead, is to put normal strike throughs in your table ( https://support.microsoft.com/en-us/...1-5a5a52d66738 )

The main macro must go in the worksheets object code module of the worksheet.
I have done an initial macro for you. Here it is: https://excelfox.com/forum/showthrea...ll=1#post14837 ( It is also in the uploaded file )

I did one quick test using your example, and the results look OK: https://excelfox.com/forum/showthrea...ll=1#post14836

I have not thoroughly tested it. I leave that to you
It is intended to get you / us going…
I expect that it may not be exactly what you want.
For example, the ranges used in the macro are fixed. They can be made dynamic, but I am keeping things simple initially.
I can look in again in a day or two if you want me to take it further. ( If you ask the question anywhere else in the meantime, please tell everyone where else you are asking, so we don't waste our time )
I am happy to "help" , but this looks more like an exercise in doing a lot of simple tedious coding for you, which I am less keen on doing, at least not very quickly… that is not really what a this free help forum is about..
But as long as you are not in a rush, I will help further

Alan
08-27-2020, 05:08 PM
wk9128

1 Attachment(s)

DocAElstein Thank you for your kind help

It looks like animation files, 4 and 5 are manually modified to automatic modification

https://imgur.com/q6Wc612
https://i.imgur.com/q6Wc612.gif
08-27-2020, 10:53 PM
DocAElstein

Quote:

Originally Posted by wk9128

It looks like animation files, 4 and 5 are manually modified to automatic modification

:confused:
What are you saying? Why are you showing this?

I gave you a solution!!
Have you tried it?
Did you read this: https://excelfox.com/forum/showthrea...ll=1#post14838 ?
Have you tried the file?
08-28-2020, 08:17 AM
wk9128

Moderator, Thank you for your kind help

the problem has not been completely resolved. The serial number in column A should be adjusted automatically. Don't modify it manually. Ask what to do? How to write the code?
08-28-2020, 10:47 AM
DocAElstein

1 Attachment(s)

Quote:

Originally Posted by wk9128

. Don't modify it manually. Ask what to do? How to write the code?

I have written the code for you. I have given it to you.

I think the problem is that you do not understand English language.
You have not been able to read or understand anything that I write
My macro does do it automatically!!
My macro does adjusted automatically

You have not tried yet my macro
You have not downloaded my file in post #2 https://excelfox.com/forum/showthrea...ll=1#post14838 -- https://imgur.com/FattoI3
I do not modify manually. My macro modifies automatically. I have written the macro.

All is done . All is answered. All is written and explained in post #2 https://excelfox.com/forum/showthrea...ll=1#post14838 Attachment 3380
But it seems that you are incapable of understanding any English.

Quote:

Originally Posted by DocAElstein

The main macro must go in the worksheets object code module of the worksheet.
I have done an initial macro for you. Here it is: https://excelfox.com/forum/showthrea...ll=1#post14837 ( It is also in the uploaded file )

Maybe you should not ask for help in an English speaking help forum if you understand no English language...
I have solved the problem. I have given you the code. It does all automatically.
The problem may be completely solved. But it is impossible to communicate it to you.
08-28-2020, 02:24 PM
DocAElstein

Quote:

Originally Posted by wk9128

Moderator, Thank you for your kind help.

I used https://imgur.com/upload
Sometimes it is better for images at excelfox
https://imgur.com/klEleuC
https://imgur.com/T0Z7Ic0
https://imgur.com/MYfUyRZ

[img]https://i.imgur.com/cKDISqA.gif[/img]

Quote:

Originally Posted by wk9128

the problem has not been completely resolved.

You have already 2 complete solutions, but you appear unable to understand any English. So we are wasting time trying to help you
( https://tinyurl.com/y32n7247 https://tinyurl.com/y49dxtyo https://tinyurl.com/y2j3q47j )
08-28-2020, 02:25 PM
DocAElstein

I have done a second solution for you. I used information from your answer from Jindon at excelforum
So I have now given you two complete solutions
Here https://excelfox.com/forum/showthrea...ll=1#post14844 https://tinyurl.com/y49dxtyo
https://excelfox.com/forum/showthrea...ll=1#post14845 , https://tinyurl.com/y2j3q47j is the details of the second solution
08-28-2020, 02:41 PM
wk9128

Many Thank you for your kind help (Try first) tqvm

Jindon answer very good and works perfectly, solve this problem "Reserve the Horizontal line numbers and information , but the calculation result is change"

There is a small problem that has not been resolved

https://excelfox.com/forum/showthrea...-More-Formula)
ITEM serial number is established according to QTY, if QTY is empty, ITEM serial number is also empty
The sequence number is from small to large, from top to bottom, as long as you delete or add row, it will automatically adjust
08-29-2020, 03:46 AM
DocAElstein

1 Attachment(s)

Hi
Please try better to read and understand the answers you are given.
We are all starting to waste time.

Quote:

Originally Posted by wk9128

...There is a small problem that has not been resolved.....

They are not small problems. You are asking for lots of work to be done for you!

Issue 1
…ITEM serial number is established according to QTY, if QTY is empty, ITEM serial number is also empty
The sequence number is from small to large, from top to bottom, as long as you delete or add row, it will automatically adjust….

This section must be replaced

Code:

If Range("A" & Target.Row & "").Value2 = "" Then ' We are only intertsted in putting a value in column A to update an ittem number if there is not already one in there ' get current maximum item number info: what is it, and where is it Dim Cnt As Long, Mx As Long, MxInd As Long ' MxInd is for the Item number at which we got the maximum , - I need this to know where to put the new ITEM Dim RngA As Range: Set RngA = Range("A16:A34") For Cnt = 1 To RngA.Rows.Count Step 1 If Mx < RngA.Item(Cnt).Value Then ' In Excel Ranges cell item numbers are counted along columns then next rows etc. So for a single column, each next item number is the next row Let Mx = RngA.Item(Cnt).Value Let MxInd = Cnt Else End If Next Cnt ' update current row item number to be the current highest, and make previous highest one more Let Application.EnableEvents = False ' I have to temporarily turn this thing off, or else the next line makes this macro start again Let Range("A" & Target.Row & "").Value2 = Mx: Let RngA.Item(MxInd).Value2 = Mx + 1 Let Application.EnableEvents = True Else ' Column A already has a number in so no item number update End If

Instead we just order… The sequence number is from small to large, from top to bottom….

Code:

' Dynamic Lr Dim Lr As Long: Let Lr = Range("J" & Rows.Count & "").End(xlUp).Row - 1 If Range("A" & Target.Row & "").Value2 = "" Then ' We are only intertsted in putting a value in column A to update an ittem number if there is not already one in there Let Application.EnableEvents = False Let Range("A" & Target.Row & "").Value2 = "anything" ' Put anything in for now Let Application.EnableEvents = True Dim RngA As Range: Set RngA = Range("A16:A" & Lr & "") Dim Cnt As Long, ACel As Range For Each ACel In RngA.SpecialCells(xlCellTypeConstants) ' Each cell with something in it in column A Let Cnt = Cnt + 1 Let Application.EnableEvents = False Let ACel.Value = Cnt ' The next cell down is given the next number Let Application.EnableEvents = True Next ACel Else ' Column A already has a number in so no item number update End If

Other issues ?????
I will try to guess…

For example …

Quote:

Originally Posted by DocAElstein

......
For example, the ranges used in the macro are fixed. They can be made dynamic, ....

See Lr in latest macro

Inserting rows ( to include formula also is there )
Solution is like Solution1 here https://excelfox.com/forum/showthrea...ll=1#post14856
In ThisWorkbook code module

Code:

Option Explicit Private Sub Workbook_Open() Let Sheet1.UsdRws = Worksheets.Item(3).UsedRange.Rows.Count End Sub

The main macro is modified at beginning

Code:

Public UsdRws As Long Public Sub Worksheet_Change(ByVal Target As Range) If Me.UsedRange.Rows.Count = UsdRws + 1 Then ' We added a row Let Application.EnableEvents = False Let Range("J" & Target.Row & "").Value = "=IF(OR(RC[-3]="""",RC[-1]=""""),"""",RC[-3]*RC[-1])" Let Application.EnableEvents = True Let UsdRws = UsdRws + 1 Exit Sub ' No more will be done after a row insert Else End If

Latest macro is for 3rd worksheet ( worksheet "Sheet2 excelforum jindon" ) in file:
Share 'help0824_2.xls' : https://app.box.com/s/6sln43gyk68mjv0wskedtrfit40flm6r
Macro is also here: https://excelfox.com/forum/showthrea...ll=1#post14855

If you want more help in the future from excelfox, then please try to take more time to prepare your question carefully, and please take more time to try to read and understand what is written for you

Alan