0

I am trying to find a better solution to more dynamically loop through a cell with a Dynamic list of values from a Database Connection. The cell "B3" has a drop-down list of the items I want to loop through, and when a particular item is selected it refreshes the worksheet to reflect that items values (Select * from Table where Item = X). The number of items changes per quarter, as the items are the list of properties we appraise. Sometimes there are 50, sometimes 5 items. How do I modify this code so I do not have to go into the Macro and update the For i = 1 to 49 loop? I want is so it just can either query the item list and find the number of items or does a Do While loop and loops through all the values until the last one.

Dim strfindrow As String
For i = 1 To 49
    Range("B3").Select
    strfindrow = "=DATA_PROPERTY!R[" + Trim(Str(i - 2)) + "]C[-1]"
    ActiveCell.FormulaR1C1 = strfindrow
    Range("B3").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next
End Sub
Kevin Devine
  • 21
  • 6
  • 1
    https://www.rondebruin.nl/win/s9/win005.htm – Marcucciboy2 Sep 25 '18 at 14:31
  • 1
    When you say "cell B3 has a drop-down list", are you referring to Data Validation with Type = List? If so, what is the source of that list? Does it reference a range? Can you provide the exact formula from the RefEdit (Source:) box in the Data Validation window? – Valon Miller Sep 25 '18 at 15:03
  • B3 has a source of hidden cells in a sheet called "DATA_PROPERTY". Until I un-hid the sheet, cell B3 would not indicate the source. Now I know B3 is the list of properties in DATA_PROPERTY which is a dynamic list based on Database connection SQL view. I was able to successfully query the number of rows I need, and all I have to do is subtract 1 from LastRow to get the total # of properties. – Kevin Devine Sep 25 '18 at 15:31

1 Answers1

0

To find last row, also at this link: Error in finding last used cell in VBA

With ThisWorkbook.Worksheets("sheet name")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' if col is "A"
End With
Kubie
  • 1,551
  • 3
  • 12
  • 23