VBA Test on Array - Find a value
 

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

--
C Ward

VBA Test on Array - Find a value
 

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


"christopher ward" wrote:

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

--
C Ward

VBA Test on Array - Find a value
 

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

--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"christopher ward" wrote:

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

--
C Ward

VBA Test on Array - Find a value
 

Thanks Jacob - worked a treat
--
C Ward


"Jacob Skaria" wrote:

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


"christopher ward" wrote:

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

--
C Ward