Count chartbjects from multiple sheets in a excel file

[Sagar0650]

I have 3 charts in three different sheets in excel workbook.
I want to count the number of charts present in excel workbook.
I have tried activesheet.chartobjects.count
It gives the count of charts present in that particular sheet.
Can anyone has solution to count all the charts present across the whole workbook?

 

[Richard U]

something like....

sub countem()
dim ws as worksheet
dim ctr as integer

for each ws in ThisWorkbook
     ctr = ctr + activesheet.chartobjects.count
next ws

end sub

 

[Sagar0650]

Thank you Richard for quick response.

for each ws in worksheets
ctr = ctr + activesheet.chartobjects.count
next ws

this code worked for me
again thank you for helping me out
much appreciated

 

[Richard U]

YW, Please feel free to reach out to me any time.

 

[Sagar0650]

Sure Richard.
thank you

 

[Richard U]

Sure Richard.
thank you

Yeah, that should have been for each ws in thisworkbook.worksheets

Sorry about that. glad I could steer you in the right direction though.

BTW, always be explicit in your code If multiple sheets/workbooks are open, you can get strange results.

So, for example, I typically do something like.

Dim wb as workbook
dim ws as worksheet
dim ws2 as worksheet

set wb = thisworkbook
set ws = wb.sheets(1)
set ws2 = wb.sheets(2)


then later in the code....


ws.cells(1,1).value = ws2.cells.value


or whatnot.

It makes it easier to read, and less likely to cause trouble if multiple workbooks are open, or if your code is interrupted and an unexpected change happens.

 

[Sagar0650]

BTW, always be explicit in your code If multiple sheets/workbooks are open, you can get strange results.

yes it creates problem and as you said it is good to always have explicit code.
Thank you for sharing

 

[Sagar0650]

loop through merged cells in a Column to get their addresses

I have one column. say column A, which has multiple merged cells of different ranges. for example first cell is merged from A2 to A15 whereas second merged cell ranges from A16 to A115 now i want to run through entire column to get the address of each merged cell. i have code which helps me to...

www.mrexcel.com

can you please check & help me with this one?