Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 91 with FIND in macro
I can't seem to get around a ERROR 91 message when I make a macro loop with a
FIND function.....any ideas on how to correct this sort of error would be welcome. I know the data is present and I know it has to be just as it looks rounded values will not work, I also know it can be part of the cell content. I'm looking over a full sheet. A good way to test this would be to place some number of RAND numbers within a sheet and use the MIN function to find the least......use the FIND function within a macro to find the least number and select and remove that so it looks for the next higher value....repeat Before responding please test your idea€¦.Ive been given over 100 bad ideas to date, (with in 1 week) As far as I can tell the FIND is looking for part of a string and I understand the 91 ERROR to be some sort of SET value error. The macro should look something like this with the data in sheet 1 and the MIN function in sheet 2 Starting in sheet 2 active cell and ending in same.......recorded in relative ActiveCell.Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet1").Select ActiveCell.Cells.Select Selection.Find(What:="0.110937", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents Sheets("Sheet2").Select ActiveCell.Offset(-1, 0).Range("A1").Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 91 with FIND in macro
What are you trying to achieve with these two lines?:
... ActiveCell.Offset(1, 0).Range("A1").Select ... ... ActiveCell.Cells.Select ... *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 91 with FIND in macro
Hi Robert,
It is rarely necessary to make physical selections, so, with selections removed, try something like: Sub Tester03() Dim FoundCell As Range Dim sStr As String sStr = "0.110937" '"Apples" ' With Sheets("Sheet1").Cells Set FoundCell = .Find(What:=sStr, _ After:=.Range("A1"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If Not FoundCell Is Nothing Then FoundCell.ClearContents End If End Sub --- Regards, Norman "Robert AS" wrote in message ... I can't seem to get around a ERROR 91 message when I make a macro loop with a FIND function.....any ideas on how to correct this sort of error would be welcome. I know the data is present and I know it has to be just as it looks rounded values will not work, I also know it can be part of the cell content. I'm looking over a full sheet. A good way to test this would be to place some number of RAND numbers within a sheet and use the MIN function to find the least......use the FIND function within a macro to find the least number and select and remove that so it looks for the next higher value....repeat Before responding please test your idea..I've been given over 100 bad ideas to date, (with in 1 week) As far as I can tell the FIND is looking for part of a string and I understand the 91 ERROR to be some sort of SET value error. The macro should look something like this with the data in sheet 1 and the MIN function in sheet 2 Starting in sheet 2 active cell and ending in same.......recorded in relative ActiveCell.Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet1").Select ActiveCell.Cells.Select Selection.Find(What:="0.110937", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents Sheets("Sheet2").Select ActiveCell.Offset(-1, 0).Range("A1").Select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 91 with FIND in macro
ActiveCell.Offset(1, 0).Range("A1").Select
gives me a copy of the data to be worked ActiveCell.Cells.Select okay this may be redondent in some form I agree because it will in al odds find the cell anyway, But at the page holds nothing but data it also can cause no real harm "Claud Balls" wrote: What are you trying to achieve with these two lines?: ... ActiveCell.Offset(1, 0).Range("A1").Select ... ... ActiveCell.Cells.Select ... *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 91 with FIND in macro
Still having problems with this
How do I set the string to the value found with the MIN function in sheet 2 as I explaned before the macro? Consider this....toss some 30 RAND numbvers any place in sheet 1 let a MIN function in sheet 2 find the least value in sheet 1 Move to that min value in sheet 1 and clear it so the MIN function in shhet 2 moves to the next higher value Find and deleate all values in order in sheet 1...... But still this is only my way of understanding how to get the FIND to work with changing values and has little to do with what I'm really doing, what I;m doing would fill pages. I have also found I need to select the found cell so that I can look and use the data around the selected cell back in page 2 The FIND is only a very small part of all the other macros all in all I have 125 meg data in page one and 87meg of macros.....the FIND is the only one I can't get to work and I can't afford the time to baby sit it with input box's The value "0.110937" was only the value at the time of recording the macro but that value changes all the time.....setting the string to that alone will not work.....how do I set the value found with the MIN function in sheet 2 to the string value for the find to work in sheet 1? "Norman Jones" wrote: Hi Robert, It is rarely necessary to make physical selections, so, with selections removed, try something like: Sub Tester03() Dim FoundCell As Range Dim sStr As String sStr = "0.110937" '"Apples" ' With Sheets("Sheet1").Cells Set FoundCell = .Find(What:=sStr, _ After:=.Range("A1"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If Not FoundCell Is Nothing Then FoundCell.ClearContents End If End Sub --- Regards, Norman "Robert AS" wrote in message ... I can't seem to get around a ERROR 91 message when I make a macro loop with a FIND function.....any ideas on how to correct this sort of error would be welcome. I know the data is present and I know it has to be just as it looks rounded values will not work, I also know it can be part of the cell content. I'm looking over a full sheet. A good way to test this would be to place some number of RAND numbers within a sheet and use the MIN function to find the least......use the FIND function within a macro to find the least number and select and remove that so it looks for the next higher value....repeat Before responding please test your idea..I've been given over 100 bad ideas to date, (with in 1 week) As far as I can tell the FIND is looking for part of a string and I understand the 91 ERROR to be some sort of SET value error. The macro should look something like this with the data in sheet 1 and the MIN function in sheet 2 Starting in sheet 2 active cell and ending in same.......recorded in relative ActiveCell.Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet1").Select ActiveCell.Cells.Select Selection.Find(What:="0.110937", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents Sheets("Sheet2").Select ActiveCell.Offset(-1, 0).Range("A1").Select |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 91 with FIND in macro
Sub Tester03()
Dim FoundCell As Range Dim rng as Range Set rng = Worksheets("Sheet2").Range("B2") With Sheets("Sheet1") Set FoundCell = .Cells.Find(What:=rng.value, _ After:=.Range("A1"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not FoundCell Is Nothing Then .Activate FoundCell.Select ' FoundCell.ClearContents End If End With End Sub -- Regards, Tom Ogilvy "Robert AS" wrote in message ... Still having problems with this How do I set the string to the value found with the MIN function in sheet 2 as I explaned before the macro? Consider this....toss some 30 RAND numbvers any place in sheet 1 let a MIN function in sheet 2 find the least value in sheet 1 Move to that min value in sheet 1 and clear it so the MIN function in shhet 2 moves to the next higher value Find and deleate all values in order in sheet 1...... But still this is only my way of understanding how to get the FIND to work with changing values and has little to do with what I'm really doing, what I;m doing would fill pages. I have also found I need to select the found cell so that I can look and use the data around the selected cell back in page 2 The FIND is only a very small part of all the other macros all in all I have 125 meg data in page one and 87meg of macros.....the FIND is the only one I can't get to work and I can't afford the time to baby sit it with input box's The value "0.110937" was only the value at the time of recording the macro but that value changes all the time.....setting the string to that alone will not work.....how do I set the value found with the MIN function in sheet 2 to the string value for the find to work in sheet 1? "Norman Jones" wrote: Hi Robert, It is rarely necessary to make physical selections, so, with selections removed, try something like: Sub Tester03() Dim FoundCell As Range Dim sStr As String sStr = "0.110937" '"Apples" ' With Sheets("Sheet1").Cells Set FoundCell = .Find(What:=sStr, _ After:=.Range("A1"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If Not FoundCell Is Nothing Then FoundCell.ClearContents End If End Sub --- Regards, Norman "Robert AS" wrote in message ... I can't seem to get around a ERROR 91 message when I make a macro loop with a FIND function.....any ideas on how to correct this sort of error would be welcome. I know the data is present and I know it has to be just as it looks rounded values will not work, I also know it can be part of the cell content. I'm looking over a full sheet. A good way to test this would be to place some number of RAND numbers within a sheet and use the MIN function to find the least......use the FIND function within a macro to find the least number and select and remove that so it looks for the next higher value....repeat Before responding please test your idea..I've been given over 100 bad ideas to date, (with in 1 week) As far as I can tell the FIND is looking for part of a string and I understand the 91 ERROR to be some sort of SET value error. The macro should look something like this with the data in sheet 1 and the MIN function in sheet 2 Starting in sheet 2 active cell and ending in same.......recorded in relative ActiveCell.Select Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet1").Select ActiveCell.Cells.Select Selection.Find(What:="0.110937", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select Application.CutCopyMode = False Selection.ClearContents Sheets("Sheet2").Select ActiveCell.Offset(-1, 0).Range("A1").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Macro Error | Excel Discussion (Misc queries) | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
"Can't find macro" error | New Users to Excel | |||
can't find error in macro logic... | Excel Programming | |||
macro error "can't find project or library" | Excel Programming |