Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old June 1st 19, 07:59 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,695
Default 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   Report Post  
Old June 1st 19, 08:30 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old June 1st 19, 09:06 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
Default 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.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #14   Report Post  
Old June 1st 19, 09:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,695
Default 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   Report Post  
Old June 1st 19, 09:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,695
Default 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   Report Post  
Old June 1st 19, 10:41 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old June 1st 19, 08:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
Default 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.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #18   Report Post  
Old June 2nd 19, 07:00 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old June 2nd 19, 01:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,695
Default 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   Report Post  
Old June 2nd 19, 05:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying rows values on one sheet to part of a formula in a column Manosh Excel Discussion (Misc queries) 3 June 23rd 09 03:37 PM
Count unique values in 1 column where cells in another show non-bl Sarah (OGI) Excel Discussion (Misc queries) 4 October 17th 08 04:25 AM
Lookup values in 2 columns, count and then multiply??? HELP! DebbieV Excel Worksheet Functions 4 July 26th 07 11:40 PM
Part 2 of Show zero values only when data is..... gevew Excel Discussion (Misc queries) 6 December 29th 06 05:43 PM
Operations (add, multiply) on all values ($00.00) in a column? DanielG Excel Discussion (Misc queries) 2 April 1st 06 05:47 AM


All times are GMT +1. The time now is 12:26 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017