I have a rng problem

[Patrick C. Simonds]

My Userform has a series of textboxes on it and the code below which are
populate during the UserForm initialization. When the NextRow routine finds
the value NoXXX. I have included an abbreviated version of the relevant part
of my UserForm Initialization. Everything works great in as much as all
TexBoxes and OptionButtons are set properly during my Initialization. My
problems begin when I edit the TextBoxes and/or change the OptionButtons.
Since rng1, rng2 and rng3 could have been any row (it all depends on where
the occurrences of NoXXX were found, I have no way of writing data back to
the worksheet.

Is there any way in which to capture the row data associated with each of
the rng1, rng2 and rng3 so that data can be written back to the proper
location?

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'No Show #1 Data
Dim rng1
Set rng1 = Cells(ActiveCell.Row, 1)

TextBox2202.Value = rng1(1, 6)
'Date of No Show
Me.TextBox2202.Value = Format$(TextBox2202.Value, "ddd dd mmm yy")

'No Show #2 Data

Call NextRow

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

TextBox2302.Value = rng2(1, 6)
'Date of No Show
Me.TextBox2302.Value = Format$(TextBox2302.Value, "ddd dd mmm yy")

'No Show #3 Data

Call NextRow

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

TextBox2402.Value = rng3(1, 6)
'Date of No Show
Me.TextBox2402.Value = Format$(TextBox2402.Value, "ddd dd mmm yy")

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub NextRow()
'
' Macro4 Macro
'

'This routine places the NoXXX in column AA so that multipage 2 of
NoShowDataInput UserForm can display prior No Shows
Dim C As Range

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

End Sub

 

[OssieMac]

Hello Patrick,

If you declare the range variables (Dim statements) at the top of the VBA
editor in the Declarations area before any of your subs then they can be
used by any sub in the module and they retain their value unless the code
gets recompiled which does not usually occur during normal production use.

I see that you have dimensioned without specifying the type of variable. If
you specify the type of variable like my examples below then most of the time
when you use the variable the intellisense provides dropdowns of the possible
next part of the code each tme you enter a dot or whatever.

declare variables like this at top in Declarations area.
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

assign values to the ranges like this
rng1 = textbox1.value
rng2(1, 6) = textbox2.value

 

[Joel]

The problem is with the select statement. Don't select the cell. In the
code below I modified NextRow to be a function which returns the found cell.


'No Show #1 Data
Dim rng1
Set rng1 = Cells(ActiveCell.Row, 1)

TextBox2202.Value = rng1(1, 6)
'Date of No Show
Me.TextBox2202.Value = Format$(TextBox2202.Value, "ddd dd mmm yy")

'No Show #2 Data

FoundCell = NextRow(rng1)

Dim rng2
Set rng2 = Cells(Found.Row, 1)

TextBox2302.Value = rng2(1, 6)
'Date of No Show
Me.TextBox2302.Value = Format$(TextBox2302.Value, "ddd dd mmm yy")

'No Show #3 Data

FoundCell = NextRow(FoundCell)

Dim rng3
Set rng3 = Cells(Found.Row, 1)

TextBox2402.Value = rng3(1, 6)
'Date of No Show
Me.TextBox2402.Value = Format$(TextBox2402.Value, "ddd dd mmm yy")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function NextRow(FirstCell as Range) as Variant
'
' Macro4 Macro
'

'This routine places the NoXXX in column AA so that multipage 2 of
NoShowDataInput UserForm can display prior No Shows
Dim C As Range

Set C = Cells.Find(What:="NoXXX", After:=FirstCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If Not C Is Nothing Then
If Intersect(C, Union(Rows("1:" & (FirstCell.Row + _
(FirstCell.Row <> 1))), Range("A" & _
FirstCell.Row & ":" & FirstCell.Address))) _
Is Nothing Then
C.Select

Else

End If
End If

set NextRow = c
End Sub