Dynamic Name Range With Data Analysis, Macro

[Jarke]

Hi,

My goal is to create a macro where it updates my data analysis, such as descriptive analysis, histogram, correlation etc. I've managed to record a macro to do this.

The problem is that i will update my data, add more rows to it, thus the ranges withing the data analysis (histogram..) will change automatically with my extra input. The best solution would be to put dynamic named ranges in the data analysis ranges. This does unfortunately not work for me yet.

It works to put a dynamic name range when creating the macro, but when I want to use the macro it says: "Input range missing. Please Enter." Thus my dynamic range disappears from the macro.

How should I go about this? Am I missing something?

How can I use dynamic name ranges in a macro?

I managed to get abit furher, but the dynamic name range was replaced with $M$17:$M$29

My name range name is "Test"

Could I replace the range in the code with: =Sheet1!Test

Would love any help!

- I want my macro to update my data analysis with just a click, without having to update the ranges within the analysis tools, thus using dynamic name ranges.

VBA code:
Sub Macro10()
'
' Macro10 Macro
'

'
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$M$17:$M$29"), _
ActiveSheet.Range("$P$19"), "C", False, True
Range("D45").Select
End Sub
Sub Macro11()
'
' Macro11 Macro
'

'
Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$M$17:$M$29"), _
ActiveSheet.Range("$P$19"), "C", False, True
Range("E37").Select
End Sub

 

[Marcelo Branco]

Glad it worked!

Don't know how to answer your questions...

D45? I copied this from your first macro

M.

 

[Jarke]

^_^

I figured out what D45 was, it is what cell excel goes to after I have pressed the macro.

Sorry for being unprecise.

The macro now successfully updates my descriptive statistics table, which is great. I also want the same macro to update my histogram table, correlation and regression table.

I now have the basis for selecting the data, such as
"Dim MyRange As Range
With Sheets("Trade")
Set MyRange = .Range("M41:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
Application.Run "ATPVBAEN.XLAM!Descr", MyRange, .Range("$Y$6"), "C", False, True
.Range("q5").Select"

I found the application specification "ATPVBAEN.XLAM!Descr"
For regression it would be .XLAM!Regress"

My question is how I should code to create the same macro but for correlation, regression and histogram. All of them uses two data ranges, not one. Do you have any idea? It's above my knowledge level :/

 

[Marcelo Branco]

I have never used Analysis Toolpak. I was only trying do define your data range dynamically.
Now that you know how to do this, try the same thing for the second range. It should work.

M.

 

[Jarke]

I managed to get the Histogram function to work also! Will try the next one now

 

[Marcelo Branco]

There is a typo in your code
Application.Run "ATPVBAEN.XLAM!Histogram", Range _
, ActiveSheet.Range("$U$19"), ActiveSheet.Range("$M$17:$M$25"), False, _
False, False, False

and as the code is inside a block With Sheets("Sheet1") ...End With all the ranges inside such block refer to Sheet1. So you should not use ActiveSheet

Try

Sub UpdateHistogram()
    Dim MyRange As Range
    
    With Sheets("Sheet1")
        Set MyRange = .Range("L17:L" & .Cells(.Rows.Count, "L").End(xlUp).Row)
        
        Application.Run "ATPVBAEN.XLAM!Histogram", [COLOR=#0000cd]MyRange[/COLOR] _
            , .Range("$U$19"), .Range("$M$17:$M$25"), False, _
            False, False, False
    End With
End Sub

Next time, try to indent your code in blocks and post it inside the tags

 ...[/ CODE]  (without the blank after /)
These tags increase the readability of the code and make the analysis easier

M.

 

[Marcelo Branco]

I managed to get the Histogram function to work also! Will try the next one now

Good job!
I noticed you edited the post. Anyway, take a look in my comments above.

M.

 

[Marcelo Branco]

To use dynamic ranges try something like this

Sub UpdateHistogram()
    Dim lastRow As Long, Range1 As Range, Range2 As Range
    
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
        Set Range1 = .Range("L17:L" & lastRow)
        Set Range2 = .Range("M17:M" & lastRow)
        
        Application.Run "ATPVBAEN.XLAM!Histogram", Range1 _
            , .Range("$U$19"), Range2, False, False, False, False
    End With
End Sub

M.

 

[Jarke]

Okey, will remove ActiveSheet then.
Your pointers are great, learn so much faster with some direction!
My project now is attempting to connect the two into one macro. I came this far, but can't see what's wrong.

Sub UpdateDSandHist()

Dim MyRange As Range
Dim MyRange2 As Range2
    
    With Sheets("Sheet1")
    Set MyRange = .Range("M17:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
    Set MyRange2 = .Range("L17:L" & .Cells(.Rows.Count, "L").End(xlUp).Row)
        
        Application.Run "ATPVBAEN.XLAM!Descr", MyRange, .Range("$P$19"), "C", False, True
        .Range("D45").Select
        
        Application.Run "ATPVBAEN.XLAM!Histogram", MyRange2 _
        , .Range("$U$19"), .Range("$M$17:$M$25"), False, _
        False, False, False
End With
End Sub

Hmm, but we already use dynamic ranges? As I understand it, we have created "dynamic ranges" within the VBA code instead of creating it in the Name Manager.
Is there any difference between your above post regarding dynamic ranges than what we are currently coding? A k a "Set MyRange = ......."

 

[Marcelo Branco]

Hmm, but we already use dynamic ranges? As I understand it, we have created "dynamic ranges" within the VBA code instead of creating it in the Name Manager.
Is there any difference between your above post regarding dynamic ranges than what we are currently coding? A k a "Set MyRange = ......."

Not exactly. We are not creating dynamic ranges, i.e., after the macro there will not be ranges in Names Manager.
What really happens is that the macro, on-the-fly, defines the ranges dynamically. But such ranges disappear after the macro run.

M.

 

[Marcelo Branco]

Question
As i said, i never used this add-in, so i don't know, in the case of Histogram where you need two ranges (columns L and M), if these ranges need to be the same size.
Could you clarify?

M.