Code to create a Histogram

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

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

--
Regards,
Tom Ogilvy


"Don Ireland" wrote:

> 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

I will give a try and let you know.

thanks

Don

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Don Ireland" wrote:
>
> > 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

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
"Don Ireland" wrote:

> I will give a try and let you know.
>
> thanks
>
> Don
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Don Ireland" wrote:
> >
> > > 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

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.

--
Regards,
Tom Ogilvy


"Don Ireland" wrote:

> 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
> "Don Ireland" wrote:
>
> > I will give a try and let you know.
> >
> > thanks
> >
> > Don
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Don Ireland" wrote:
> > >
> > > > 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

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

"Don Ireland" <DonIreland@discussions.microsoft.com> wrote in message
news:B9CFC656-8677-47C4-B314-0B267F8D7E9F@microsoft.com...
> 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
> "Don Ireland" wrote:
>
>> I will give a try and let you know.
>>
>> thanks
>>
>> Don
>>
>> "Tom Ogilvy" wrote:
>>
>> > 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
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Don Ireland" wrote:
>> >
>> > > 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