On Fri, 25 Feb 2011 05:07:54 -0800 (PST), AndreasHermle wrote:

Dear Experts:

I wonder whether the following is possible using VBA:

1. Search for the word 'Sales' on the current worksheet
2. If found then the following action has to be performed (This has
been recorded using the macro recorder)
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
3. The range has to be named 'range1'
4. The macro goes on searching for all instances of 'Sales', repeating
the named range creation as described under Point 2 and the 3
5. the range name's number is to be incremented by 1, i.e. range1,
range2, range3 etc.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Perhaps something like below will get you started. I may have missed some fine points.

The CurrentArea property of the Range object should work better for you than the cursor moves you recorded.

===============================
Option Explicit
Sub NameSales()
Dim c As Range
Dim firstAddress As String
Dim i As Long
i = 1
With Range("A1")
If .Value = "Sales" Then
.CurrentRegion.Name = "range" & i
firstAddress = .Address
i = i + 1
End If
End With

Set c = Cells.Find(What:="Sales", After:=Range("A1"), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByColumns, _
searchdirection:=xlNext, _
MatchCase:=True)

Do While Not c Is Nothing And c.Address < firstAddress
c.CurrentRegion.Name = "range" & i
i = i + 1
If firstAddress = "" Then firstAddress = c.Address
Set c = Cells.FindNext(c)
Loop

End Sub
================================