VBA Test on Array - Find a value

[christopher ward]

Hi Experts

A question - I want to search an array called lastknowngood to find out if a
value exits within it. The arary is a Variant data type but try as I may I
cannot find out how to do this easily. If you help many thanks in advance.

Sheets("Master").Select
lastknowngood = Range("j11:j75").Value
sFile = Dir(sPath & sExtension)

so I want to test if the sFile variable exists in the lastknowngood array at
any point?

regards and hoping someone has a quick fix or idea

 

[Jacob Skaria]

Dim varRange As Range

Set varRange = Sheets("Master").Range("J11:J75")
Set varFound = varRange.Find(lastknowngood)
If Not varFound Is Nothing Then
'Found
sFile = Dir(sPath & sExtension)
End If

If this post helps click Yes

 

[Jacob Skaria]

I hope the variable to be searched is sFile. In which case you need to find
for sFile...

Dim varRange As Range
Dim varFound As Variant

sFile = "<search string>"

Set varRange = Sheets("Master").Range("J11:J75")
Set varFound = varRange.Find(sFile)
If Not varFound Is Nothing Then
Msgbox sFile & "is in range"
Else
Msgbox sFile & "not in range"
End If

 

[christopher ward]

Thanks Jacob - worked a treat
--
C Ward

Dim varRange As Range

Set varRange = Sheets("Master").Range("J11:J75")
Set varFound = varRange.Find(lastknowngood)
If Not varFound Is Nothing Then
'Found
sFile = Dir(sPath & sExtension)
End If

If this post helps click Yes