Question about using FIND in macros

[cooldyood]

I have 2 questions about using the Find function in macos.

1) My macro uses Find to search for text and highlights the cell containing the text. If the text is not found, it throws an error. How can I avoid the error and just continue with the macro? There's probably an IF clause to be used.

2) If the text is found, it then activates the cell next to it, and populates it with a value from another tab. Currently, the cell location in the other tab is hardcoded but I'd like to use the find function during referencing that tab. Is this possible? For ex: instead of saying Range("A1") = Sheet1!("D1"), I want to say Range("A1") = Sheet1!<find text "abc" in column 3 and get the value in the cell next to it>

As always, thanks a ton!

[Dave Peterson]

#1.

Dim FoundCell as Range
with worksheets("Sheet1")
set foundcell = .cells.find(yourfindstatementhere, ....)
end with
if foundcell is nothing then
'do nothing
else
'do the work
end if


#2. You want to find something on sheet1 and something else in Sheet2 column C?

Dim FoundCell1 as range
dim FoundCell2 as range

with worksheets("Sheet1")
set foundcell1 = .cells.find(yourfindstatementhere, ....)
end with

if foundcell1 is nothing then
'do nothing
msgbox "not found on sheet2"
else
'do the work
with worksheets("sheet2").range("C:C")
set foundcell2 = .cells.find(yourotherfindhere, ...)
end with

if foundcell2 is nothing then
msgbox "not found on sheet2"
else
foundcell1.value = foundcell2.offset(0,1).value
end if
end if

But I'm not sure I really understand.

cooldyood wrote:
>
> I have 2 questions about using the Find function in macos.
>
> 1) My macro uses Find to search for text and highlights the cell
> containing the text. If the text is not found, it throws an error. How
> can I avoid the error and just continue with the macro? There's
> probably an IF clause to be used.
>
> 2) If the text is found, it then activates the cell next to it, and
> populates it with a value from another tab. Currently, the cell
> location in the other tab is hardcoded but I'd like to use the find
> function during referencing that tab. Is this possible? For ex: instead
> of saying Range("A1") = Sheet1!("D1"), I want to say Range("A1") =
> Sheet1!<find text "abc" in column 3 and get the value in the cell next
> to it>
>
> As always, thanks a ton!
>
> --
> cooldyood
> ------------------------------------------------------------------------
> cooldyood's Profile: http://www.excelforum.com/member.php...o&userid=35611
> View this thread: http://www.excelforum.com/showthread...hreadid=569245

--

Dave Peterson

[cooldyood]

Dave, I think you answered both Qs but I'll try them tomorrow and let you know.