PDA

View Full Version : Solved: Cut and Paste to different cell



julesfromthb
11-10-2011, 05:20 PM
Hi! Please help with this problem: I have a sheet with five columns in col B I have cells with both text and numbers at varying intervals. I want to run a macro that will find each number then cut and paste it one row above and two columns over. Heres what I have:


Sub newtry()

Range("B2").Select

Dim testvar As Object
Set testvar = Range("B2")
Dim numericcheck As Boolean
numericcheck = IsNumeric(testvar)

Do Until IsEmpty(ActiveCell.Offset(0, 3))
If numericcheck = False Then
testvar.Select.Cut.Offset(-1, 2).Paste
ActiveCell.Offset(2, -2).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop



End Sub

Kenneth Hobs
11-10-2011, 06:14 PM
Welcome to the forum! When pasting code, please paste between VBA code tags.

Select and Activate are often not needed and can slow your code execution. Even the cut/copy will do that so it slows down, see my speed routines in the kb.

Sub newtry()
Dim r As Range, c As Range
Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each c In r
If IsNumeric(c) Then c.Cut c.Offset(-1, 2)
Next c
End Sub

julesfromthb
11-10-2011, 07:12 PM
Wow! That was so seamless. New to VBA and the forum, I'll be sure post correctly next time. Thank you for a fast and perfect response!