Excel 2016 - VBA: Transform values in column into array

alpha2007

New Member
Joined
Jun 20, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hello!

Unfortunately, I have not worked with VBA arrays so far, and thus I don't know of how to transfer values in a column to an array. I hope someone can help me find a solution for it

I have a variable list of URLs in column (("A1:A" & lastRow)) of worksheet "sheet1" wherêby
lastRow = .Cells(.rows.Count, "A").End(xlUp).Row

The array I need to use in a VBA Sub is as following:

VBA Code:
urls = Array("https://www.bizbuysell.com/Business-Opportunity/covid-friendly-commercial-cleaning-est-30-years-100k-net/1753433/?d=L2Zsb3JpZGEvaGlsbHNib3JvdWdoLWNvdW50eS1idXNpbmVzc2VzLWZvci1zYWxlLzI/cT1hVEk5T0RFc01qQXNNekFzTnpnbWJtRndQV1UlM0Q=", _
"https://www.bizbuysell.com/Business-Opportunity/Established-Cleaning-Business-Tampa-St-Pete/1849521/?utm_source=bizbuysell&utm_medium=emailsite&utm_campaign=shtmlbot&utm_content=headline")


In the above example VBA array urls, there are only two URLs and I need all URLs of column A in "sheet1" to be in that specific array.

Thank you for helping me!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I think this is one way provided that A1 has a header. If not, adjust accordingly:

Code:
Sub Alpha2007()
Dim lr As Long, url() As String, i As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row - 2
ReDim url(lr)
For i = 0 To lr
 url(i) = Sheets("Sheet1").Cells(i + 2, "A")
Next i
End Sub
 
Upvote 0
Solution
Hi,​
with a single codeline : urls = [Sheet1!A1].CurrentRegion.Columns(1).Value2 …​
 
Upvote 0
Thank you kweaver and Marc L

Question for Marc L:

Code:
urls = [Sheet1!A1].CurrentRegion.Columns(1).Value2

Is urls an array as in my example?

I ask because each individual url in the array urls is needed for the loop in following code, and the loop does not work


VBA Code:
    Dim url As Long, results() As Variant
 
    ReDim results(1 To UBound(urls) + 1, 1 To 21) 

    With http
 
        For url = LBound(urls) To UBound(urls)   'loop url list
 
            .Open "Get", urls(url), False
            .setRequestHeader "User-Agent", "Mozilla/5.0"
            .send
 
            html.body.innerHTML = .responseText
 
            Dim currentDetailedInformation As Scripting.Dictionary
 
            Set currentDetailedInformation = GetCurrentDetailedInfo(html)
 
            AddCurrentDetailedInfoToResults results, currentDetailedInformation, url, urls(url), html
        Next
 
    End With


Thank you so much for looking into it again!
 
Upvote 0
Each element of url is part of the array. Try checking url(0), or url(1), etc.
 
Upvote 0
Question for Marc L:
As the source data is a column so the first element of the array is urls(1, 1), the second is urls(2, 1), … as you can check in the Locals VBE window !​
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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
Back
Top