Everything works...but can't get it to Loop
 

I have been able to get everything to do what I need it to do, but when I
added the Do...Until Loop it gets hung up.

Here is what I have so far:

# Dim aRange As Range
# Set aRange = ActiveCell
#
#
# 'Do Until ActiveCell.Value = ""
# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets stuck
here!
# ActiveSheet.Paste
# ActiveCell.Copy
#
# aRange.Offset([0], [1]).Select
# Selection.End(xlDown).Offset([0], [-1]).Select
# ActiveSheet.Paste
# Application.CutCopyMode = False
# ActiveCell.Select
# Range(Selection, Selection.End(xlUp)).Select
# Selection.FillUp
# ActiveCell.Select
# Selection.End(xlDown).Select
# ActiveCell.Select
# Selection.Offset([4], [1]).Select
# 'Loop
#
# End Sub

Any suggestions??? Also, if any of this code can be cleaned up feel free to
offer ideas!

Thanks in advance

Everything works...but can't get it to Loop
 

1. Why do you have brackets around the numbers in your Offsets?
2. Move "Set aRange = ActiveCell" inside the Do loop (right after Do Until)
3. You should try to adjust the code so you aren't selecting cells and
changing the selection and so forth. For example:

# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets
stuck here!
# ActiveSheet.Paste

becomes

aRange.Cut aRange.Offset(3, -1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"PBnJGang" wrote in message
...
I have been able to get everything to do what I need it to do, but when I
added the Do...Until Loop it gets hung up.

Here is what I have so far:

# Dim aRange As Range
# Set aRange = ActiveCell
#
#
# 'Do Until ActiveCell.Value = ""
# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets
stuck
here!
# ActiveSheet.Paste
# ActiveCell.Copy
#
# aRange.Offset([0], [1]).Select
# Selection.End(xlDown).Offset([0], [-1]).Select
# ActiveSheet.Paste
# Application.CutCopyMode = False
# ActiveCell.Select
# Range(Selection, Selection.End(xlUp)).Select
# Selection.FillUp
# ActiveCell.Select
# Selection.End(xlDown).Select
# ActiveCell.Select
# Selection.Offset([4], [1]).Select
# 'Loop
#
# End Sub

Any suggestions??? Also, if any of this code can be cleaned up feel free
to
offer ideas!

Thanks in advance

Everything works...but can't get it to Loop
 

Jon,

Thank you very much! moving the Set statement inside the loop did the trick.
I am using the brackets to indicate a cell relative to the active cell. Is
there another/better way to do this?
I knew all of the selecting wasn't necessary, but since I'm relatively new
to vba I couldn't get it to do what I wanted any other way. I'll try what
you suggested.

Thanks again!

"Jon Peltier" wrote:

1. Why do you have brackets around the numbers in your Offsets?
2. Move "Set aRange = ActiveCell" inside the Do loop (right after Do Until)
3. You should try to adjust the code so you aren't selecting cells and
changing the selection and so forth. For example:

# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets
stuck here!
# ActiveSheet.Paste

becomes

aRange.Cut aRange.Offset(3, -1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"PBnJGang" wrote in message
...
I have been able to get everything to do what I need it to do, but when I
added the Do...Until Loop it gets hung up.

Here is what I have so far:

# Dim aRange As Range
# Set aRange = ActiveCell
#
#
# 'Do Until ActiveCell.Value = ""
# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets
stuck
here!
# ActiveSheet.Paste
# ActiveCell.Copy
#
# aRange.Offset([0], [1]).Select
# Selection.End(xlDown).Offset([0], [-1]).Select
# ActiveSheet.Paste
# Application.CutCopyMode = False
# ActiveCell.Select
# Range(Selection, Selection.End(xlUp)).Select
# Selection.FillUp
# ActiveCell.Select
# Selection.End(xlDown).Select
# ActiveCell.Select
# Selection.Offset([4], [1]).Select
# 'Loop
#
# End Sub

Any suggestions??? Also, if any of this code can be cleaned up feel free
to
offer ideas!

Thanks in advance

Everything works...but can't get it to Loop
 

I am using the brackets to indicate a cell relative to the active cell.
Is
there another/better way to do this?

Offset does this anyway, but with respect to the range it's attached to. The
brackets do nothing.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"PBnJGang" wrote in message
...
Jon,

Thank you very much! moving the Set statement inside the loop did the
trick.
I am using the brackets to indicate a cell relative to the active cell.
Is
there another/better way to do this?
I knew all of the selecting wasn't necessary, but since I'm relatively new
to vba I couldn't get it to do what I wanted any other way. I'll try what
you suggested.

Thanks again!

"Jon Peltier" wrote:

1. Why do you have brackets around the numbers in your Offsets?
2. Move "Set aRange = ActiveCell" inside the Do loop (right after Do
Until)
3. You should try to adjust the code so you aren't selecting cells and
changing the selection and so forth. For example:

# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets
stuck here!
# ActiveSheet.Paste

becomes

aRange.Cut aRange.Offset(3, -1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"PBnJGang" wrote in message
...
I have been able to get everything to do what I need it to do, but when
I
added the Do...Until Loop it gets hung up.

Here is what I have so far:

# Dim aRange As Range
# Set aRange = ActiveCell
#
#
# 'Do Until ActiveCell.Value = ""
# ActiveCell.Select
# Selection.Cut
# aRange.Offset([3], [-1]).Select 'When I add the Do Loop it gets
stuck
here!
# ActiveSheet.Paste
# ActiveCell.Copy
#
# aRange.Offset([0], [1]).Select
# Selection.End(xlDown).Offset([0], [-1]).Select
# ActiveSheet.Paste
# Application.CutCopyMode = False
# ActiveCell.Select
# Range(Selection, Selection.End(xlUp)).Select
# Selection.FillUp
# ActiveCell.Select
# Selection.End(xlDown).Select
# ActiveCell.Select
# Selection.Offset([4], [1]).Select
# 'Loop
#
# End Sub

Any suggestions??? Also, if any of this code can be cleaned up feel
free
to
offer ideas!

Thanks in advance