using find with variant?

[Don S]

How do you use find statement to find variant? My code:

Activecell.Value = varval
On Error Resume Next
With Sheets("Table").Columns("A:A")
Set c = .Find(What:=varval, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If Not c Is Nothing Then

Question? Dose varval need to be in quotes? parenthethes? varval is a string.

[Jim Thomlinson]

The only problem that I see is that you never set the value of varval. The line
Activecell.Value = varval
sets the active cell to varval, but where is varval set?

--
HTH...

Jim Thomlinson


"Don S" wrote:

> How do you use find statement to find variant? My code:
>
> Activecell.Value = varval
> On Error Resume Next
> With Sheets("Table").Columns("A:A")
> Set c = .Find(What:=varval, After:=ActiveCell, LookIn:=xlValues, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False)
> On Error GoTo 0
> If Not c Is Nothing Then
>
> Question? Dose varval need to be in quotes? parenthethes? varval is a string.

[Don S]

This code is inside a loop that changes the "activecell". varval is set to
the value of activecell; i.e it changes throughtout the loop. It could be
"car" on loop 1 and "boat" on loop , etc. I want to use the variant to Seach
sheet Table to pick up other values associated to "varval" from the Table.


"Jim Thomlinson" wrote:

> The only problem that I see is that you never set the value of varval. The line
> Activecell.Value = varval
> sets the active cell to varval, but where is varval set?
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Don S" wrote:
>
> > How do you use find statement to find variant? My code:
> >
> > Activecell.Value = varval
> > On Error Resume Next
> > With Sheets("Table").Columns("A:A")
> > Set c = .Find(What:=varval, After:=ActiveCell, LookIn:=xlValues, _
> > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False)
> > On Error GoTo 0
> > If Not c Is Nothing Then
> >
> > Question? Dose varval need to be in quotes? parenthethes? varval is a string.

[Jim Thomlinson]

Where is varval set. I would understand if you had the line
varval = activecell.value
I just wanted to confirm that varval has a value before you start your find.
--
HTH...

Jim Thomlinson


"Don S" wrote:

> This code is inside a loop that changes the "activecell". varval is set to
> the value of activecell; i.e it changes throughtout the loop. It could be
> "car" on loop 1 and "boat" on loop , etc. I want to use the variant to Seach
> sheet Table to pick up other values associated to "varval" from the Table.
>
>
> "Jim Thomlinson" wrote:
>
> > The only problem that I see is that you never set the value of varval. The line
> > Activecell.Value = varval
> > sets the active cell to varval, but where is varval set?
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Don S" wrote:
> >
> > > How do you use find statement to find variant? My code:
> > >
> > > Activecell.Value = varval
> > > On Error Resume Next
> > > With Sheets("Table").Columns("A:A")
> > > Set c = .Find(What:=varval, After:=ActiveCell, LookIn:=xlValues, _
> > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > > MatchCase:=False)
> > > On Error GoTo 0
> > > If Not c Is Nothing Then
> > >
> > > Question? Dose varval need to be in quotes? parenthethes? varval is a string.

[Don S]

Actually I do have that code earlier in my loop. Also, I have figured out my
problem. The find was incorrect because I did not activate a cell on sheet
Table and the search could not search "After:=Activecell". Once I corrected
this my origanl code works fine. THANKS FOR THE HELP.....


"Jim Thomlinson" wrote:

> Where is varval set. I would understand if you had the line
> varval = activecell.value
> I just wanted to confirm that varval has a value before you start your find.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Don S" wrote:
>
> > This code is inside a loop that changes the "activecell". varval is set to
> > the value of activecell; i.e it changes throughtout the loop. It could be
> > "car" on loop 1 and "boat" on loop , etc. I want to use the variant to Seach
> > sheet Table to pick up other values associated to "varval" from the Table.
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > The only problem that I see is that you never set the value of varval. The line
> > > Activecell.Value = varval
> > > sets the active cell to varval, but where is varval set?
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Don S" wrote:
> > >
> > > > How do you use find statement to find variant? My code:
> > > >
> > > > Activecell.Value = varval
> > > > On Error Resume Next
> > > > With Sheets("Table").Columns("A:A")
> > > > Set c = .Find(What:=varval, After:=ActiveCell, LookIn:=xlValues, _
> > > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > > > MatchCase:=False)
> > > > On Error GoTo 0
> > > > If Not c Is Nothing Then
> > > >
> > > > Question? Dose varval need to be in quotes? parenthethes? varval is a string.