Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird range selection issue XL2003- help please!
In the code below, it works great until I return to Sheet1 to delete the
selected row. For some reason, when I return to Sheet1, the command PullRng.Select actually selects a range I haven't even defined (a combination of PullRng and PasteRng; the sheet from PullRng and the row from PasteRng). So basically, it goes to PullRng, selects the correct range, copies it, selects PasteRng on Sheet19, and pastes it in the right row. It is only when it returns to Sheet1 to delete that row, that it goes whacky and selects the equivalent of PasteRng Row, e,g, 2, then 3, then 4... Am I doing something wrong? Is this a bug? Either way, what should I do differently? Thanks, Keith Private Sub CommandButton1_Click() frmMove.Hide 'hide so I can switch to Excel and see the sheet to tell what is actually happening during development Dim PullRng As Range Dim PasteRng As Range LastOutputMUV = Sheet1.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row FirstClearDestRow = 1 + Sheet19.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row Dim ctl As Control For Each ctl In frmMove.Controls If TypeOf ctl Is MSForms.CheckBox Then 'from a userform that has the 12 months to determine what months of data to copy If ctl.Value = True Then 'user wants to move this month 'MsgBox ctl.Tag 'shows which month(s) are selected, 'each chkbox tag is set to the number equivalent of that month, Jan=1, Feb=2, etc For n = 3 To LastOutputMUV 'cycle through all the source records If Month(Sheet1.Range("A" & Trim(Str(n))).Value) = ctl.Tag And _ Year(Sheet1.Range("A" & Trim(Str(n))).Value) = 2006 Then 'for the records that match 'convert my numerics to text to simplify my set range statements ActRow = Trim(Str(n)) PstRow = Trim(Str(FirstClearDestRow)) 'set the two ranges Set PullRng = Sheet1.Range(ActRow & ":" & ActRow) 'note this is Sheet1 Set PasteRng = Sheet19.Range(PstRow & ":" & PstRow) 'note this is Sheet19 'copy the row to my destination sheet and delete the row from the source sheet Sheet1.Activate PullRng.Select Selection.Cut Sheet19.Select PasteRng.Select ActiveSheet.Paste Sheet1.Select 'or activate, tried both PullRng.Select '**** This is where it selects Sheet1.Range(PstRow & ":" & PstRow) 'which I never did in code! PstRow is for Sheet19.... Selection.Delete Shift:=xlUp FirstClearDestRow = FirstClearDestRow + 1 n = n - 1 End If Next End If End If Next End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please please help!! Weird coding issue | Excel Discussion (Misc queries) | |||
Multiple range selection issue | Excel Discussion (Misc queries) | |||
Weird Printing Issue | Excel Discussion (Misc queries) | |||
weird date issue | Excel Programming | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming |