CODEWITHSUNDEEP
vbaexcelvbscript
marija
marija

Reputation: 101

VBA reading two columns inside Excel through loop

I have this code:

Dim cityList() 

Set objExcel = CreateObject("Excel.Application") 

Set objWorkbook = objExcel.Workbooks.Open("C:\Users\xyz\Desktop\Test\Cities.xlsx") 
objExcel.Visible = True 

i = 1 
x = 0 

Do Until objExcel.Cells(i, 1).Value = "" 
ReDim Preserve cityList(x) 
cityList(x) = objExcel.Cells(i, 1).Value 
i = i + 1 
x = x + 1 

Loop 

objExcel.Quit

I have problem with making this like array with two conditions. My excel contains two columns: City Date

Mumbai  22.04.2016
Delhi   23.05.2016
Goa     24.06.2016

I have managed to read column City and read them one by one, but I need also to read Date and condition is like:

For City = "Mumbai" and Date = "22.04.2016" do something....

I am not very familiar with VBA, but script must be written.

Is anyone can help me how to add also Date inside so he can read both columns?

Thanks upfront

Upvotes: 1

Views: 529

Answers (4)

marija
marija

Reputation: 101

I have found solution:

Dim cityList() 

Set objExcel = CreateObject("Excel.Application") 
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\xyz\Desktop\Test\Cities.xlsx") 
objExcel.Visible = True 
i = 1
x = 0 
y = 0

Do Until objExcel.Cells(i, 1).Value = "" 
ReDim Preserve cityList(x)
ReDim Preserve cityDate(y)
cityList(x) = objExcel.Cells(i, 1).Value
cityDate(y) = objExcel.Cells(i, 2).Value

i = i + 1 

x = x + 1 
y = y + 1

Loop 

objExcel.Quit

j = 0

For Each city in cityList

tab = "City"
        datetime = cityDate(j)
        j = j + 1
        count = count + 1
        .....
Next

Maybe is not best solution, but is working! Thank you all on suggestions and help!

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

Why all those loops and redim ?

Dim cityList as variant, lastRow as Long
lastRow = range("A" & rows.count).End(xlUp).Row
cityList = range("A1:B" & lastrow)

Much faster to write and execute

Upvotes: 1

msinfo
msinfo

Reputation: 1175

' in your code you can capture date values using offset property

ReDim Preserve cityList(x) 
cityList(x) = objExcel.Cells(i, 1).Value 
' = objExcel.Cells(i, 1).Offset(0, 1).Value  ' offset property to capture date 
' As Dave said either you can use Dictionary or 2D arrays to store date. . .
i = i + 1

Upvotes: 0

Dave
Dave

Reputation: 4356

Here's a quick rewrite of your existing code achieving what I think you're after:

Dim cityList(1,0)
Set objExcel = CreateObject("Excel.Application") 
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\xyz\Desktop\Test\Cities.xlsx") 
objExcel.Visible = True 
Set oSheet = objWorkbook.Worksheets("Sheet1")   ' set the correct sheet name here
iLastRow = oSheet.Cells(oSheet.Rows.Count, 1).End(xlUp).Row ' determine the last row to look at

For iRow = 1 To iLastRow
    ReDim Preserve cityList(1,iRow-1)                       ' within the loop, extend the array by 1
    cityList(0,UBound(cityList)) = oSheet.Cells(iRow,1)     ' Set the city value
    cityList(1,UBound(cityList)) = oSheet.Cells(iRow,2)     ' Set the date value
Next

' Now you have all the data you can iterate over it like so:
For iLoop = 0 To UBound(cityList,2)
    If cityList(0, iLoop) = "Mumbai" And cityList(1, iLoop) = "22.04.2016" Then
        ' Do whatever you needed to do
    End If
Next

objExcel.Quit

Upvotes: 2

Related Questions