Arrays, Ranges and unknown row count

yellowc

New Member
Joined
Feb 27, 2018
Messages
10
I've created a macro that takes a list of addresses and returns the distance. The problem I'm having is that the macro is static, but I don't know how many rows of addresses I'm going to have. I've made it so that the data is returned in an array but once again when populating the column for distance the range is statically set. How can I make so that I can put in any number of addresses and loop through quickly?

Code below

Code:
Public Sub getdirections()


Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String, c As Range, rng As Range, xmlresponse As New DOMDocument60
Dim myarray() As Variant, x As Long, d As Range


Set rng = Range("c2:c26")




'single substitution to get one location will work on looping in a bit
For Each c In rng
    myurl = "http://www.mapquestapi.com/directions/v2/route?key=key & c & "&outFormat=xml&ambiguities=ignore&routeType=shortest"
    'Debug.Print myurl
    'sending the request
      xmlhttp.Open "GET", myurl, False
      xmlhttp.Send
      xmlresponse.LoadXML (xmlhttp.ResponseText)
      'once I got XML returns instead of JSON returns all started to work properly
     Set stats = xmlresponse.SelectNodes("//response/route/distance")
     'Sheets(1).Range("e2").Value = stats(0).Text
     ReDim Preserve myarray(x)
     myarray(x) = stats(0).Text
     x = x + 1
    'Range("d2 & rng").Value = stats(0).Text
     If c = "" Then
    Exit For
    End If
Next




For x = LBound(myarray) To UBound(myarray)
    Debug.Print myarray(x)
 Next x


Range("d2:d26").Value = Application.WorksheetFunction.Transpose(myarray)




End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Does any of this help?

firstrow = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
firstcol = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
 

yellowc

New Member
Joined
Feb 27, 2018
Messages
10
Does any of this help?

firstrow = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
firstcol = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column


Yes I can get the count of how many rows but I'm sorta lost as how to implement it in the for each loop I have going on.

Code:
count = 1
for each c in rng 
do
"blah blah blah"
count = count + 1
until count == lastrow ???

and then later for transposing the function

Code:
range(???).value = Application.WorksheetFunction.Transpose(myarray) ???
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Maybe more specifically


Code:
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
rngstr = "c2:c" & lastrow
Set rng = Range(rngstr)
 

yellowc

New Member
Joined
Feb 27, 2018
Messages
10
OOOOO, that makes so much sense. And it worked perfectly. I took what you gave me and was able to get the array part to function as well. AWESOME. Now are there any caveats for this? Like it I have a cell that I wrote in and deleted, do I have to save first before getting the correct row count? Appreciate it so much
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
OOOOO, that makes so much sense. And it worked perfectly. I took what you gave me and was able to get the array part to function as well. AWESOME. Now are there any caveats for this? Like it I have a cell that I wrote in and deleted, do I have to save first before getting the correct row count? Appreciate it so much


It counts to the lastrow at the moment of the running. You don't have to save before, it counts the cells at the macro run. Glad it worked.
 

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top