Delete records from result-cell after a find-function call

[Pluggie]

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.

 

[AB]

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.

 

[Pluggie]

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]

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?

 

[AB]

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.

 

[AB]

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.

 

[Pluggie]

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]

Glad it helped.
Thanks for the feedback.

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...










- Show quoted text -