Finding a cell on a workbook that I have opened

[Pasty]

Hi there I am trying to get my code to let them decide if they want to change
the control (which is linked from another workbook) if they say yes it will
open up the other workbook (which works fine) but then I want it to copy the
information from the previous workbook and locate it on the one that has
opened the code I am using is this - does anyone have any pointers?


Sub goToSoa()

Dim soaValue As String

frmYesNo2.Show
If frmYesNo2.rdNo = True Then Exit Sub

If frmYesNo2.rdYes = True Then
soaValue = ActiveCell.Copy
Workbooks.Open Filename:= _
"http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/Statement of Applicability.xls"

Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Cells.FindNext(After:=ActiveCell).Activate

End If

End Sub

 

[Bob Phillips]

Rather than activate through the Find, I find it better to set a range
variable, like so

Set oCell = Cells.Find(What:=soaValue, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False

and then test if it was found

If Not oCell Is Nothing

If you don't find it, then you don't do the FindNext. If you do, you have to
make sure that you don't then just find the same one, VBA Help on Find shows
how.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pasty]

Hi,

I tried putting this bit in but it comes up with "Compile Area: Variable not
defined and goes to the Set oCell bit so i set the variable as a range which
worked but it is not finding the relevant cell.

 

[Bob Phillips]

So that suggests that the value is not there. Check what is in soaValue, and
the target worksheet.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pasty]

Hi I did this by getting it to print the value in to a cell offset from the
activecell and it is printing the text I want it to search for on the other
sheet.