Code to create a Histogram

[Don Ireland]

I am trying to use some VB code to generate a histogram of data. The input
range for the histogram can vary in the number of rows only. I am trying to
have the code be "generic" enough to know how many rows were filled with data
and use that info to populate the "Input Range" field for the histogram. The
code is as follows:

Application.Run "ATPVBAEN.xla!histogram", ActiveSheet.Range("B2:B3035"),
"Histo Chart", ActiveSheet.Range("$a$2:$a$58"), False, True, True, False

I would like to replace the first Activesheet.range statement with a
variable name. However, when I build the string value, I cannot place the
double quotes in the string (the macro thinks it is at the end of the string
value and won't insert the double quote).

The string I am trying to build is similar to :
InputRange = "Activesheet.range("b2:B" & ActualCount

I would then insert the InputRange variable for the first Activesheet.range
statement in the call to the Histogram. The macro gets lost after the second
double-quote.

Short of completely coding the histogram generation, does anyone have any
suggestions or ideas?

Please let me know.

Thanks
Don

 

[Tom Ogilvy]

Don't make a string, because that would be the long way around anyway. Use a
range reference as shown. I showed you another way to do it for the
reference for column A.

Dim ActualCount as Long
Dim InputRange as Range
' code that assigns actualcount a value ex:
' actualcount = Application.CountA(columns(2))
set InputRange = Activesheet.Range("B2:B" & ActualCount)
Application.Run "ATPVBAEN.xla!histogram", InputRange,
"Histo Chart", ActiveSheet.Range("$a$2").Resize(Actualcount,1), _
False, True, True, False

 

[Don Ireland]

I will give a try and let you know.

thanks

Don

Don't make a string, because that would be the long way around anyway. Use a
range reference as shown. I showed you another way to do it for the
reference for column A.

Dim ActualCount as Long
Dim InputRange as Range
' code that assigns actualcount a value ex:
' actualcount = Application.CountA(columns(2))
set InputRange = Activesheet.Range("B2:B" & ActualCount)
Application.Run "ATPVBAEN.xla!histogram", InputRange,
"Histo Chart", ActiveSheet.Range("$a$2").Resize(Actualcount,1), _
False, True, True, False

 

[Don Ireland]

IT WORKED!!!!!!!!!!!!!!!!!!!!!!

Thank you for the assist!

I am assuming that anytime I need a range, I can establish a variable as a
range type and then set it with Set command (that makes sense...) The
Actualcount reference that you included (application.counta.. I am not sure
what that is doing. though. I tried doing a search for in in VB and return
empty. Can you offer a suggestion or point me in the right direction?


Thanks again for the help

Don

 

[Tom Ogilvy]

It is the
=counta(A1:A30)

that you would use in the worksheet. Many, although not all, worksheet
functions are available for use in VBA (set the worksheetfunction object in
the object browser). You can often avoid looping and so forth using these
function. (application.Sum, application.Average, Application.Countif,
application.Vlookup).

In this case, counta counts non-empty cells If you data doesn't have blanks
embedded in it, it should count the number of cells in the column which are
non-blank and tell you the bottom row of your data (assuming it starts in
row1).

It was just a placeholder. You can do what you want.
cells(rows.count,2).End(xlup).row for example would probably be faster in
this case.

 

[Mike Middleton]

Don -

If you want to have a slightly better histogram, you might decide on
"completely coding the histogram generation," borrowing the code from my
free Better Histogram add-in, available for download from www.treeplan.com.

- Mike
www.mikemiddleton.com