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]

Have you tried
Range("Test")
instead of
ActiveSheet.Range("$M$17:$M$29")

M.

 

[Jarke]

I tried but when I use the macro it converts the name range into the current range (example c4:c20).

1. I create the macro with the name range
2. it works with the range
3. I add further data, in the rows below
4. the macro does not update the range but uses the range that was created in 1. instead


Need it to update :/

 

[Marcelo Branco]

How did you create the dynamic named range?
Show us the formula you used

M.

 

[Jarke]

I use this:

=OFFSET(Sheet1!$M$17;0;0;COUNT(Sheet1!$M:$M);1)

 

[Marcelo Branco]

hmm...your formula seems ok

Insert this code line
Msgbox Range("Test").Address
and tell us what you see

M.

 

[Marcelo Branco]

As an alternative, try

Dim MyRange As Range
With Sheets("Sheet1")
    Set MyRange = .Range("M17:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
End With
'rest of code

And use
MyRange
instead of
ActiveSheet.Range("$M$17:$M$29")

M.

 

[Jarke]

Hmm, in the data range in data analysis or in the VBA? I have never coded in VBA. Tried to replace it with some code in the VBA, without success. Any pointers on where?
It did not work in the data range in the "descriptive analysis" for example

 

[Marcelo Branco]

I understood that this worked (being Sheet1 the active sheet)

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

So, see if this works

Sub aTest()
    Dim MyRange As Range
    
    With Sheets("Sheet1")
        Set MyRange = .Range("M17:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
        Application.Run "ATPVBAEN.XLAM!Descr", MyRange, .Range("$P$19"), "C", False, True
        .Range("D45").Select
    End With
End Sub

M.

 

[Jarke]

Looks like it works! You are a magician mate! Thanks alot :D

Is it possible to remove the Warning after i press the macro? It says "...will overwrite existing data.... press OK to proceeed".

I won't be able to just Record my macro this way. How should I connect further aspects?

This now updates my Descriptive statistics, is that equal to "Application.Run "ATPVBAEN.XLAM!Descr"" in the formula? Could I just change that and other numbers to include a second function?
What is "D45"?

Do you know the code for other functions in Data Analysis?