0

I have 2 arrays and a bunch of data. The arrays are set as ranges...

Dim Ar1() As Variant, Ar2() As Variant
Ar2= Workbooks("workbook2.xlsx").Sheets("Sheet2").Range("A2:A" & LastRow).Value
Ar1= Workbooks("workbook2.xlsx").Sheets("Sheet1").Range("A2:A" & LastRow).Value

Trying to find something like...

If workbooks("workbook1.xlsx").sheets("Sheet1").range("A" & LastRowH).value = Ar1 Then
do an action
Else
do action 2
End If

and a second If...Then for Ar2. This keeps erroring out on me with the Datatype error. Please let me know if there is a way I can do this. Thanks!!!!

edit: if I check a range and apply the actions to those that match, then that would be more effecient - Thanks!

Community
  • 1
  • 1
AJames
  • 81
  • 1
  • 1
  • 10
  • You can use a UDF for this - there are a couple good ones at this question [here](https://stackoverflow.com/questions/38267950/check-if-a-value-is-in-an-array-or-not-with-excel-vba) – dwirony May 10 '18 at 14:19

2 Answers2

0

I think this is what you're looking for - this UDF returns True if the value is found in the array:

Sub Test()

If IsInArray(Workbooks("workbook1.xlsx").Sheets("Sheet1").Range("A" & LastRowH).Value, Ar1) = True Then
    'do something!
Else
    'do something else!
End If

End Sub
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function
dwirony
  • 5,487
  • 3
  • 21
  • 43
  • The only caveat, which is not a problem for the OP but may be for future readers, is that this will only work for one dimensional arrays. – Scott Craner May 10 '18 at 14:28
  • If `arr` is a `Range` instead of a `Variant`, then you can use `IsInArray = Not (arr.Find(What:=stringToBeFound, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing)` for 2D ranges – Chronocidal May 10 '18 at 14:47
  • the above is great. as a followup, is there a way to vlookup an array and sum the values, or does each datapoint need its own vlookup? – AJames May 10 '18 at 19:35
0

To answer my own comment, yes, yes there is. Found this on the intertube-intranetwork-wifi-google :D

http://www.vbaexpress.com/forum/showthread.php?55832-vlookup-array-values

AJames
  • 81
  • 1
  • 1
  • 10