Home 
Search 
Today's Posts 
#11




Count the sum of multiply values in one cell, registered as part of a textcode, combine them per column and show the results in the next sheet
Hi Johan,
Am Fri, 31 May 2019 22:35:37 0700 (PDT) schrieb JS SL: In one of the first codes it was; For i = LBound(varNames, 2) To UBound(varNames, 2) z = i + 10 So, because I inserted also some columns in Sheet1 it was easy understanding to change i+10 to i+26 because I change the startingpoint to column AA. You have change this part of the code to LColSh1 = .Cells(5, .Columns.Count).End(xlToLeft).Column varNames = .Range(.Cells(5, firstSh1), .Cells(5, LColSh1)) So I thought I have to change it to LColSh1 = .Cells(27, .Columns.Count).End(xlToLeft).Column varNames = .Range(.Cells(5, firstSh1), .Cells(27, LColSh1)) when you insert columns the new ranges will be calculated automatically. The only thing you must change in the code is the range for the codes when this range has changed. Make some test without changing the range for the codes. Insert or delete columns in both sheets and you will see that the output is always correct. Regards Claus B.  Windows10 Office 2016 
#12




Count the sum of multiply values in one cell, registered as partof a textcode, combine them per column and show the results in the next sheet
Oke, so.... if I understand it correctly then the only thing I had to change is the Sheet2 column were the TextCode is registered. This is at this moment column H. Just as you remarked clear in the code.
In Sheet1 its no issue that the TextCodes are registered in column AA and so further on. Oke. Then it should work, but.. the code still gives an error on below last rule; For i = LBound(varNames, 2) To UBound(varNames, 2) z = firstSh1  1 + i If Application.CountA(.Range(.Cells(6, z), .Cells(LRowSh1, z))) = 0 Then GoTo Skip For j = LBound(varCodes) To UBound(varCodes) x = j + 1 codeSum = 0 For Each rngC In .Range(.Cells(6, z), .Cells(LRowSh1, z)).SpecialCells(xlCellTypeConstants) If InStr(rngC, Chr(10)) = 0 Then sCode = Left(Split(rngC, "[")(0), Len(Split(rngC, "[")(0))  1) iNmbr = Left(Split(rngC, "[")(1), Len(Split(rngC, "[")(1))  1) The last rule gives the error (iNmbr....). Whats the problem here ? regards, Johan 
#13




Count the sum of multiply values in one cell, registered as part of a textcode, combine them per column and show the results in the next sheet
Hi Johan,
Am Fri, 31 May 2019 22:35:37 0700 (PDT) schrieb JS SL: In one of the first codes it was; For i = LBound(varNames, 2) To UBound(varNames, 2) z = i + 10 So, because I inserted also some columns in Sheet1 it was easy understanding to change i+10 to i+26 because I change the startingpoint to column AA. You have change this part of the code to LColSh1 = .Cells(5, .Columns.Count).End(xlToLeft).Column varNames = .Range(.Cells(5, firstSh1), .Cells(5, LColSh1)) So I thought I have to change it to LColSh1 = .Cells(27, .Columns.Count).End(xlToLeft).Column varNames = .Range(.Cells(5, firstSh1), .Cells(27, LColSh1)) when you insert columns the new ranges will be calculated automatically. The only thing you must change in the code is the range for the codes when this range has changed. Make some test without changing the range for the codes. Insert or delete columns in both sheets and you will see that the output is always correct. Regards Claus B. Claus, Using Defined Names for various ranges eliminates the problems associated with range addresses changing when inserting/deleting cols/rows, AND eliminates the need for code updating as a result!  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#14




Count the sum of multiply values in one cell, registered as part of a textcode, combine them per column and show the results in the next sheet
Hi Johan,
Am Sat, 1 Jun 2019 00:30:16 0700 (PDT) schrieb JS SL: Oke, so.... if I understand it correctly then the only thing I had to change is the Sheet2 column were the TextCode is registered. This is at this moment column H. Just as you remarked clear in the code. In Sheet1 its no issue that the TextCodes are registered in column AA and so further on. Oke. Then it should work, but.. the code still gives an error on below last rule; For i = LBound(varNames, 2) To UBound(varNames, 2) z = firstSh1  1 + i If Application.CountA(.Range(.Cells(6, z), .Cells(LRowSh1, z))) = 0 Then GoTo Skip For j = LBound(varCodes) To UBound(varCodes) x = j + 1 codeSum = 0 For Each rngC In .Range(.Cells(6, z), .Cells(LRowSh1, z)).SpecialCells(xlCellTypeConstants) If InStr(rngC, Chr(10)) = 0 Then sCode = Left(Split(rngC, "[")(0), Len(Split(rngC, "[")(0))  1) iNmbr = Left(Split(rngC, "[")(1), Len(Split(rngC, "[")(1))  1) The last rule gives the error (iNmbr....). for me it works fine. I supposed that in Sheet1 row 5 the first entry is one of the names. If you have other entries in row 5 in front of the names you have to change the line with Application.Match to the first name instead of "*" Now I have created a range name for the codes. You can insert or delete columns without changing the code: Sub SumCodes() Dim LColSh1 As Integer, LColSh2 As Integer, i As Integer, n As Integer, x As Integer, z As Integer Dim LRowSh1 As Long, LRowSh2 As Long, j As Long Dim varNames As Variant, varCodes As Variant, varTmp As Variant Dim codeSum As Long Dim rngC As Range Dim sCode As String, iNmbr As Integer, firstSh1 As Integer With Sheets("Sheet2") LColSh2 = .Cells(1, .Columns.Count).End(xlToLeft).Column LRowSh2 = .Cells(.Rows.Count, "A").End(xlUp).Row varCodes = Range("Codes") End With Application.ScreenUpdating = False With Sheets("Sheet1") LRowSh1 = .Cells(.Rows.Count, "A").End(xlUp).Row 'first col with a name firstSh1 = Application.Match("*", .Range("5:5"), 0) LColSh1 = .Cells(5, .Columns.Count).End(xlToLeft).Column varNames = .Range(.Cells(5, firstSh1), .Cells(5, LColSh1)) For i = LBound(varNames, 2) To UBound(varNames, 2) z = firstSh1  1 + i If Application.CountA(.Range(.Cells(6, z), .Cells(LRowSh1, z))) = 0 Then GoTo Skip For j = LBound(varCodes) To UBound(varCodes) x = j + 1 codeSum = 0 For Each rngC In .Range(.Cells(6, z), .Cells(LRowSh1, z)).SpecialCells(xlCellTypeConstants) If InStr(rngC, Chr(10)) = 0 Then sCode = Left(Split(rngC, "[")(0), Len(Split(rngC, "[")(0))  1) iNmbr = CInt(Left(Split(rngC, "[")(1), Len(Split(rngC, "[")(1))  1)) If Application.CountIf(Range("Codes"), sCode) = 0 Then rngC.Interior.Color = vbRed ElseIf sCode Like varCodes(j, 1) & "*" Then codeSum = codeSum + iNmbr End If Else varTmp = Split(rngC, Chr(10)) For n = LBound(varTmp) To UBound(varTmp) sCode = Left(Split(varTmp(n), "[")(0), Len(Split(varTmp(n), "[")(0))  1) iNmbr = CInt(Left(Split(varTmp(n), "[")(1), Len(Split(varTmp(n), "[")(1))  1)) If Application.CountIf(Range("Codes"), sCode) = 0 Then rngC.Interior.Color = vbRed ElseIf sCode Like varCodes(j, 1) & "*" Then codeSum = codeSum + iNmbr End If Next End If Next If codeSum 0 Then Sheets("Sheet2").Cells(x, LColSh2  UBound(varNames, 2) + i) = codeSum Next Skip: Next End With Application.ScreenUpdating = True End Sub Regards Claus B.  Windows10 Office 2016 
#15




Count the sum of multiply values in one cell, registered as part of a textcode, combine them per column and show the results in the next sheet
Hi Garry,
Am Sat, 01 Jun 2019 04:06:50 0400 schrieb GS: Using Defined Names for various ranges eliminates the problems associated with range addresses changing when inserting/deleting cols/rows, AND eliminates the need for code updating as a result! you are right. I changed it. Regards Claus B.  Windows10 Office 2016 
#16




Count the sum of multiply values in one cell, registered as partof a textcode, combine them per column and show the results in the next sheet
Claus, Garry,
Thanks both. It works super. The trick is that for row.5 the cells in the same row before the startcolumn should be empty. That's oke for me. The other attention comes from Claus is to set a RangeName in Sheet.2 at the TextCode column (in this case column H). Herewith the final and working code from Claus. THANKS !! Claus you are great !!! regards, Johan.  Sub SumCodes3() Dim LColSh1 As Integer, LColSh2 As Integer, i As Integer, n As Integer, x As Integer, z As Integer Dim LRowSh1 As Long, LRowSh2 As Long, j As Long Dim varNames As Variant, varCodes As Variant, varTmp As Variant Dim codeSum As Long Dim rngC As Range Dim sCode As String, iNmbr As Integer, firstSh1 As Integer Application.ScreenUpdating = False With Sheets("Count") 'Count = Sheet1 LColSh2 = .Cells(1, .Columns.Count).End(xlToLeft).Column LRowSh2 = .Cells(.Rows.Count, "A").End(xlUp).Row varCodes = Range("Codes") 'Codes is a RangeName in Sheet2 at the column of the TextCodes, for example Column.H, with name "Codes" and formula "=OFFSET(Sheet2!$H$2,,,COUNTA(Sheet2!$H:$H)1)" End With With Sheets("Planning") '=Sheet2 LRowSh1 = .Cells(.Rows.Count, "A").End(xlUp).Row 'first col with a name. The hading starts in row 5. The rowcells before the to use column should be empty ! firstSh1 = Application.Match("*", .Range("5:5"), 0) LColSh1 = .Cells(5, .Columns.Count).End(xlToLeft).Column varNames = .Range(.Cells(5, firstSh1), .Cells(5, LColSh1)) For i = LBound(varNames, 2) To UBound(varNames, 2) z = firstSh1  1 + i If Application.CountA(.Range(.Cells(6, z), .Cells(LRowSh1, z))) = 0 Then GoTo Skip For j = LBound(varCodes) To UBound(varCodes) x = j + 1 codeSum = 0 For Each rngC In .Range(.Cells(6, z), .Cells(LRowSh1, z)).SpecialCells(xlCellTypeConstants) If InStr(rngC, Chr(10)) = 0 Then sCode = Left(Split(rngC, "[")(0), Len(Split(rngC, "[")(0))  1) iNmbr = CInt(Left(Split(rngC, "[")(1), Len(Split(rngC, "[")(1))  1)) If Application.CountIf(Range("Codes"), sCode) = 0 Then rngC.Interior.Color = vbRed ElseIf sCode Like varCodes(j, 1) & "*" Then codeSum = codeSum + iNmbr End If Else varTmp = Split(rngC, Chr(10)) For n = LBound(varTmp) To UBound(varTmp) sCode = Left(Split(varTmp(n), "[")(0), Len(Split(varTmp(n), "[")(0))  1) iNmbr = CInt(Left(Split(varTmp(n), "[")(1), Len(Split(varTmp(n), "[")(1))  1)) If Application.CountIf(Range("Codes"), sCode) = 0 Then rngC.Interior.Color = vbRed ElseIf sCode Like varCodes(j, 1) & "*" Then codeSum = codeSum + iNmbr End If Next End If Next If codeSum 0 Then Sheets("Count").Cells(x, LColSh2  UBound(varNames, 2) + i) = codeSum Next Skip: Next End With End Sub 
#17




Count the sum of multiply values in one cell, registered as part of a textcode, combine them per column and show the results in the next sheet
you are right. I changed it.
Nicely done!  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#18




Count the sum of multiply values in one cell, registered as partof a textcode, combine them per column and show the results in the next sheet
Claus,
ManyMany thanks the way you helped me out. And just as with a nice diner.....there's sometimes the need for a nice dessert. In my case the dessert is the count of the cost based on the values for the headingnames in sheet.2 were for those names in sheet.3 a costvalue is registered (sheet.3 column.B is the name and column.D the cost). Sheet.3; For Mister.X the value 50 is registered and for Mister.Y 100 What's nice to have (the dessert); ps. In sheet.2 I had moved the column with the TextCodes to column.M. In sheet.2 in column.J (perhaps you can give it the RangeName 'cost' so it can flow when inserting/deleting columns), I like to have the sum of the values for each column in the same row. What you get is; Mister.X = value 10  multiply with 50 from sheet.3 = 500. In the same record stands Mister.Y = value 6  multiply with the value 100 in sheet.3 = 600. This gives the sum of 500+600=1100. This should be then the value in column.J of that record in sheet.2. If..... the name in sheet.2 isn't exist in sheet.3 column.B then, if there is a value registered in sheet.2, the cell should be marked red. Another exception.... this count rule must only count if the last 2 digits of that record in sheet.2 column.M (thats the famous 'codes' RangeName) is "HR". Is this a nice dessert or not. It should be great if I can enjoy this !! ps. If easier you can make it a separate code to run. And.... Thanks if possible to do (!) 
#19




Count the sum of multiply values in one cell, registered as part of a textcode, combine them per column and show the results in the next sheet
Hi Johan,
Am Sat, 1 Jun 2019 23:00:46 0700 (PDT) schrieb JS SL: Sheet.3; For Mister.X the value 50 is registered and for Mister.Y 100 In sheet.2 in column.J (perhaps you can give it the RangeName 'cost' so it can flow when inserting/deleting columns), I like to have the sum of the values for each column in the same row. What you get is; Mister.X = value 10  multiply with 50 from sheet.3 = 500. In the same record stands Mister.Y = value 6  multiply with the value 100 in sheet.3 = 600. This gives the sum of 500+600=1100. This should be then the value in column.J of that record in sheet.2. If..... the name in sheet.2 isn't exist in sheet.3 column.B then, if there is a value registered in sheet.2, the cell should be marked red. Another exception.... this count rule must only count if the last 2 digits of that record in sheet.2 column.M (thats the famous 'codes' RangeName) is "HR". that is easier and faster when you do it with formulas and conditional formatting. Regards Claus B.  Windows10 Office 2016 
#20




Count the sum of multiply values in one cell, registered as partof a textcode, combine them per column and show the results in the next sheet
Claus,
The way you suggest with the formula; =IF(RIGHT(M2,2)<"HR","",SUMIF(myNames,N$1,Cost)*N 2+SUMIF(myNames,O$1,Cost)*O2+SUMIF(myNames,P$1,Cos t)*P2+SUMIF(myNames,Q$1,Cost)*Q2) And NameRanges in Sheet.3 for the names and the cost, and also a ConditionalFormatting in Sheet.2, works fine. Thnk !!!. And now playing with the tool created. regards, Johan 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Copying rows values on one sheet to part of a formula in a column  Excel Discussion (Misc queries)  
Count unique values in 1 column where cells in another show nonbl  Excel Discussion (Misc queries)  
Lookup values in 2 columns, count and then multiply??? HELP!  Excel Worksheet Functions  
Part 2 of Show zero values only when data is.....  Excel Discussion (Misc queries)  
Operations (add, multiply) on all values ($00.00) in a column?  Excel Discussion (Misc queries) 