Is there any macro that can be made to simplify the code below,, such
that ("$A:$A"), is automatically replaced by B,C,D.....IV, insteading
of having something given below? Please let me know. Thanks

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A:$A"),
_
"", Sheets("Sheet4").Range("$A:$A"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B:$B"),
_
"", Sheets("Sheet4").Range("$B:$B"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$C:$C"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$D:$D"), False, False, True, True
............
................
...................

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$IV:
$IV"), _
"", Sheets("Sheet4").Range("$IV:$IV"), False, False, True, True

Set up a For Loop that goes across the columns:

replace
Range("$A:$A")
with
Cells(i1,i).EntireColumn

For i=1 to 255
some code
Next


--
Gary''s Student - gsnu2007


" wrote:

Is there any macro that can be made to simplify the code below,, such
that ("$A:$A"), is automatically replaced by B,C,D.....IV, insteading
of having something given below? Please let me know. Thanks

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A:$A"),
_
"", Sheets("Sheet4").Range("$A:$A"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B:$B"),
_
"", Sheets("Sheet4").Range("$B:$B"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$C:$C"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$D:$D"), False, False, True, True
............
................
...................

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$IV:
$IV"), _
"", Sheets("Sheet4").Range("$IV:$IV"), False, False, True, True

Try this (untested):

Public Sub MakeHistograms()
Dim wsActive As Worksheet
Dim wsSheet4 As Worksheet
Dim lngColumn As Long

Set wsActive = ActiveSheet
Set wsSheet4 = ActiveWorkbook.Worksheets("Sheet4")

For lngColumn = 1 To 256
Application.Run "ATPVBAEN.XLA!Histogram", _
wsActive.Columns(lngColumn), _
"", _
wsSheet4.Columns(lngColumn), _
False, False, True, True
Next lngColumn
End Sub

--
Regards,
Bill Renaud

On Oct 26, 9:52 am, wrote:
Is there any macro that can be made to simplify the code below,, such
that ("$A:$A"), is automatically replaced by B,C,D.....IV, insteading
of having something given below? Please let me know. Thanks

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A:$A"),
_
"", Sheets("Sheet4").Range("$A:$A"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B:$B"),
_
"", Sheets("Sheet4").Range("$B:$B"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$C:$C"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$D:$D"), False, False, True, True
...........
...............
..................

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$IV:
$IV"), _
"", Sheets("Sheet4").Range("$IV:$IV"), False, False, True, True

I got the concept now but the code gives some error. Can someone help
rectifying the error?

Thanks

Sub hist_gen()
Dim rng As Range
Dim rng1 As Range

Dim i As Integer

For i = 1 To 256


Set rng = Range("A:IV")
'rng.Columns(i).Select

Set rng1 = rng.Columns(i)


Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.rng1, _
"", Sheets("Sheet2").rng1, False, False, True, True

Next
End Sub

On Oct 26, 11:00 am, wrote:
On Oct 26, 9:52 am, wrote:





Is there any macro that can be made to simplify the code below,, such
that ("$A:$A"), is automatically replaced by B,C,D.....IV, insteading
of having something given below? Please let me know. Thanks

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$A:$A"),
_
"", Sheets("Sheet4").Range("$A:$A"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$B:$B"),
_
"", Sheets("Sheet4").Range("$B:$B"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$C:$C"), False, False, True, True

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$C:$C"),
_
"", Sheets("Sheet4").Range("$D:$D"), False, False, True, True
...........
...............
..................

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("$IV:
$IV"), _
"", Sheets("Sheet4").Range("$IV:$IV"), False, False, True, True

I got the concept now but the code gives some error. Can someone help
rectifying the error?

Thanks

Sub hist_gen()
Dim rng As Range
Dim rng1 As Range

Dim i As Integer

For i = 1 To 256

Set rng = Range("A:IV")
'rng.Columns(i).Select

Set rng1 = rng.Columns(i)

Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.rng1, _
"", Sheets("Sheet2").rng1, False, False, True, True

Next
End Sub- Hide quoted text -

- Show quoted text -

Bill, Gary Thanks for your help.

Bill, I am using your code. But histogram gives an error saying that
"Input range must contain atleast one value".

So, I would like to have a condition like "If column in the
activehseet does not have any data, then skip that column and go to
next column". Please let me know.. Gary, if you know this, then please
let me know the code for this. Thanks

Compiled, but only partially tested (I commented out the Application.Run
command and used a MsgBox):

'----------------------------------------------------------------------
Public Sub MakeHistograms()
Dim wsActive As Worksheet
Dim wsSheet4 As Worksheet
Dim lngColumn As Long
Dim rngColumn As Range
Dim rngCellsWithData As Range

Set wsActive = ActiveSheet
Set wsSheet4 = ActiveWorkbook.Worksheets("Sheet4")

On Error Resume Next

For lngColumn = 1 To 256
Set rngColumn = wsActive.Columns(lngColumn)
Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

If Not rngCellsWithData Is Nothing _
Then
Application.Run "ATPVBAEN.XLA!Histogram", _
rngColumn, _
"", _
wsSheet4.Columns(lngColumn), _
False, False, True, True
End If
Set rngCellsWithData = Nothing 'Reset for next loop.
Next lngColumn
End Sub

--
Regards,
Bill Renaud

On Oct 26, 11:43 am, "Bill Renaud"
wrote:
Compiled, but only partially tested (I commented out the Application.Run
command and used a MsgBox):

'----------------------------------------------------------------------
Public Sub MakeHistograms()
Dim wsActive As Worksheet
Dim wsSheet4 As Worksheet
Dim lngColumn As Long
Dim rngColumn As Range
Dim rngCellsWithData As Range

Set wsActive = ActiveSheet
Set wsSheet4 = ActiveWorkbook.Worksheets("Sheet4")

On Error Resume Next

For lngColumn = 1 To 256
Set rngColumn = wsActive.Columns(lngColumn)
Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

If Not rngCellsWithData Is Nothing _
Then
Application.Run "ATPVBAEN.XLA!Histogram", _
rngColumn, _
"", _
wsSheet4.Columns(lngColumn), _
False, False, True, True
End If
Set rngCellsWithData = Nothing 'Reset for next loop.
Next lngColumn
End Sub

--
Regards,
Bill Renaud

Bill, Justto test I have three columns with data and the code works
well for rows with data. When I becomes 4, I get an error. "No cells
were found" for this line

Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

I tested with msgbox too and still the error appears.. Any idea why
this is happening. Thanks

On Oct 26, 12:01 pm, wrote:
On Oct 26, 11:43 am, "Bill Renaud"
wrote:





Compiled, but only partially tested (I commented out the Application.Run
command and used a MsgBox):

'----------------------------------------------------------------------
Public Sub MakeHistograms()
Dim wsActive As Worksheet
Dim wsSheet4 As Worksheet
Dim lngColumn As Long
Dim rngColumn As Range
Dim rngCellsWithData As Range

Set wsActive = ActiveSheet
Set wsSheet4 = ActiveWorkbook.Worksheets("Sheet4")

On Error Resume Next

For lngColumn = 1 To 256
Set rngColumn = wsActive.Columns(lngColumn)
Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

If Not rngCellsWithData Is Nothing _
Then
Application.Run "ATPVBAEN.XLA!Histogram", _
rngColumn, _
"", _
wsSheet4.Columns(lngColumn), _
False, False, True, True
End If
Set rngCellsWithData = Nothing 'Reset for next loop.
Next lngColumn
End Sub

--
Regards,
Bill Renaud

Bill, Justto test I have three columns with data and the code works
well for rows with data. When I becomes 4, I get an error. "No cells
were found" for this line

Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

I tested with msgbox too and still the error appears.. Any idea why
this is happening. Thanks- Hide quoted text -

- Show quoted text -

Bill, I got it right now. Initially I commented the line On Error
Resume Next. Now, I understand you had had it on purpose. Thanks

Yes, it would probably be better to use something like:

For lngColumn = 1 To wsActive.UsedRange.Columns.Count

This will avoid looping through empty columns after the last one and will
make the macro run a little faster. This assumes that column $A is not
empty.
--
Regards,
Bill Renaud

On Oct 26, 11:43 am, "Bill Renaud"
wrote:
Compiled, but only partially tested (I commented out the Application.Run
command and used a MsgBox):

'----------------------------------------------------------------------
Public Sub MakeHistograms()
Dim wsActive As Worksheet
Dim wsSheet4 As Worksheet
Dim lngColumn As Long
Dim rngColumn As Range
Dim rngCellsWithData As Range

Set wsActive = ActiveSheet
Set wsSheet4 = ActiveWorkbook.Worksheets("Sheet4")

On Error Resume Next

For lngColumn = 1 To 256
Set rngColumn = wsActive.Columns(lngColumn)
Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

If Not rngCellsWithData Is Nothing _
Then
Application.Run "ATPVBAEN.XLA!Histogram", _
rngColumn, _
"", _
wsSheet4.Columns(lngColumn), _
False, False, True, True
End If
Set rngCellsWithData = Nothing 'Reset for next loop.
Next lngColumn
End Sub

--
Regards,
Bill Renaud

Bill, Justto test I have three columns with data and the code works
well for rows with data. When I becomes 4, I get an error. "No cells
were found" for this line

Set rngCellsWithData = rngColumn.SpecialCells(xlCellTypeConstants)

Any idea why this is happening. I tested with msgbox too and still the
error appears

I am still running Excel 2000, so can't exactly verify what you are seeing.
I think the Analysis ToolPak works slightly differently in different
versions of Excel. Whenever I turn on the macro recorder and attempt to
create a Histogram, I get the following code:

Application.Run "ATPVBAEN.XLA!Histogram", , "", , _
False, False, True, True

The macro recorder returns nothing for the 1st and 3rd arguments of the
Histogram function. According to the Object Browser, the call and arguments
for the Excel 2000 version of the Analysis ToolPak Histogram function
is/are as follows:

Sub Histogram(inprng, [outrng], [binrng], [pareto], [chartc], [chart],
[labels])

Therefore, I am assuming from your recorded macro that you are attempting
to specify the input range and the bin range, but not the output range. You
are also specifying that your input data has labels at the top of the data
and you are requesting a chart to be made.

1. Is each column of data on the active sheet a different number of rows
(maybe we should assume they are, even if they are not).
2. Does each column of data on the active sheet (the "Data") actually need
a separate set of Bin values ("Sheet4")?
3. What is on "Sheet4" in your workbook? Is this where you have your Bin
values specified? Do they have a label on row 1 (i.e. "Bin" or something)?
4. I notice that the macro runs a long time when you allow the input range
and Bin range to be an entire column. We should probably shorten this down
to be the cells that actually have data.

Let me know more specifically what the data looks like on both worksheets.
I'll then attempt to fix the macro.

You might also refer to the Microsoft KB article "How to create an Excel
histogram by using Automation and Analysis ToolPak" at
http://support.microsoft.com/default...b;en-us;270844 for some
additional ideas and tips.
--
Regards,
Bill Renaud