Change 'If Not IsEmpty' into a loop

paulmc1981

New Member
Joined
Jan 16, 2017
Messages
9
Hi,

I am a complete novice to VBA and I have created some code below to format some data. I need it to LOOP if the 'ActiveCell.Offset(1, -6)' is not empty. Please can you help?


Sub bSetupDate()


If Not IsEmpty(ActiveCell.Offset(1, -6)) Then
ActiveCell.Offset(1, -6).Select
Selection.Cut
ActiveCell.Offset(-1, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],6,LEN(RC[-2])-4)"
ActiveCell.Offset(1, 1).Select
Selection.Cut
ActiveCell.Offset(-1, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 6).Range("A1").Select


End If


End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You say your a novice to Vba but then instead of saying what your wanting to do you show us a script and want us to add a loop to it.

Please tell us what you have now and what your ultimate goal is.

And then we will write a script to do what you want. And we never use active cell is these type scripts.
 
Upvote 0
My ultimate goal is to strighten up this data:

HORSE / RACETRAINER / JOCKEYPRICE
Alfred Oats R Goldie Bet 3656/1
3:15 AyrB Hughes
Alinstante Sir Mark Prescott Bet 36511/8
4:30 WolverhamptonL Morris
Alottarain J W Mullins Bet 36512/1
2:00 PlumptonDaniel Sansom
Ambitious Boy J G M OʼShea William Hill25/1



<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

As you will see the Horse/Race and Jockey/Trainer are originally shown on 2 rows and I want to normalize the data (with the specified gaps in my above code).
 
Upvote 0
While I wouldn't question the previous posts comments about style (using activecell can lead to uncertain behaviour!) I would offer the following amendment to your code (note this is untested, but should work):

Code:
Sub bSetupDate()
Dim n As Integer
n = 0
While Not IsEmpty(ActiveCell.Offset(1, -6))
    ActiveCell.Offset(1, -6).Select
    Selection.Cut
    ActiveCell.Offset(-1, 1).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-2],6,LEN(RC[-2])-4)"
    ActiveCell.Offset(1, 1).Select
    Selection.Cut
    ActiveCell.Offset(-1, 1).Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Offset(0, 6).Range("A1").Select
    
    ' not having tested the above, the code below ensures that you don't get stuck in an endless loop;
    n = n + 1
    If n = 1001 Then
        MsgBox "Subroutine interations exceed 1000 so process halted"
        Exit Sub
    End If
        
Wend
End Sub

PS. Note the indentation of lines between grouping statements such as IF/ENDIF and WHILE/WEND - it makes the code much easier to read.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top