MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

problem with Find

  Asked By: Diane    Date: Oct 19    Category: MS Office    Views: 1250

I just want to check if which one of the following values

A = Array("JOY", "COKE", "FANTA", "SPRITE")

is in cell B29, assuming "1.50 LT COKE (LOCAL) 576 Bt"

I intend to use a For ....Next

For count = 0 To 3

If find A(bien) in Range("B29").Value) Then Exit For

Next count

But I get stuck there because it always exit for



5 Answers Found

Answer #1    Answered By: Ella Oliver     Answered On: Oct 19


If find  A(bien) in Range("B29").Value) Then Exit For


If find A(count) in Range("B29").Value) Then Exit For

Answer #2    Answered By: Alok Iit     Answered On: Oct 19

I mean it always exit For and tell me incorrectly that value "JOY" in cell

Answer #3    Answered By: Lucinda Hall     Answered On: Oct 19

I think we need to see more of the sub. With the bit of code you showed it
will always exit for. If it finds that one of the values in the array is in
B29 it will exit for. If it does not find  that it will still exit for when it
has exhaused all the values in the array (it falls out at the bottom of the

I would suggest that when sending code you use copy and paste, rather than
retyping. Otherwise we tell you all your typos, which you should have found
in VB editor anyhow.

Answer #4    Answered By: Rosie Brooks     Answered On: Oct 19

Very sorry about that

Sub testing()
On Error Resume Next

Dim count, description

Dim A As Variant

A = Array("JOY", "COKE", "FANTA", "SPRITE")

For count = 0 To 3
If (WorksheetFunction.Find(A(count), Range("B29").Value)
> 6) Then

Exit For

End If

Next count

description = Right(Range("Parameter!C6").Value, 3) & "-" &
Range("B24").Value & A(count)

End Sub
I want to extract the word JOY/COKE/FANTA/SPRITE

' B29 sometimes is 600ML JOY BOTTLE (1,530/PLT) / 1.50 LT COKE (LOCAL) 576
Bt / 1.50 LT FANTA (LOCAL) 576 Bt / 1.50 LT SPRITE (LOCAL) 576 Bt

I notice that JOY/COKE.... start at least from 7 in cell  B29 but I do not
think it's a good condition "WorksheetFunction.Find(A(count),
Range("B29").Value) > 6"

Answer #5    Answered By: Fabiano Ferrrari     Answered On: Oct 19

This should work

Sub testing()

Dim description
Dim A As Variant
Dim X
Dim J

A = Array("JOY", "COKE", "FANTA", "SPRITE")

For J = 0 To 3
If InStr(Range("B29").Value, A(J)) > 6 Then Exit For
Next J

description = Right(Range("Parameter!C6").Value, 3) & "-" & Range("B24").Value
& A(J)

End Sub

Didn't find what you were looking for? Find more on problem with Find Or get search suggestion and latest updates.