Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Part of my macro needs to delete records starting from a row which is
determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveCell.FormulaR1C1 = "2" Range("A2:A3").Select Dim LastRow2 As Long LastRow2 = Range("G" & Rows.Count).End(xlUp).Row Selection.AutoFill Destination:=Range("A2:A" & LastRow2) Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Range("C202:F202").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Which of the hardcoding you're concerned about?
This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then Debug.Print "There is no such value on this sheet" Else Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56*am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- * * Columns("A:A").Select * * Selection.Insert Shift:=xlToRight * * Range("A2").Select * * ActiveCell.FormulaR1C1 = "1" * * Range("A3").Select * * ActiveCell.FormulaR1C1 = "2" * * Range("A2:A3").Select * * Dim LastRow2 As Long * * LastRow2 = Range("G" & Rows.Count).End(xlUp).Row * * Selection.AutoFill Destination:=Range("A2:A" & LastRow2) * * Rows("2:2").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("B:B").Select * * Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ * * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ * * * * False, SearchFormat:=False).Activate * * Range("C202:F202").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Your assumptions are right.
I'm concerned about the hardcoding: 'Range("C202:F202").Select' The function will always look for the first "2" in the range. So how do I incorporate your suggestion into my code? The function looks for the first instance of "2" in column B, and should then select from that row downwards all cells in the range C:F and delete their contents. "AB" wrote: Which of the hardcoding you're concerned about? This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then Debug.Print "There is no such value on this sheet" Else Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56 am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveCell.FormulaR1C1 = "2" Range("A2:A3").Select Dim LastRow2 As Long LastRow2 = Range("G" & Rows.Count).End(xlUp).Row Selection.AutoFill Destination:=Range("A2:A" & LastRow2) Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Range("C202:F202").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Try this:
Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(what:="2") If myResultRange Is Nothing Then Debug.Print "There is no such value in Column B" Else Debug.Print "Found value in cell: " & myResultRange.Address Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I realize that it's still a standalone code (i.e., it's not incorporated into your code) but you could check if it delivers what you need without the other stuff it needs to do - but just the find- clearcontent bit. Please also note that all the 'select' bit of the code is entirelly necessary in a code - you pretty much (almost) can do anything without even selecting any of the objects. Paste the above code in a Standard VBA module, then try stepping it through by hitting F8 and you'll be able to see where you are and 'what it does'. So, does it work? On Jul 15, 12:21*pm, Pluggie wrote: Your assumptions are right. I'm concerned about the hardcoding: 'Range("C202:F202").Select' The function will always look for the first "2" in the range. So how do I incorporate your suggestion into my code? The function looks for the first instance of "2" in column B, and should then select from that row downwards all cells in the range C:F and delete their contents. "AB" wrote: Which of the hardcoding you're concerned about? This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value on this sheet" Else * * Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange *holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56 am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- * * Columns("A:A").Select * * Selection.Insert Shift:=xlToRight * * Range("A2").Select * * ActiveCell.FormulaR1C1 = "1" * * Range("A3").Select * * ActiveCell.FormulaR1C1 = "2" * * Range("A2:A3").Select * * Dim LastRow2 As Long * * LastRow2 = Range("G" & Rows.Count).End(xlUp).Row * * Selection.AutoFill Destination:=Range("A2:A" & LastRow2) * * Rows("2:2").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("B:B").Select * * Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ * * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ * * * * False, SearchFormat:=False).Activate * * Range("C202:F202").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Ooops - when i said 'necessary' i meant 'unnecessary'.
Also, i added a couple controls in the code: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(What:="2", After:= _ Cells(Rows.Count, "B"), LookAt:=xlWhole) If myResultRange Is Nothing Then Debug.Print "There is no such value in Column B" Else Debug.Print "Found value in cell: " & myResultRange.Address Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I added this: After:= Cells(Rows.Count, "B"), LookAt:=xlWhole This is to force Excel to start searching in Column B from row 1 and also to pick only the cell that contains only "2" and not "22" or "23", for instance. Feel free to remove, if not needed. On Jul 15, 1:06*pm, AB wrote: Try this: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value in Column B" Else * * Debug.Print "Found value in cell: " & myResultRange.Address * * Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I realize that it's still a standalone code (i.e., it's not incorporated into your code) but you could check if it delivers what you need without the other stuff it needs to do - but just the find- clearcontent bit. Please also note that all the 'select' bit of the code is entirelly necessary in a code - you pretty much (almost) can do anything without even selecting any of the objects. Paste the above code in a Standard VBA module, then try stepping it through by hitting F8 and you'll be able to see where you are and 'what it does'. So, does it work? On Jul 15, 12:21*pm, Pluggie wrote: Your assumptions are right. I'm concerned about the hardcoding: 'Range("C202:F202").Select' The function will always look for the first "2" in the range. So how do I incorporate your suggestion into my code? The function looks for the first instance of "2" in column B, and should then select from that row downwards all cells in the range C:F and delete their contents. "AB" wrote: Which of the hardcoding you're concerned about? This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value on this sheet" Else * * Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange *holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56 am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- * * Columns("A:A").Select * * Selection.Insert Shift:=xlToRight * * Range("A2").Select * * ActiveCell.FormulaR1C1 = "1" * * Range("A3").Select * * ActiveCell.FormulaR1C1 = "2" * * Range("A2:A3").Select * * Dim LastRow2 As Long * * LastRow2 = Range("G" & Rows.Count).End(xlUp).Row * * Selection.AutoFill Destination:=Range("A2:A" & LastRow2) * * Rows("2:2").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("B:B").Select * * Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ * * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ * * * * False, SearchFormat:=False).Activate * * Range("C202:F202").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
And, after all, you can put it into your code like this:
----------------------------code---------------------------------- Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveCell.FormulaR1C1 = "2" Range("A2:A3").Select Dim LastRow2 As Long LastRow2 = Range("G" & Rows.Count).End(xlUp).Row Selection.AutoFill Destination:=Range("A2:A" & LastRow2) Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal FindAndClearCells ----------------------------end-of-code----------------------------- 'Here somewhere needs to be 'End Sub' - to end your Sub. Sub FindAndClearCells() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(What:="2", After:= _ Cells(Rows.Count, "B"), LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False) If myResultRange Is Nothing Then Debug.Print "There is no such value in Column B" Else Debug.Print "Found value in cell: " & myResultRange.Address Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub As a side note - perhaps your code can be considerably simplified by eliminating all that 'select' stuff it would also speed to code up but if it's not a concern and it works as anticipated - you don't need to bother. On Jul 15, 1:17*pm, AB wrote: Ooops - when i said 'necessary' i meant 'unnecessary'. Also, i added a couple controls in the code: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(What:="2", After:= _ * * * * Cells(Rows.Count, "B"), LookAt:=xlWhole) If myResultRange Is Nothing Then * * Debug.Print "There is no such value in Column B" Else * * Debug.Print "Found value in cell: " & myResultRange.Address * * Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I added this: After:= Cells(Rows.Count, "B"), LookAt:=xlWhole This is to force Excel to start searching in Column B from row 1 and also to pick only the cell that contains only "2" and not "22" or "23", for instance. Feel free to remove, if not needed. On Jul 15, 1:06*pm, AB wrote: Try this: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value in Column B" Else * * Debug.Print "Found value in cell: " & myResultRange.Address * * Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I realize that it's still a standalone code (i.e., it's not incorporated into your code) but you could check if it delivers what you need without the other stuff it needs to do - but just the find- clearcontent bit. Please also note that all the 'select' bit of the code is entirelly necessary in a code - you pretty much (almost) can do anything without even selecting any of the objects. Paste the above code in a Standard VBA module, then try stepping it through by hitting F8 and you'll be able to see where you are and 'what it does'. So, does it work? On Jul 15, 12:21*pm, Pluggie wrote: Your assumptions are right. I'm concerned about the hardcoding: 'Range("C202:F202").Select' The function will always look for the first "2" in the range. So how do I incorporate your suggestion into my code? The function looks for the first instance of "2" in column B, and should then select from that row downwards all cells in the range C:F and delete their contents. "AB" wrote: Which of the hardcoding you're concerned about? This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value on this sheet" Else * * Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange *holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56 am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- * * Columns("A:A").Select * * Selection.Insert Shift:=xlToRight * * Range("A2").Select * * ActiveCell.FormulaR1C1 = "1" * * Range("A3").Select * * ActiveCell.FormulaR1C1 = "2" * * Range("A2:A3").Select * * Dim LastRow2 As Long * * LastRow2 = Range("G" & Rows.Count).End(xlUp).Row * * Selection.AutoFill Destination:=Range("A2:A" & LastRow2) * * Rows("2:2").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("B:B").Select * * Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ * * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ * * * * False, SearchFormat:=False).Activate * * Range("C202:F202").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Works like a charm... the part about removing all the select statements from
my macro is understandable in concept... but in practise I don't get it yet. But I will get it sometime... "AB" wrote: Ooops - when i said 'necessary' i meant 'unnecessary'. Also, i added a couple controls in the code: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(What:="2", After:= _ Cells(Rows.Count, "B"), LookAt:=xlWhole) If myResultRange Is Nothing Then Debug.Print "There is no such value in Column B" Else Debug.Print "Found value in cell: " & myResultRange.Address Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I added this: After:= Cells(Rows.Count, "B"), LookAt:=xlWhole This is to force Excel to start searching in Column B from row 1 and also to pick only the cell that contains only "2" and not "22" or "23", for instance. Feel free to remove, if not needed. On Jul 15, 1:06 pm, AB wrote: Try this: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(what:="2") If myResultRange Is Nothing Then Debug.Print "There is no such value in Column B" Else Debug.Print "Found value in cell: " & myResultRange.Address Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I realize that it's still a standalone code (i.e., it's not incorporated into your code) but you could check if it delivers what you need without the other stuff it needs to do - but just the find- clearcontent bit. Please also note that all the 'select' bit of the code is entirelly necessary in a code - you pretty much (almost) can do anything without even selecting any of the objects. Paste the above code in a Standard VBA module, then try stepping it through by hitting F8 and you'll be able to see where you are and 'what it does'. So, does it work? On Jul 15, 12:21 pm, Pluggie wrote: Your assumptions are right. I'm concerned about the hardcoding: 'Range("C202:F202").Select' The function will always look for the first "2" in the range. So how do I incorporate your suggestion into my code? The function looks for the first instance of "2" in column B, and should then select from that row downwards all cells in the range C:F and delete their contents. "AB" wrote: Which of the hardcoding you're concerned about? This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then Debug.Print "There is no such value on this sheet" Else Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56 am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code). This should be modified so that it does what I want. ----------------------------code---------------------------------- Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A2").Select ActiveCell.FormulaR1C1 = "1" Range("A3").Select ActiveCell.FormulaR1C1 = "2" Range("A2:A3").Select Dim LastRow2 As Long LastRow2 = Range("G" & Rows.Count).End(xlUp).Row Selection.AutoFill Destination:=Range("A2:A" & LastRow2) Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Range("C202:F202").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete records from result-cell after a find-function call
Glad it helped.
Thanks for the feedback. On Jul 15, 1:36*pm, Pluggie wrote: Works like a charm... the part about removing all the select statements from my macro is understandable in concept... but in practise I don't get it yet. But I will get it sometime... "AB" wrote: Ooops - when i said 'necessary' i meant 'unnecessary'. Also, i added a couple controls in the code: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(What:="2", After:= _ * * * * Cells(Rows.Count, "B"), LookAt:=xlWhole) If myResultRange Is Nothing Then * * Debug.Print "There is no such value in Column B" Else * * Debug.Print "Found value in cell: " & myResultRange.Address * * Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I added this: After:= Cells(Rows.Count, "B"), LookAt:=xlWhole This is to force Excel to start searching in Column B from row 1 and also to pick only the cell that contains only "2" and not "22" or "23", for instance. Feel free to remove, if not needed. On Jul 15, 1:06 pm, AB wrote: Try this: Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.Columns("B").Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value in Column B" Else * * Debug.Print "Found value in cell: " & myResultRange.Address * * Range(myResultRange.Offset(0, 1), Cells(Rows.Count, "F")).ClearContents End If End Sub I realize that it's still a standalone code (i.e., it's not incorporated into your code) but you could check if it delivers what you need without the other stuff it needs to do - but just the find- clearcontent bit. Please also note that all the 'select' bit of the code is entirelly necessary in a code - you pretty much (almost) can do anything without even selecting any of the objects. Paste the above code in a Standard VBA module, then try stepping it through by hitting F8 and you'll be able to see where you are and 'what it does'. So, does it work? On Jul 15, 12:21 pm, Pluggie wrote: Your assumptions are right. I'm concerned about the hardcoding: 'Range("C202:F202").Select' The function will always look for the first "2" in the range. So how do I incorporate your suggestion into my code? The function looks for the first instance of "2" in column B, and should then select from that row downwards all cells in the range C:F and delete their contents. "AB" wrote: Which of the hardcoding you're concerned about? This: 'Selection.Find(What:="2" ' or 'Range("C202:F202").Select' From your post i'd gather that the latter is your concern. Does it mean that the Find method you refer to will always be searching for '2' in a specified range? If it's not the case, then where the variables (values to search for) are coming from? Maybe this would get you started (if I've understood correctly your post, though): Sub TryFindCell() Dim myResultRange As Range Set myResultRange = ActiveSheet.UsedRange.Find(what:="2") If myResultRange Is Nothing Then * * Debug.Print "There is no such value on this sheet" Else * * Debug.Print "Found value in cell: " & myResultRange.Address End If End Sub So, now if successful, the variable myResultRange *holds the range properties of the cell where the first instance of the searched value ("2" in this instance) was found. Check the Immediate window. On Jul 15, 10:56 am, Pluggie wrote: Part of my macro needs to delete records starting from a row which is determined by a find-function call. Naturally... the resultcell of this find-function will vary each time. How do I make sure that the resultcell of the first recorded instance of the macro is not hardcoded into the macro? Here is the sample of code I have now (last 7 rows of the code).. This should be modified so that it does what I want. ----------------------------code---------------------------------- * * Columns("A:A").Select * * Selection.Insert Shift:=xlToRight * * Range("A2").Select * * ActiveCell.FormulaR1C1 = "1" * * Range("A3").Select * * ActiveCell.FormulaR1C1 = "2" * * Range("A2:A3").Select * * Dim LastRow2 As Long * * LastRow2 = Range("G" & Rows.Count).End(xlUp).Row * * Selection.AutoFill Destination:=Range("A2:A" & LastRow2) * * Rows("2:2").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ * * * * OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ * * * * DataOption1:=xlSortNormal * * Columns("B:B").Select * * Selection.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ * * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ * * * * False, SearchFormat:=False).Activate * * Range("C202:F202").Select * * Range(Selection, Selection.End(xlDown)).Select * * Selection.ClearContents ----------------------------end-of-code----------------------------- Anybody have the solution? Regards, Pluggie.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find mulitple records Vlookup type function? | Excel Programming | |||
Find duplicate records and delete | Excel Programming | |||
Can VLookup function find and list multiple records? | Excel Worksheet Functions | |||
Find records between two dates & check a box for each positive result | Excel Programming | |||
Call to custom function appears as typed not as result | Excel Programming |