find and select a cell

N

Nigel

I am looking in column A for a specific Phrase and I want to make that the
active cell so I can perform an essbase zoom in, I am using this code but it
doesn't want to work any suggestions

For r = 1 To rngB.Rows.Count
If ActiveSheet.Cells(r, 1).Value = "Arc Accesories" Then
Set rngTemp = ActiveSheet.Cells(r, 1).Range("A1:A1")
x = EssMenuVZoomIn
End If
Next r


Thanks in advance
 
T

Tom Ogilvy

Dim rngTemp as Range
For r = 1 To rngB.Rows.Count
If ActiveSheet.Cells(r, 1).Value = "Arc Accesories" Then
Set rngTemp = ActiveSheet.Cells(r, 1)
exit for
end if
Next
if not rngTemp is nothing then
rngTemp.Select
x = EssMenuVZoomIn
else
Msgbox "String was not found"
End If
 
N

Nigel

Tom,

Ignore my last email I am an idiot

but I do have another question, I was hping to expand this and move down
column a and find the next parent select that cell and zoom in and so, I have
about parents to find.

the trouble is that RNGTEMP IAis staying in the original cell selected and
not move down, here is the code I am using

thanks

Set rngB = ActiveSheet.UsedRange.Columns("A:A")
For r = 1 To rngB.Rows.Count
If ActiveSheet.Cells(r, 1).Value = "Arc Accessories" Then
Set rngTemp = ActiveSheet.Cells(r, 1)
Exit For
End If
Next
If Not rngTemp Is Nothing Then
rngTemp.Select
x = EssMenuVZoomIn
Else
MsgBox "String was not found"
End If



Set rngB = ActiveSheet.UsedRange.Columns("A:A")
For r = 1 To rngB.Rows.Count
If ActiveSheet.Cells(r, 1).Value = "Cutting Tables" Then
Set rngTemp = ActiveSheet.Cells(r, 1)
Exit For
End If
Next
If Not rngTemp Is Nothing Then
rngTemp.Select
x = EssMenuVZoomIn
Else
MsgBox "String was not found"
End If
 
T

Tom Ogilvy

Guess who wrote that line Nigel.

You did. If you haven't defined rngB for use in

rngB.Rows.Count

there is no way I am going to know what you want.
 
H

Hilvert Scheper

Hi Nigel,
Why don't You use a rather different approach, and simply Loop the following
action:

Columns("A:A").Select
Selection.Find(What:="Arc Accesories", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Select

Do whatever You want to do with this Cell,
and Loop until there are no more cells with "Arc Accesories"?
Rgds,
Hilvert
 
S

Simon Lloyd

I wouldn't loop that way as it can prove to be slow, why not change al
the found cells at once like this
Dim rngFind As Rang
Dim strValueToPick As Strin
Dim rngPicked As Rang
Dim rngLook As Rang
Dim strFirstAddress As Strin

Set rngLook = Sheets("Sheet1").Range("A1:H25") 'change to sui
strValueToPick = InputBox("Enter value to find", "Find al
occurences"
With rngLoo
Set rngFind = .Find(strValueToPick, LookIn:=xlValues
lookat:=xlWhole
If Not rngFind Is Nothing The
strFirstAddress = rngFind.Addres
Set rngPicked = rngFin
D
Set rngPicked = Union(rngPicked, rngFind
Set rngFind = .FindNext(rngFind
Loop While Not rngFind Is Nothing And rngFind.Address <
strFirstAddres
End I
End Wit

If Not rngPicked Is Nothing The
Sheets("Sheet1").Select 'change as abov
rngPicked.Selec

End I
Selection.Value = "Changed" ' change to sui
Hi Nigel
Why don't You use a rather different approach, and simply Loop th
followin
action

Columns("A:A").Selec
Selection.Find(What:="Arc Accesories", After:=ActiveCell
LookIn:=xlValues, LookAt:=
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:=False
, SearchFormat:=False).Selec

Do whatever You want to do with this Cell
and Loop until there are no more cells with "Arc Accesories"
Rgds
Hilver
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
Top