VBA Array with different length

[adnan1975]

Hi,
I am trying to make the following code work in excel VBA.

Sub TESTCASE()
Dim i, x, lr As Long
Dim a(), b(), c(), d(), e() As Variant

Set MT = Sheets("Mortgage")
With MT
lr = .Range("A" & Rows.Count).End(3).Row
a = .Range("J2:J" & lr).Value
c = .Range("K2:K" & lr).Value
b = .Range("M2:M9").Value
.Range("K2:K" & lr).ClearContents


For i = 1 To UBound(a)
If UCase(a(i, 1)) = UCase(b(i, 1)) Then c(i, 1) = "APOD"

Next
    .Range("K2").Resize(UBound(c)).Value = c

End With
End Sub

I can understand length of b() is different then a() that is why my code gives me an error but I dont know how to fix it. Any help is very much appriciated.
Thanks,

 

[DanteAmor]

If you are only going to read into array 'b' from 2 to 9, then the for loop should only go to ubound(b).

But if what you really want is to compare each data item in array 'a' with each data item in array 'b', then try the following.
Note: I properly declared each variable:

Sub TESTCASE()
  Dim MT As Worksheet
  Dim i As Long, lr As Long
  Dim a() As Variant, b() As Variant, c() As Variant
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  Set MT = Sheets("Mortgage")
  dic.comparemode = vbTextCompare
    
  With MT
    lr = .Range("A" & Rows.Count).End(3).Row
    a = .Range("J2:J" & lr).Value
    c = .Range("K2:K" & lr).Value
    b = .Range("M2:M9").Value
    .Range("K2:K" & lr).ClearContents
    For i = 1 To UBound(b, 1)
      dic(b(i, 1)) = Empty
    Next
    
    For i = 1 To UBound(a)
      If dic.exists(a(i, 1)) Then c(i, 1) = "APOD"
    Next
    
    .Range("K2").Resize(UBound(c)).Value = c
  End With
End Sub

Review the following example to see how to properly declare variables.

Check the following example to know the use of Dictionary

---

 

[adnan1975]

If you are only going to read into array 'b' from 2 to 9, then the for loop should only go to ubound(b).

But if what you really want is to compare each data item in array 'a' with each data item in array 'b', then try the following.
Note: I properly declared each variable:

Sub TESTCASE()
  Dim MT As Worksheet
  Dim i As Long, lr As Long
  Dim a() As Variant, b() As Variant, c() As Variant
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
  Set MT = Sheets("Mortgage")
  dic.comparemode = vbTextCompare
   
  With MT
    lr = .Range("A" & Rows.Count).End(3).Row
    a = .Range("J2:J" & lr).Value
    c = .Range("K2:K" & lr).Value
    b = .Range("M2:M9").Value
    .Range("K2:K" & lr).ClearContents
    For i = 1 To UBound(b, 1)
      dic(b(i, 1)) = Empty
    Next
   
    For i = 1 To UBound(a)
      If dic.exists(a(i, 1)) Then c(i, 1) = "APOD"
    Next
   
    .Range("K2").Resize(UBound(c)).Value = c
  End With
End Sub

Review the following example to see how to properly declare variables.

Check the following example to know the use of Dictionary

---

Thank you so much it worked. Is there any way to resolve it without creating Scripting.Dictionary?

 

[DanteAmor]

Is there any way to resolve it without creating Scripting.Dictionary?

This could be a way:

Sub TESTCASE_2()
  Dim MT As Worksheet
  Dim i As Long, lr As Long
  Dim a() As Variant, c() As Variant
  Dim f As Range
  
  Set MT = Sheets("Mortgage")
    
  With MT
    lr = .Range("A" & Rows.Count).End(3).Row
    a = .Range("J2:J" & lr).Value
    c = .Range("K2:K" & lr).Value
    .Range("K2:K" & lr).ClearContents
    
    For i = 1 To UBound(a)
      Set f = .Range("M2:M9").Find(a(i, 1), , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then c(i, 1) = "APOD"
    Next
    
    .Range("K2").Resize(UBound(c)).Value = c
  End With
End Sub

 

[adnan1975]

This could be a way:

Sub TESTCASE_2()
  Dim MT As Worksheet
  Dim i As Long, lr As Long
  Dim a() As Variant, c() As Variant
  Dim f As Range
 
  Set MT = Sheets("Mortgage")
   
  With MT
    lr = .Range("A" & Rows.Count).End(3).Row
    a = .Range("J2:J" & lr).Value
    c = .Range("K2:K" & lr).Value
    .Range("K2:K" & lr).ClearContents
   
    For i = 1 To UBound(a)
      Set f = .Range("M2:M9").Find(a(i, 1), , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then c(i, 1) = "APOD"
    Next
   
    .Range("K2").Resize(UBound(c)).Value = c
  End With
End Sub

Thank you again.

 

[adnan1975]

Once again thank you so so much for all the help. I have another question from the same code. Instead of "APOD" I want to reference another range (N2:N9) with different names i.e. APOD, BPOD, CPOD etc. which refer to different values in M2:M9. How can I solve it.
Sorry my VBA skills are very very basic. I always appreciate your help.

 

[DanteAmor]

Sorry my VBA skills are very very basic.

Don't worry, you don't have to develop the code, we can help you here.

What you do know is how your data is and what you need as a result, that is something I do not know.

If you post here your examples of what you have in your sheet, what you want to look for, where you are going to look for it, the result you want to obtain and where you want to put the result, it could help you.

Paste the data from your sheet here, it does not necessarily have to be real data, you can replace it with generic data, briefly explain the final objective.

NOTE XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[adnan1975]

Currently, my if condition solves for "APOD" so everything in Column K is shows as "APOD". However, I want to use reference data from Column N. So for example, if a cell in Column J is "NSS" then cell in Column K is "APOD", but if cell in Column J is APS8 then K should be "BPOD"
I hope it makes sense. I am using the following code

Sub TESTCASE1()
  Dim MT As Worksheet
  Dim i As Long, lr As Long
  Dim a() As Variant, b() As Variant, c() As Variant
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")
  Set MT = Sheets("Mortgage")
  dic.comparemode = vbTextCompare
    
  With MT
    lr = .Range("A" & Rows.Count).End(3).Row
    a = .Range("J2:J" & lr).Value
    c = .Range("K2:K" & lr).Value
    b = .Range("M2:M9").Value
    .Range("K2:K" & lr).ClearContents
    
    For i = 1 To UBound(b, 1)
      dic(b(i, 1)) = Empty
    Next
    
    For i = 1 To UBound(a)
      If dic.exists(a(i, 1)) Then c(i, 1) = "APOD"
    Next
    
    .Range("K2").Resize(UBound(c)).Value = c
  End With
End Sub

 

[DanteAmor]

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data.

Try this:

Sub testcase_3()
  Dim MT As Worksheet
  Set MT = Sheets("Mortgage")
  With MT.Range("K2:K" & MT.Range("J" & Rows.Count).End(3).Row)
    .Formula = "=VLOOKUP(J2,M:N,2,0)"
    .Value = .Value
  End With
End Sub

 

[adnan1975]

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data.

Try this:

Sub testcase_3()
  Dim MT As Worksheet
  Set MT = Sheets("Mortgage")
  With MT.Range("K2:K" & MT.Range("J" & Rows.Count).End(3).Row)
    .Formula = "=VLOOKUP(J2,M:N,2,0)"
    .Value = .Value
  End With
End Sub

Thank you so much. It worked. In the future I will try to upload the spreadsheet.