CODEWITHSUNDEEP
arraysexcelvba
Clauric
Clauric

Reputation: 1886

Array not returning an answer in For loop

I have an array that takes in values from a single row of a spreadsheet (1 row, 185 columns). I then want to loop through the array, looking for a match between the values in the array, and values in a particular cell.

However, every time I run the code, it says it finds a match, but does not return a value to the cell.

The relevant lines of the code are:


Dim qCountry()

Worksheets("Data").Activate

    qCountry = Range("A1:GC1").Value

    For i = 1 To 185
          If Cells(aRow, bCol) <> vbNullString Then
              Exit For
          ElseIf InStr(1, Cells(aRow, 4), "*" & qCountry(i) & "*") = 1 Then
              Cells(aRow, bCol) = qCountry(i)
          End If
    Next i

A screenshot of my array:

Array

Upvotes: 1

Views: 55

Answers (2)

Slai
Slai

Reputation: 22866

Values can be accessed as shown in the screenshot :

Cells(aRow, bCol) = qCountry(1, i)

Upvotes: 1

Louis
Louis

Reputation: 3632

Just define qCountry as a normal Variant variable, like this:

Dim qCountry as Variant

This will get rid of one extra dimension, but you still have a multi-dimension array.

If you want to deal with an array of single dimension, you can use the Application.Transpose() Function:

qCountry = Application.Transpose(Range("A1:GC1").Value)

But since you have data in a row, you need to do it twice:

qCountry = Application.Transpose(Application.Transpose(Range("A1:GC1").Value))

At this point your code will work:

Dim qCountry
Worksheets("Data").Activate
qCountry = Application.Transpose(Application.Transpose(Range("A1:GC1").Value))

For i = 1 To 185
      If Cells(aRow, bCol) <> vbNullString Then
          Exit For
      ElseIf InStr(1, Cells(aRow, 4), "*" & qCountry(i) & "*") = 1 Then
          Cells(aRow, bCol) = qCountry(i)
      End If
Next i

Hope this helps.

Upvotes: 2

Related Questions