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

[PBnJGang]

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

 

[Jon Peltier]

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
_______

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

 

[Jon Peltier]

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
_______

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!

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
_______

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