Hit OK for error box in VBA

[crockwell]

Hello,

I'm writing a code that calculates a histogram on a series of pages in my Excel workbook. The histogram data replaces a preexisting calculated histogram. I can't seem to get my code to ignore the popup box that says Output range will overwrite existing data. Press OK to overwrite data...

I've tried DisplayAlerts=False, EnableEvents=False and ScreenUpdating=False... and none of them can stop the error box coming up for each spreadsheet.

Thank you to anyone in advance!

Here's my code:

Sub NewHistogram()
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets("0000").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("0001").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("0500").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("1000").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("1500").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("2000").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("2500").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("3000").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("3500").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("4000").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("4500").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Sheets("5000").Select
Histogram ActiveSheet.Range("$b$1:$b$50001"), ActiveSheet.Range("$k$7"), ActiveSheet.Range("$i$8:$i$11"), False, False, False, False
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[shg]

What is the code for Histogram?

EDIT: Also, more compactly,

Sub NewHistogram()
    Dim asWks()     As String
    Dim i           As Long
 
    asWks = aplit("0000 0001 0500 1000 2000 2500 3000 3500 4000 4500 5000", " ")
 
    For i = 0 To UBound(asWks)
        With Worksheets(asWks(i))
            Histogram .Range("B1:$B50001"), .Range("K7"), .Range("I8:I11"), False, False, False, False
        End With
    Next i
End Sub

 

[shg]

aplit("0000 ...

should be

Split("0000 ...

 

[crockwell]

The code for histogram:

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

where the mandatory ranges are:
inprng=input range
outrng=output range
binrng=bin range

and the options are:
pareto=sorted histogram
chartc=cumulative percentage
chart=chart output
labels

Thank you for the consolidated code. Any ideas on how to get the error messages to stop popping up?

Thanks

 

[Scott Huish]

That's not the code for Histogram, that is the syntax.

Histogram is not a native function, so a UDF must be defined somewhere. What is that code?