Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to create a Histogram
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to create a Histogram
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to create a Histogram
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to create a Histogram
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to create a Histogram
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a CORRECT histogram? | Charts and Charting in Excel | |||
create a Histogram? | Charts and Charting in Excel | |||
How do I create a histogram | Charts and Charting in Excel | |||
HOW CAN I CREATE A HISTOGRAM GRAPH FOR MY DATA? | Excel Worksheet Functions | |||
HOW CAN I CREATE A HISTOGRAM GRAPH FOR MY DATA? | Excel Worksheet Functions |