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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,446
Office Version
  1. 2013
Platform
  1. Windows
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.
 

paulmc1981

New Member
Joined
Jan 16, 2017
Messages
9
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).
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
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:

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
my pleasure, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,788
Messages
5,626,890
Members
416,208
Latest member
tan21

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
Top