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
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