VBA Code/Macro Help

[sDE]

Hi everyone. First time poster. Hoping someone can help me if possible please.

Given the below procedure:

Code:

Sub Update_Strategy_Dropdown_List()
Application.Calculation = xlManual
Dim r As Range
Dim List As Range
Set List = Sheets("Strategy Library").Range("StrategyList")
Dim n As Integer
n = 0
For Each r In List
If Not IsEmpty(r) Then Sheets("Lists").Range("A1").Offset(n, 0) = r.Value
If Not IsEmpty(r) Then n = n + 1
End Sub

This macro is not working. What is this macro intended to do and how would I fix it?

Many thanks in advance

[Paul_Hossler]

Well, based on the macro's name, I'd guess it updates a data validation dropdown list of acceptable strategy values. What would you like it to do?

"Not working" is a tad ambiguous.

Possibly it is working and you didn't realize that the strategies in column A of worksheet Lists used for the Strategy cell were being updated for Data Validation purposes

[sDE]

Well, based on the macro's name, I'd guess it updates a data validation dropdown list of acceptable strategy values. What would you like it to do?

"Not working" is a tad ambiguous.

Possibly it is working and you didn't realize that the strategies in column A of worksheet Lists used for the Strategy cell were being updated for Data Validation purposes

I only have the problem written down on paper Paul so I don't have a great deal to go on. A couple of questions I have are:

• Should the code not specify what range to store in the variable 'r' as it does with the variable 'List'
• Where the code says 'For Each r in List', what does the 'r' refer to?

I'm just getting started with VBA so apologies for all the questions.

[Paul_Hossler]

'r' is a loop variable (I forget the proper name)

'List' a Range, and is Set to the cells in what I assume is a Named Range called StrategyList. You can use Control-F3 to see the named ranges

Capture.JPG


So if the named range contains 15 cells (like in example), the For Each loop will run 15 times, and each time make r Each of those cells for the contents of the loop

The For Each will Set 'r' = A1 do the If's, then 'r' = A2 and do the If's, then A3, ...., A15 from Sheet1 in my example

Sub Update_Strategy_Dropdown_List()

 Application.Calculation = xlManual

 Dim r As Range
 Dim List As Range
 
Set List = Sheets("Strategy Library").Range("StrategyList")
 
Dim n As Integer

 n = 0

For Each r In List.Cells      '-------------------------------- modified a little
      If Not IsEmpty(r) Then Sheets("Lists").Range("A1").Offset(n, 0) = r.Value
      If Not IsEmpty(r) Then n = n + 1
Next                  ' ------------------------------------------ added

End Sub

[sDE]

Thanks Paul.

I was led to believe that whenever you declare a range variable, you have to specify what range to store in the variable which led me to think that may be why there is a problem with the macro. Are we saying it's not necessary to do this if you are using 'Dim r As Range'? I've just tried to create a spreadsheet to test the above code and it doesn't seem to make any difference when I delete 'Dim r As Range' from the code. Is that even necessary in order for the loop to run through?

I also get a 'Run-time error '1004': Application-defined or object-defined error' on the line of code in bold below despite having a 'StrategyList' named range set up within Excel. If I delete 'StrategyList' and specify a range of cells e.g. A1:A7, then the macro seems to run through.

Sub UpdateDropdownList()
Application.Calculation = xlManual
Dim List As Range
    Set List = Worksheets("Strategy Library").Range("StrategyList")
    Dim n As Integer
n = 0
For Each r In List.Cells
If Not IsEmpty(r) Then Sheets("Lists").Range("A1").Offset(n, 0) = r.Value
If Not IsEmpty(r) Then n = n + 1
Next
End Sub

[Paul_Hossler]

1. Use the [#] icon on the message area toolbar to add [ CODE ] tags and paste the macro inside to format it pretty and to set it off

2. Option Explicit at the top of a module forces all variables to be explicitly Dim-ed (e.g. the 'r'). I always do it to avoid typing mistakes, plus some other reasons

3. You need a sheet named "Strategy Library" and "Lists" as well as the named range "StrategyList"

The attachment seems to run OK for me

Option Explicit

Sub UpdateDropdownList()
    Dim List As Range, r As Range
    Dim n As Integer
    Set List = Worksheets("Strategy Library").Range("StrategyList")
    Application.Calculation = xlManual
    n = 0
    
    For Each r In List.Cells
        MsgBox r.Value
        If Not IsEmpty(r) Then Sheets("Lists").Range("A1").Offset(n, 0) = r.Value
        If Not IsEmpty(r) Then n = n + 1
    Next
End Sub