How to create code to calculate total length installed based on number of pages that are entered
Category: [Excel] Demo Available
How to create code to calculate total length installed based on number of pages that are entered
Requirement:
The user has the equations to calculate the data is entered (daily total length installed), but needs the equation to add each daily based on the number of sheets the user requires. The equation would pull the data from the same cell reference(just different sheets). For example: if the user enters 10 pages, the user needs the code to add the data from the 10 cells and place it on my main screen. But if the user only needs 5 pages, the user needs it to do the same. It would also need to be able to recognize that the user has added a sheet after the project has begun (the user has this option setup for them using an ActiveX control).
Solution:
Code:
For Each ws In Sheets
If ws.Name <> "MAIN" Then
If isVal = "" Then
isVal = ws.Range("B15")
Else
isVal = isVal & "," & ws.Range("B15")
End If
End If
Next ws
arr = Split(isVal, ",")
n = 0
For i = LBound(arr) To UBound(arr)
n = n + arr(i)
Next i
Sheets("MAIN").Cells(1, 1) = n
Every time when you will run the code it will collect data from all the worksheets but MAIN.
Obtained from the OzGrid Help Forum.
Solution provided by adygelber.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to loop a macro with various length columns |
| How to create a pop up message if the length exceeds the limits |
| How to copy master sheet as values and automatically set new name |
| How to re-name each sheet in workbook with a pre-defined name |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.