I am stumped

I have 2 routines the 1st ( Sub NextRow() ) is designed to take me to the
first row which has the text "NoXXX", and it works fine.

The 2nd routine is suppose to populate some TextBoxes on my UserForm and
that is not working. The code selects a starting point (cell A3) then it
calls NextRow to find the 1st occurrence of NoXXX, but when the remainder of
the code runs to populate the TextBoxes it is getting its data from row 3.
Even though the cell with the occurrence of NoXXX is selected.

Any ideas?


Sub NextRow()
'
' Macro4 Macro
'

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

End Sub

----------------------------------------------------------------------------------------

Private Sub UserForm_Initialize()

Dim rng
Set rng = Cells(ActiveCell.Row, 1)


'No Show #1 Data


'Range("A3").Select

Call NextRow

TextBox1.Value = rng(1, 4) & " " & rng(1, 3)
TextBox2.Value = rng(1, 6)
Me.TextBox2.Value = Format$(TextBox2.Value, "ddd dd mmm yy")
TextBox3.Value = rng(1, 7)
TextBox4.Value = rng(1, 8)
TextBox5.Value = rng(1, 13)
Me.TextBox5.Value = Format$(TextBox5.Value, "ddd dd mmm yy")
TextBox6.Value = rng(1, 15)


End Sub

Hi

Set the 'rng' variable after calling NextRow, or better (without
selecting any cell) do it like this:

Private Sub UserForm_Initialize()
Dim TargetRow As Long

'No Show #1 Data
TargetRow = Cells.Find(What:="NoXXX", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row + 1


TextBox1.Value = Cells(TargetRow, 4) & " " & Cells(TargetRow, 3)
TextBox2.Value = Cells(TargetRow, 6)
Me.TextBox2.Value = Format$(TextBox2.Value, "ddd dd mmm yy")
TextBox3.Value = Cells(TargetRow, 7)
TextBox4.Value = Cells(TargetRow, 8)
TextBox5.Value = Cells(TargetRow, 13)
Me.TextBox5.Value = Format$(TextBox5.Value, "ddd dd mmm yy")
TextBox6.Value = Cells(TargetRow, 15)

End Sub

Regards,
Per


On 15 Jul., 22:40, "Patrick C. Simonds" wrote:
I am stumped

I have 2 routines the 1st ( *Sub NextRow() *) is designed to take me to the
first row which has the text *"NoXXX", and it works fine.

The 2nd routine is suppose to populate some TextBoxes on my UserForm and
that is not working. The code selects a starting point (cell A3) then it
calls NextRow to find the 1st occurrence of NoXXX, but when the remainder of
the code runs to populate the TextBoxes it is getting its data from row 3..
Even though the cell with the occurrence of NoXXX is selected.

Any ideas?

Sub NextRow()
'
' Macro4 Macro
'

'
* * Cells.Find(What:="NoXXX", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
* * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
* * * * False, SearchFormat:=False).Select

End Sub

---------------------------------------------------------------------------*-------------

Private Sub UserForm_Initialize()

Dim rng
Set rng = Cells(ActiveCell.Row, 1)

'No Show #1 Data

* * 'Range("A3").Select

* * Call NextRow

* * TextBox1.Value = rng(1, 4) & " " & rng(1, 3)
* * TextBox2.Value = rng(1, 6)
* * * * Me.TextBox2.Value = Format$(TextBox2.Value, "ddd dd mmm yy")
* * TextBox3.Value = rng(1, 7)
* * TextBox4.Value = rng(1, 8)
* * TextBox5.Value = rng(1, 13)
* * * * Me.TextBox5.Value = Format$(TextBox5.Value, "ddd dd mmm yy")
* * TextBox6.Value = rng(1, 15)

End Sub