Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
Does the worksheet have to active for this code to work?
Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
One way:
Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender With Sheets("Sheet1") fRow = .Columns(1).Find( _ What:=TxtMan.Value, _ After:=.Cells(5000, 1), _ LookIn:=xlFormulas, _ LookAT:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Row .Rows(fRow).Delete End With Exit Sub ender: MsgBox "Value not found" End Sub In article , Mekinnik wrote: Does the worksheet have to active for this code to work? Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
To make it not care what is active, use variables
Private Sub BtnDelete_Click() Dim fRow As Long,WS as Worksheet, rngHitCell as Range Set WS = Workbooks("Whatever").Worksheets("Whatever") Set rngHitCell = WS.Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If rngHitCell is Nothing Then MsgBox "Value not found" Else WS.Rows(rngHitCell.Row).Delete End If End Sub "Mekinnik" wrote: Does the worksheet have to active for this code to work? Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
When in doubt you are best off to be explicit. Include the sheet and then you
will always hit the right sheet. Then there are no worries (so long as you have not tried to select or activate something). Try something more like this... Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Valu e, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub -- HTH... Jim Thomlinson "Mekinnik" wrote: Does the worksheet have to active for this code to work? Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
Actually, I forgot something ....
Private Sub BtnDelete_Click() Dim fRow As Long,WS as Worksheet, rngHitCell as Range Set WS = Workbooks("Whatever").Worksheets("Whatever") Set rngHitCell = WS.Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If rngHitCell is Nothing Then MsgBox "Value not found" Else If rngHitCell.Row 5000 then WS.Rows(rngHitCell.Row).Delete Else Msgbox "Value found before Row 5000" End If End If End Sub "INTP56" wrote: To make it not care what is active, use variables Private Sub BtnDelete_Click() Dim fRow As Long,WS as Worksheet, rngHitCell as Range Set WS = Workbooks("Whatever").Worksheets("Whatever") Set rngHitCell = WS.Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If rngHitCell is Nothing Then MsgBox "Value not found" Else WS.Rows(rngHitCell.Row).Delete End If End Sub "Mekinnik" wrote: Does the worksheet have to active for this code to work? Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
Oops I missed one reference. JE's code does it. Or you could tighten things
up with this... Private Sub BtnDelete_Click() On Error GoTo ender Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Valu e, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).EntireRow.Delete Exit Sub ender: MsgBox "Value not found" End Sub -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: When in doubt you are best off to be explicit. Include the sheet and then you will always hit the right sheet. Then there are no worries (so long as you have not tried to select or activate something). Try something more like this... Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Valu e, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub -- HTH... Jim Thomlinson "Mekinnik" wrote: Does the worksheet have to active for this code to work? Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code question
Just wanted to caution you about this method. If you are sure the search will
never find the value in the 1st 5000 rows, then this is fine. However, if you are specifying After because it may be, then you could delete a row above row 5000 using this method. Another way to avoid this is to limit the search via ..Range(.Cells(5001,1),.Cells(65536,1)).Find( ....) Bob "Jim Thomlinson" wrote: Oops I missed one reference. JE's code does it. Or you could tighten things up with this... Private Sub BtnDelete_Click() On Error GoTo ender Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Valu e, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).EntireRow.Delete Exit Sub ender: MsgBox "Value not found" End Sub -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: When in doubt you are best off to be explicit. Include the sheet and then you will always hit the right sheet. Then there are no worries (so long as you have not tried to select or activate something). Try something more like this... Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Valu e, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub -- HTH... Jim Thomlinson "Mekinnik" wrote: Does the worksheet have to active for this code to work? Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender fRow = Columns(1).Find(What:=TxtMan.Value, _ After:=Cells(5000, 1), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).Row Rows(fRow).Delete Exit Sub ender: MsgBox "Value not found" End Sub Because if it does not then it doesn't work. Right now it requires it to be and I would like to knwo how to make so it doesn't need the sheet to be active? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code question | Excel Discussion (Misc queries) | |||
Code Question | Excel Discussion (Misc queries) | |||
Another code question | Excel Discussion (Misc queries) | |||
VBA code question | Excel Discussion (Misc queries) | |||
Code Question | Excel Programming |