Automatic range selection problem

[Pepper75]

Dear Mr Excel, Dear users,

Being new to VBA is quite a challenge and I am finding that this one is quite a big one! I would like to request your help in helping me find a solution to an automatic range issue.

Problem : I have two different excel workbooks both having a different number of columns and a different number of rows
example -> workbook 1: 5 columns /10 rows
workbook 2: 12 columns / 200 rows

Target: What I would like to obtain is a code that would work on both workbooks and create a sum and a count of all rows but that would adapt to a change in the number of rows and columns if the code is copied from one workbook to another.

example -> John Peter Paul James William Count Sum
Monday 1 5 7 0 3 4 16
Tuesday 5 0 0 12 0 2 17

Many thanks in advance to all for your help and time!

François

 

[farmerscott]

Hi Francois,

1. do your tables start at any particular cell (eg A1) or can they be anywhere in the worksheet?
2. from your data are counting only cells >0?

thx

FarmerScott

 

[Pepper75]

Hello FarmerScott,

Thank you very much for your reply!
Yes my tables start on the same cell : example -> John (C2) Peter Paul James William Count Sum
Monday(B3) 1 5 7 0 3 4 16
Tuesday 5 0 0 12 0 2 17


Regarding the numbers, there will never be a negative number. There may be the number 0 or a blank cell to indicate 0.
I hope these indications will help. If need be I can send an existing example or post it (if possible) in this thread.

Thanks again!

François

 

[farmerscott]

Hi Francois,

give this code a try-

I have assumed the first name will always be in C2.
The code should handle both 0's or blanks.

Sub Count_and_sum()
Dim lr As Integer
Dim lc As Integer
Dim x As Integer
Dim rngtocount As Range
Dim rngtosum As Range

lr = Worksheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row
lc = Worksheets("sheet1").Cells("2", Columns.Count).End(xlToLeft).Column
 
 
For x = 3 To lr
Set rngtocount = Range(Cells(x, 3), Cells(x, lc))

Count = WorksheetFunction.CountIf(rngtocount, ">0")
Cells(x, lc + 1).Value = Count
Next x
For x = 3 To lr
Set rngtosum = Range(Cells(x, 3), Cells(x, lc))

Sum = WorksheetFunction.Sum(rngtosum)
Cells(x, lc + 2).Value = Sum
Next x
Cells(2, lc + 1).Value = "Count"
Cells(2, lc + 2).Value = "Sum"
End Sub

hope that helps,

FarmerScott

 

[farmerscott]

Also, if all your worksheets (with the different tables) are in the same workbook-

1. put the code in the 'workbook' module.
2.change "sheet1" to the name of the sheet you want.

FarmerScott

 

[Pepper75]

Hello FarmerScott,

After trying out your code, almost everything is working! The "count" and "sum" columns appear and within the desired results but by some problem the data stops short of the end row.
For example -> the data goes on till row 712 but the code makes the calculations stop on row 688. I took the liberty to try the code out on another workbook and the results are the same.

Thank you again!

François

 

[farmerscott]

Francios,

I glad I have gotten close to solving your problem.....

The only thing I can suggest as to the issue, change the wording in the code from 'integer' to 'long' in the Dim statements.

The maximum integer number should go to row 712 without any issue so I am lost for any meaningful suggestion at this time. (Excel help states that the integer data type should go to 32,700+.)

Also just check your data in those last couple of rows, you might have some different formatting in there.

FarmerScott

 

[Pepper75]

Hello FarmerScott,

I have tried your changes on the code but to no avail.

Changing the word 'integer' to 'long' did not change the fact that when lauching the macro the count and sum columns stop short of the end of the data (in this particular test, my data ranged till row 634 but the macro stops on row 621).
I tried within the same workbook to delete the rows till row 500 to see if it would fill the data up to this row but the macro kept stopping short -> row 484
It feels like the macro follows a percentage principle and will never go to the end of the data.
I know it is a lot to ask but any other ideas?

Thank you very much,

François

 

[farmerscott]

Francois,

can you rerun the code in 'break mode', that is, instead of hitting F5, press F8. This will enact the code line by line.

When the yellow highlight gets to the following line....

lr = Worksheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Row

put your pointer over the 'lr' and check to see if that matches --

1. the last row of data in Col C
2. and the last row of, the rest of your data.

FarmerScott

 

[Pepper75]

FarmerScott,

Actually no, the lr function tells me row 621 which is the row on which the data stops and not (in this case) row 634.
I also made sure that all the data has the same formatting.
Is there a way to maybe send you the xls file?

François