after a .Find command, offset the output 2 rows down.

[Ironhydroxide]

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

 

[royUK]

What have you declared J as? If it is a Range then it should work:


Code

 

[Ironhydroxide]

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.

 

[Dave Peterson]

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]

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.

 

[Ironhydroxide]

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.

 

[Dave Peterson]

I don't have another guess.

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.

 

[Ironhydroxide]

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)