| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Weird range selection issue XL2003- help please!
change
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) Selection.Delete Shift:=xlUp to pullrng.copy PasteRng pullrng.EntireRow.Delete -- Regards, Tom Ogilvy "KR" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Weird range selection issue XL2003- help please!
Once again, I am in awe of the eloquent solutions by you and the other MVPs
in the group. It works perfectly. Just out of curiosity, any idea why it was selecting the wrong range before? I'll use your suggestion, but knowing the 'why' might keep me from making other related dumb posts the next time I'm working with ranges :) Thanks Tom! Keith "Tom Ogilvy" wrote in message ... change 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) Selection.Delete Shift:=xlUp to pullrng.copy PasteRng pullrng.EntireRow.Delete -- Regards, Tom Ogilvy "KR" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Weird range selection issue XL2003- help please!
I usually avoid cutting.
Open a new workbook with at least 2 sheet and select the first sheet. Then run this code: Sub TestCut() Worksheets(1).Select Set rng = Rows(10) MsgBox "Range being cut: " & rng.Address(external:=True) rng.Cut ActiveSheet.Next.Activate Rows(2).Select ActiveSheet.Paste MsgBox "Reference to cut range now " & vbNewLine _ & " shows it has been altered: " & _ rng.Address(external:=True) End Sub As you have observed, it alters the range reference just as you describe. why I can't say. Also, you may want to use cStr rather than str, then you won't need to use Trim. from the immediate window: ? "--" & cStr(100) & "<--" --100<-- ? "--" & Str(100) & "<--" -- 100<-- also, you don't really need to use anything ? "AC" & 100 AC100 will work fine with implicit conversion (use the ampersand for concatenation). then ? 111 & 345 111345 ? typename( 111 & 345) String -- Regards, Tom Ogilvy "KR" wrote: Once again, I am in awe of the eloquent solutions by you and the other MVPs in the group. It works perfectly. Just out of curiosity, any idea why it was selecting the wrong range before? I'll use your suggestion, but knowing the 'why' might keep me from making other related dumb posts the next time I'm working with ranges :) Thanks Tom! Keith "Tom Ogilvy" wrote in message ... change 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) Selection.Delete Shift:=xlUp to pullrng.copy PasteRng pullrng.EntireRow.Delete -- Regards, Tom Ogilvy "KR" wrote: 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 |
| Reply |
| 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 | |||
Linear Mode
