so i have some information in blocks, in a selection i want to find the
first cell with the name "Elder" in it then move down 2 rows and check if
this cell has "Elder" also, if the cell (after offset) doesn't have "Elder"
in it then i want to save the cell as an Object to display in a ListBox later
in the program.

my problem is when i try to do the If(j.Offset (2, 0) <> "Elder" Then... a
Run-time error '13 Type mismatch. pops up. is this type mismatch saying
that i cant do the .Offset on a variable that comes from a (Set j =
..Find("Elder",,,,xlByColumns)) command earlier in the script?

do i have to somehow change this variable (j) to a different type before i
can do a .Offset command on it?

thanks for any and all suggestions

Well, i dont know if a .Find function declares a variable as a range.

this is a portion of my code.

With Selection
Set j = .Find("Elder", , , , xlByColumns)
If Not j Is Nothing Then
Set comp1 = j.Offset(2, 0)
If j.Offset(2, 0) <> "Elder" Then
apmtAddress1 = j.Row
Set apmtcdn1 = j
apmtlist1 = j.Offset(2, 0)
Else
Set comp2 = j.Offset(2, 0)
Set j = .FindNext(j)
apmtlist1 = j.Offset(2, 0)
End If
................................(goes further with other If-thens)



even with putting the (.Value) in there after the .Offset(2, 0) it is
giving me the same error code.



"royUK" wrote:

>
> What have you declared J as? If it is a Range then it should work:
>
>
> Code:
> --------------------
> If j.offset(2,0).Value <> "Elder" Then 'etc
> --------------------
>
>
> --
> royUK
>
> Hope that helps, RoyUK
> For tips & examples visit my 'web site' (http://www.excel-it.com/)
> ------------------------------------------------------------------------
> royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47036
>
>

Find won't declare any variables.

You would use something like:

Option Explicit
Sub YourSubNameHere()
dim J as range
dim compl = range
...

with selection
set j = .find(....

==========
One of the things that can cause this error is that if that j.offset(2,0)
contains an error.

You could check it with something like:

if iserror(j.offset(2,0).value) then
'skip it
elseif lcase(j.offset(2,0).value) = lcase("Elder") then
'do the work.
end if

If you want to see what's in that cell for debugging purposes, add this before
the offending line:

with j.offset(2, 0)
msgbox .address & vblf & .text
end with

And you'll be able to see what that cell holds.

Ironhydroxide wrote:
>
> Well, i dont know if a .Find function declares a variable as a range.
>
> this is a portion of my code.
>
> With Selection
> Set j = .Find("Elder", , , , xlByColumns)
> If Not j Is Nothing Then
> Set comp1 = j.Offset(2, 0)
> If j.Offset(2, 0) <> "Elder" Then
> apmtAddress1 = j.Row
> Set apmtcdn1 = j
> apmtlist1 = j.Offset(2, 0)
> Else
> Set comp2 = j.Offset(2, 0)
> Set j = .FindNext(j)
> apmtlist1 = j.Offset(2, 0)
> End If
> ...............................(goes further with other If-thens)
>
> even with putting the (.Value) in there after the .Offset(2, 0) it is
> giving me the same error code.
>
> "royUK" wrote:
>
> >
> > What have you declared J as? If it is a Range then it should work:
> >
> >
> > Code:
> > --------------------
> > If j.offset(2,0).Value <> "Elder" Then 'etc
> > --------------------
> >
> >
> > --
> > royUK
> >
> > Hope that helps, RoyUK
> > For tips & examples visit my 'web site' (http://www.excel-it.com/)
> > ------------------------------------------------------------------------
> > royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
> > View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47036
> >
> >

--

Dave Peterson

I am still having problems with a Type mismatch Run-error. i have checked
the cell contents with the msgbox (.address & vblf § .Text) it returns $A$52
(which is correct) and Elder (which is also correct). thus my contents
should allow the if then to proceed without producing an error (the if then
statement produces the error)

i have also tried activating the cell and using activeCell to no avail.
this is driving me crazy.

Also i have defined the variable as range. and in debug when i hover over
the first portion of the if then statement it shows (j.Offset (4. 0).Value =
"Elder") which suggests that the if then should work.

I don't have another guess.

Ironhydroxide wrote:
>
> I am still having problems with a Type mismatch Run-error. i have checked
> the cell contents with the msgbox (.address & vblf § .Text) it returns $A$52
> (which is correct) and Elder (which is also correct). thus my contents
> should allow the if then to proceed without producing an error (the if then
> statement produces the error)
>
> i have also tried activating the cell and using activeCell to no avail.
> this is driving me crazy.

--

Dave Peterson

Well, i found that i had a couple other faults, the variable was being
changed between the time that i dimensioned it and the time it was being
used. but the answer you gave before was correct. thanks for your help. I
owe you one (i have no idea how, what, and who you really are, but i still
owe you one)