I am sorry if I am not being clear

my intent is to populate a number of TextBoxes on my UserForm. So I was
hoping to have your code run at the start of my UserForm Initialization,
identify each of the lines that has an NoXXX and assign a range to it. That
is why I thought I would have to set reach range individually.

Set rng1 = Cells(ActiveCell.Row, 1)
Set rng2 = Cells(ActiveCell.Row, 1)
Set rng3= Cells(ActiveCell.Row, 1) ect....

I thought that I would then be able to continue Initializing my UserForm
based on those ranges, and populate my TextBoxes.

Textbox100.value = rng1(1, 3).value
Textbox101.value = rng1(1, 5).value

Textbox200.value = rng2(1, 3).value
Textbox201.value = rng2(1, 5).value

And then as part of my exit routine write any changed data back to the
worksheet. With your code I do not understand how to initialize my textboxes
or how to write the changes back.





"Dave Peterson" wrote in message
...
I just read your followup -- you wanted the cell in column A to be rng().

change:
Set Rng(rCtr) = FoundCell
to
Set Rng(rCtr) = FoundCell.entirerow.cells(1)




Dave Peterson wrote:

I wouldn't name my variables like that. Instead I'd use an array.

Option Explicit
Sub testme04()
Dim Rng() As Range
Dim rCtr As Long
Dim HowMany As Long
Dim myStr As String
Dim FoundCell As Range

myStr = "NoXXX"

With ActiveSheet.Range("aa:aa")
HowMany = Application.CountIf(.Cells, myStr)
If HowMany = 0 Then
MsgBox "No " & myStr & " Found!"
Exit Sub
End If

ReDim Rng(1 To HowMany)

Set FoundCell = .Cells(.Cells.Count)
rCtr = 0
Do
Set FoundCell = .Cells.Find(What:=myStr, _
After:=FoundCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If FoundCell Is Nothing Then
'this shouldn't happen!
Exit Do
Else
rCtr = rCtr + 1
Set Rng(rCtr) = FoundCell
End If

If rCtr = HowMany Then
Exit Do
End If
Loop
End With

If rCtr 0 Then
For rCtr = LBound(Rng) To UBound(Rng)
MsgBox Rng(rCtr).Address(0, 0)
Next rCtr
End If
End Sub

But I may have changed your intent.

I did that =countif() based on NoXXX--not "Pat's NoXXX data". And I
changed the
xlpart to xlwhole to match this.

If you really wanted "Pat's noXXX data", then you could change two lines:

HowMany = Application.CountIf(.Cells, "*" & myStr & "*")
and
LookAt:=xlPart

Actually, you could use excel and the way it treats wild cards and just
make one
change:
myStr = "*NoXXX*"

Those asterisk wildcards will mean that =countif() looks anywhere in the
cell
and the same with xlWhole.

"Patrick C. Simonds" wrote:

The first 2 lines of code below

What I am wondering is if the code below could be modified in some way
to
where as it finds the first occurrence of NoXXX if assigns the variable
rng1
and then Set rng1 = Cells(ActiveCell.Row, 1), and then loop to the
next
occurrence of NoXXX and set it as rng2 ect.

There can be up to 10 occurrences of NoXXX in column AA. The other part
of
the problem is that the last occurrence must be called rng. So if there
was
only 1 occurrence of NoXXX it would have to be called rng.

All of this is then used to populate my UserForm with information from
each
of the lines.

Sub NextRow()
'
' Macro4 Macro
'

'This routine finds each occurance of NoXXX in column AA so that
multipage 2
of NoShowDataInput UserForm can display prior No Shows

Dim c As Range

On Error GoTo Done

ActiveSheet.Columns("AA").Hidden = False

Set c = Cells.Find(What:="NoXXX", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If Not c Is Nothing Then
If Intersect(c, Union(Rows("1:" & (ActiveCell.Row + _
(ActiveCell.Row < 1))), Range("A" & _
ActiveCell.Row & ":" & ActiveCell.Address))) _
Is Nothing Then
c.Select

Else

End If
End If

Done:
ActiveSheet.Columns("AA").Hidden = True

End Sub

--

Dave Peterson

--

Dave Peterson