Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have a rng problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have a rng problem
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 -- Regards, OssieMac "Patrick C. Simonds" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I have a rng problem
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 "Patrick C. Simonds" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |