How to use an array to get a value from another array?

Question

I am trying to search through these rows in my excel sheet and look to see if the cell says one of the things in my array. If it does match one of my values in my array, I want it to go into the other array to the correct number, then pull that value and put in a different cell.

So I want it finding the word in my first array, figuring out where that is in the array, then taking the corresponding value from Array2 and putting that value in the wanted cell. Here is what I have so far but I am getting an error message on

If ws1.Cells(lngRow, "F").Value = "" And _
            InStr(1, LCase(Range("J" & lngRow)), LCase(v)) <> 0 Then

It gives me a "Run-time error '424': Object required" Error. So I am not sure what I am doing wrong. Maybe I am not doing the code correctly at all but I thought it was something like this.

The arrays may not have the same amount of info in them right now because I took info out to make it smaller but my real one has the same number of values.

Dim lngRow As Long
Dim lngRows As Long
Dim arr As Variant, v As Variant
'
'    This function will search for all Transfers.
'    If the reason for action is ERROR, it will leave it. If it part of the list that needs changed, then it will make it blank or ""
'    Otherwise it will leave it as it is.
'
'
ActiveSheet.AutoFilterMode = False


arr = Array("Atlanta", "Chicago", "Crown Point", _
    "Dallas", "DC", "Equipment Company", "Denver", _
    "Detroit", "Home Office", "Houston", "Kansas", _
    "Las Vegas", "Louisville")

arr2 = Array("US Distribution", "US Products", "US Distribution", "US Distribution", "US Distribution", "US Distribution", "Equipment", "US Distribution", "US Products", "US Distribution", "Corporate", "US Distribution", "US Products", "US Distribution", "US Distribution", "US Distribution", "US Products", "US Products")

lngRows = Range("C" & Rows.Count).End(xlUp).Row


For lngRow = lngRows To 2 Step -1

    For Each v In arr
        If ws1.Cells(lngRow, "F").Value = "" And _
            InStr(1, LCase(Range("J" & lngRow)), LCase(v)) <> 0 Then
            ws1.Cells(lngRow, "I").Value = arr2(v)
        End If
    Next v

Next

End Sub

EDITED CODE DOWN BELOW

Dim wb As Workbook
Dim ws1 As Worksheet
Dim FFwb As Workbook
Dim FFws As Worksheet

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets(1)

Dim lngRow As Long
Dim lngRows As Long
Dim arr As Variant, var As Variant
Dim arr2 As Variant
Dim locIdx As Variant

For locIdx = LBound(arr) To UBound(arr)
    var = arr(locIdx)
    For lngRow = lngRows To 2 Step -1
        For Each var In arr
            If ws1.Cells(lngRow, "F").Value = "" And _
                InStr(1, LCase(ws1.Cells(lngRow, "I").Value), LCase(var)) <> 0 Then
                ws1.Cells(lngRow, "H").Value = arr2(var)
            End If
        Next var
    Next lngRow
Next locIdx

THIS IS MY CODE THAT WORKS DOWN BELOW. It doesn't work if the one cell is #N/A, but they shouldn't be #N/A. I had to change what arr2 was pulling from in the last line to locIdx instead of var.

Dim locIdx As Variant


For lngRow = lngRows To 2 Step -1



    For locIdx = LBound(arr) To UBound(arr)
        var = arr(locIdx)
        If ws1.Cells(lngRow, "F").Value = "" And _
            InStr(1, ws1.Cells(lngRow, "I").Value, var) <> 0 Then
            ws1.Cells(lngRow, "H").Value = arr2(locIdx)
            'ElseIf LCase(ws1.Cells(lngRow, "A").Value) = "transfer ft<>pt" And _
                'InStr(1, LCase(Range("B" & lngRow)), LCase(v)) <> 0 Then
                'ws1.Cells(lngRow, "B").Value = ""
        End If
    'If (LCase(ws1.Cells(lngRow, "A").Value) = "transfer" And _
    '        InStr(1, LCase(Range("B" & lngRow)), LCase("err")) <> 0) Then
    '        ws1.Cells(lngRow, "B").Value = "Error"
    'End If

    Next locIdx

Next lngRow

Thanks for the help in advance!

Add Option Explicit at the top of the module. The error changes to Variable not declared (or something similar) - ws1 doesn't exist; you can't make these member calls on a Variant that doesn't contain a Worksheet object reference. You probably don't want to make member calls against a Variant anyway. Dim ws1 As Worksheet and Set it to whatever appropriate Worksheet instance. Also, fix any other compile errors, and always use Option Explicit in the future. Cheers! — Mathieu Guindon
– Mathieu Guindon, Commented Oct 23, 2017 at 21:10
Looks like you might want to exit for if you find a match. — user4039065
– user4039065, Commented Oct 23, 2017 at 21:25

Joffan · Accepted Answer · 2017-10-24 15:24:55Z

2

v is not a valid index to access arr2. You need a common index variable to reference from one array to the other.

You switch the method of referencing a cell, which is confusing. Better to stick to one method, e.g. using ws1.Cells and the .Value property explicitly. This may be the source of your error, it's hard to tell. I can't say that I like using letters for columns but it is allowed.

Dim locIdx As Long
For locIdx = LBound(arr) To UBound(arr)
    v = arr(locIdx)
    If ws1.Cells(lngRow, "F").Value = "" And _
        InStr(1, LCase(ws1.Cells(lngRow, "J").Value), LCase(v)) <> 0 Then
        ws1.Cells(lngRow, "I").Value = arr2(locIdx)
    End If
Next locIdx

You might face an ambiguity problem if your list of possible cities includes something that contains the consecutive letters "dc", given the string search used here.

edited Oct 24, 2017 at 15:24

answered Oct 23, 2017 at 22:08

Joffan

1,4801 gold badge14 silver badges19 bronze badges

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

6 Comments

Robillard Over a year ago

Alright so I am still getting an error. ws1 is declared as a worksheet. arr2 is declared as a variant. I am now getting an error 13 on the same line. It says it is a type mismatch right at the first If line.

Robillard Over a year ago

I have updated my code in my original post at the bottom. That is what I have now. Along with the actual arrays in the original.

Robillard Over a year ago

I figured it out! Posting answer at the bottom of my original question. I had to change what arr2 was pulling from in the end.

Robillard Over a year ago

ahhhhh I kept coming back to look at this because you have the correct code of arr2(locIdx) for the last line but when I had the code before it wasn't working. Then I remember putting that in my equation once I looked at it closely and figured out that v was not correct but I thought you had v there before. I thought I was going crazy or just miss typed that you had v there. But I checked your edit! I WASN"T CRAZY! You did have v there! hahaha at least other people will see multiple correct versions of it now.

Joffan Over a year ago

Apologies @Robillard , there was no intent to misrepresent but that was what I always meant by the first line of my answer. Sorry if the code edit confused you.

|

Robillard · Accepted Answer · 2017-10-24 14:16:49Z

This is the fixed code that ended up working. Thanks for eveyones help.

Dim locIdx As Variant


For lngRow = lngRows To 2 Step -1



    For locIdx = LBound(arr) To UBound(arr)
        var = arr(locIdx)
        If ws1.Cells(lngRow, "F").Value = "" And _
            InStr(1, ws1.Cells(lngRow, "I").Value, var) <> 0 Then
            ws1.Cells(lngRow, "H").Value = arr2(locIdx)
            'ElseIf LCase(ws1.Cells(lngRow, "A").Value) = "transfer ft<>pt" And _
                'InStr(1, LCase(Range("B" & lngRow)), LCase(v)) <> 0 Then
                'ws1.Cells(lngRow, "B").Value = ""
        End If
    'If (LCase(ws1.Cells(lngRow, "A").Value) = "transfer" And _
    '        InStr(1, LCase(Range("B" & lngRow)), LCase("err")) <> 0) Then
    '        ws1.Cells(lngRow, "B").Value = "Error"
    'End If

    Next locIdx

Next lngRow

Collectives™ on Stack Overflow

How to use an array to get a value from another array?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related