Weird range selection issue XL2003- help please!

K

KR

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
 
T

Tom Ogilvy

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 said:
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
Click to expand...
 
K

KR

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code does not error but no copy either 4
Return last row no. of column with blanks at top 7
copy/paste in the next column 4
Placing the MsgBox "No ID found" statement in my code?? 12
copy-paste information from different workbooks 0
Mail a different file(s) to each person in a range 0
Select, copy, insert, then paste. 12
Help? Anyone see problem with this macro? Way to condense it? 2

Top