I’m looking for help to develop some code to add numbers in two columns. I CAN NOT use a formula because their is problems with other VBA code I run in the same cell range when I do that.
The code would search 2 ranges G4:G10, L4:L10 (I will need to be able to add additional ranges). Some cells will have no data in them and others will have data (numbers, 2 decimal places). Some ranges may or may not have numbers in them but there will always be 1 or more numbers (to be added up) in at least one of the ranges.
So in my sample data image the first range G4:G10 in cell G4 has number 1.00, cell G9 has a 2.00. The code will add up the two numbers and reference cell E3 which has a 1-1 in it. Using the 1-1 reference the code wold then search range B3:B4 for the 1-1 and then write the added numbers in the adjacent cell C3. Code would do the same procedure for each range. If a range has no numbers please have the code write a 0 in the appropriate cell in the C column.
The 2 cell range are formatted as numbers and the sets of numbers (cells E3 and J3) are formatted as text.
That would do it. If you have any questions or something is not clear please let me know so I can clarify it.
It's difficult to work with a picture. Could you attach a copy of your file? Also include more ranges with sample data.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Hi button clicker, thanks so much for your reply. I have tried the code and I’m sure if it’s me or what but I get an error message “Method Range of object Worksheet failed”. My sheet name is Sheet1 and it’s the only sheet in the workbook, would that be the problem?
Will any additional ranges also be located below in columns G and L? The ranges in column G contain 7 cells and the ranges in column L contain 8 cells. Will this always be the pattern? Will the data always start in row 3?
No there no patterns. The range and cells within the range will be different and could be located anywhere on the sheet. Also, my sheet name is Sheet1 and it’s the only sheet in the workbook.
When you say "could be located anywhere on the sheet", it is impossible to come up with a solution. VBA depends largely on patterns in data organization. In order to come up with a working solution, you need to attach a file which shows how your data can be organized with enough data to give us an idea of the bigger picture. Will the reference cell always be one row up and two columns to the left of each range?
You can continue to define as many ranges and results spots as you would like. Within VBA i've
included additional code which is commented out with an apostrophe. You can follow that methodology
for as many ranges and their results as you would like.
There is the option of doing an offset method, which sounds like Mumps is leaning toward.
It helps but it is not enough. Could you please attach a file that contains more ranges, perhaps 8 or 10 to give a clearer picture? If the ranges are of different sizes, please include a good variety.
Looked at my layout and can now confirm that I will stay within a fixed range/layout for every range. I've attached a new workbook. here is an image as well.
Usually for this kind of stuff I use easy formulas !
Anyway, according to your last attachment a demonstration as a beginner starter :
PHP Code:
Sub Demo1()
Dim R&, Rf As Range, A$
R = 2
[B3].CurrentRegion.ClearContents
With ActiveSheet.UsedRange.Columns
With .Item(5 - .Column + 1).Resize(, .Count - 5 + .Column)
Set Rf = .Find("-", , xlValues, xlPart, xlByRows)
If Not Rf Is Nothing Then
A = Rf.Address
Application.ScreenUpdating = False
Do
R = R + 1
Cells(R, 2).Resize(, 2).Value2 = Array(Rf.Value2, Application.Sum(Rf(1, 3).Resize(8)))
Set Rf = .FindNext(Rf)
Loop Until Rf.Address = A
Set Rf = Nothing
Application.ScreenUpdating = True
End If
End With
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 06-13-2019 at 08:12 PM.
Reason: optimization for a single sheet in the workbook …
LinkBack URL
About LinkBacks
Register To Reply
Hi ! Try this ‼
Bookmarks