View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
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