Integer makes Array return wrong value

Question

I defined the following Array:

Global Locations_Array() As String
ThisWorkbook.Worksheets("Locations").Activate
TotalRowsLocations = ActiveSheet.UsedRange.Rows.Count
ReDim Locations_Array(1 To TotalRowsLocations, 1 To 6) As String

According to this I expect the first row of the array to be row 1. I used this array to name new worksheets in my workbook, and that started at 1, and ended where it should.

However now I call again on this array with the following code:

Sub Get_Lat_Lng()
    Dim row As Integer
    Dim data_row As Integer

    Call Fill_Array_Locations

    totalrowsdata = ThisWorkbook.Worksheets("Locations").UsedRange.Rows.Count - 1

    TotalRowsDataLookup = ThisWorkbook.Worksheets(Locations_Array(row, 1)).UsedRange.Rows.Count

    For row = 2 To totalrowsdata
        ThisWorkbook.Worksheets(Locations_Array(row, 1)).Activate
        If (UCase(Locations_Array(row, 5))) = (UCase(ThisWorkbook.ActiveSheet(row, 7))) And (UCase(Locations_Array(row, 6))) = (UCase(Worksheet.ActiveSheet(row, 8))) Then

            For data_row = 1 To TotalRowsDataLookup
                If (UCase(Locations_Array(row, 2)) = UCase(ThisWorkbook.Worksheets(Locations_Array(data_row, 1)).Cells(data_row, 5))) Then
                   Cells(data_row, 7) = Locations_Array(row, 5)
                   Cells(data_row, 8) = Locations_Array(row, 6)
                   Exit For
                End If
            Next data_row

            Exit For
       End If
    Next row
End Sub

And it returns an "Run-time error '9': Subscript out of range" When I check for the values in this line of code row has the value of 0 due to being an Integer. Is there a way to make this Integer start at 1 instead of 0? I found the option to set :

Option Base 1

But this interferes with the rest of my code.

There is not an index 0 element of array but you use row as index when its value is equal to 0. Locations_Array(row, 1). You shouls assign row 1 or whatever is needed befor you use it as index. — kitap mitap
– kitap mitap, Commented Apr 28, 2015 at 10:11
Yes that is what I found out. But is there a way to make the Integer Row start at 1 instead of 0 — Luuklag
– Luuklag, Commented Apr 28, 2015 at 10:12
Row = 1. Option Base 1 has no effect on the initialisation of the numeric variables. They are equal to 0 by default in vba. option base determines first index of arrays whether they are equal to 0 or 1. Also, you have determined first indexes of your array is equal to 1. — kitap mitap
– kitap mitap, Commented Apr 28, 2015 at 10:14

stucharo · Accepted Answer · 2015-04-28 10:26:34Z

2

Defining an integer for Dim row As Integer withough assigning a value to it will default to row = 0. If you want a different value then you must assign the value you want to it before you use it.

Try:

Dim row As Integer
row = 1

Provided yo uhave data in each row of your Location_Array up to totalrowsdata then your For Loop will redefine row = 2 as the starting value and increment up to totalrowsdata.

The best way to understand the effect the change will have on your code is to try it and see. If it doesn't work as you think, try using F8 to step through each line and check the value of row with each loop.

edited Apr 28, 2015 at 10:26

answered Apr 28, 2015 at 10:18

stucharo

8655 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luuklag Over a year ago

I extended the code snippet in my OP further, to include the entire subroutine. I better posted it all at once, sorry for that. I don't see how this will go with using Next row within my loop. Thanks for your response though.

kitap mitap · Accepted Answer · 2015-04-28 10:30:46Z

Sub Get_Lat_Lng()
    Dim row As Integer
    Dim data_row As Integer

    Call Fill_Array_Locations

    totalrowsdata = ThisWorkbook.Worksheets("Locations").UsedRange.Rows.Count - 1

    Row = 1 'Row should be at least 1

    '"Row" should be at least 1 before here.
    TotalRowsDataLookup = ThisWorkbook.Worksheets(Locations_Array(row, 1)).UsedRange.Rows.Count

    For row = 2 To totalrowsdata
        ThisWorkbook.Worksheets(Locations_Array(row, 1)).Activate
        If (UCase(Locations_Array(row, 5))) = (UCase(ThisWorkbook.ActiveSheet(row, 7))) And (UCase(Locations_Array(row, 6))) = (UCase(Worksheet.ActiveSheet(row, 8))) Then

            For data_row = 1 To TotalRowsDataLookup
                If (UCase(Locations_Array(row, 2)) = UCase(ThisWorkbook.Worksheets(Locations_Array(data_row, 1)).Cells(data_row, 5))) Then
                   Cells(data_row, 7) = Locations_Array(row, 5)
                   Cells(data_row, 8) = Locations_Array(row, 6)
                   Exit For
                End If
            Next data_row

            Exit For
       End If
    Next row
End Sub

Collectives™ on Stack Overflow

Integer makes Array return wrong value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related