find and select a cell
 

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

find and select a cell
 

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

--
Regards,
Tom Ogilvy

"Nigel" wrote:

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

find and select a cell
 

ok it fails on the 2nd line with "Object required message"


Thanks Tom


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Nigel" wrote:

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

find and select a cell
 

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




"Nigel" wrote:

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

find and select a cell
 

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.

--
Regards,
Tom Ogilvy


"Nigel" wrote:



ok it fails on the 2nd line with "Object required message"


Thanks Tom


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Nigel" wrote:

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

find and select a cell
 

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

"Nigel" wrote:

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




"Nigel" wrote:

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

find and select a cell
 

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

Set rngLook = Sheets("Sheet1").Range("A1:H25") 'change to suit
strValueToPick = InputBox("Enter value to find", "Find all
occurences")
With rngLook
Set rngFind = .Find(strValueToPick, LookIn:=xlValues,
lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <
strFirstAddress
End If
End With

If Not rngPicked Is Nothing Then
Sheets("Sheet1").Select 'change as above
rngPicked.Select

End If
Selection.Value = "Changed" ' change to suit


Hilvert Scheper;284169 Wrote:
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

"Nigel" wrote:

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




"Nigel" wrote:

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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79371