Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use find to select entire rows with a cell with that value | Excel Worksheet Functions | |||
Find text in a cell and select whole row question | Excel Programming | |||
find highest number then select next cell | Excel Programming | |||
Find cell select row and delete | Excel Programming | |||
Find a certain word in excel and select the cell | Excel Programming |