Solved: If/then help

[anmlhse]

Hello to all!
I need to write a macro that will insert a 0 (zero) in column B on a new blank line, when column A has a 1, and then followed by another 1, or number. The data was given with a comma after the number. Also the zero in "data to be expected" should be in Column B and not A. Sorry about that.

Current data:
1, 575011787
1, 575011788
1, 575011789
2, 575011790
3, 575011791
4, 575011792
1, 575011793
2, 575011794
1, 575011795

Data to be expected:
1, 575011787

0
1, 575011788
0
1, 575011789
2, 575011790
3, 575011791
4, 575011792
0
1, 575011793
2, 575011794
0
1, 575011795
0

Does this make sense? Not sure if this can even be done. It's given me way to many headaches in thinking of a solution. Any help is greatly appreciated. Thank You!

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim Insertrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Insertrow = Lastrow
For i = Lastrow To 1 Step -1

If Left$(Cells(i, "A").Value2, 1) = 1 Then

.Rows(Insertrow + 1).Insert
.Cells(Insertrow + 1, "B").Value = 0
Insertrow = i - 1
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Chabu]

Hello,

I can't make sense of your example .
the third data row in your expected result should be followed by a 0 no?

[Aussiebear]

Please attach a sample workbook by clicking on Go Advanced, scroll down to Manage Attachments and follow the prompts from there.

[Aussiebear]

Chabu, I believe that anmlhse is looking for a solution where if the data has consecutive numbers starting with a 1, that a Zero is inserted, and also where the numbers fail to be increasing, then a zero is inserted.

[anmlhse]

Correct on a line by itself in Column B.

Thank you everyone for taking the time to review this. I truly appreciate it!

[anmlhse]

Aussiebear - You are correct. If the number does increment by 1 then a line needs to be inserted under 1 with a 0 in Column B. Thank you for explaining my nonsense. LOL. :thumb

[anmlhse]

Correction. If the number does NOT increase by 1. My applogies.

[macropod]

Try:Public Sub ProcessData()
Application.ScreenUpdating = False
Dim i As Long, j As Long
With ActiveSheet
j = .Cells(.Rows.Count, "A").End(xlUp).Row
If Cells(j, "A").Value = 1 Then
.Cells(j + 1, "A").Value = 0
End If
For i = j To 2 Step -1
If Cells(i, "A").Value = 1 Then
If Cells(i - 1, "A").Value <> 0 Then
.Rows(i).Insert
.Cells(i, "A").Value = 0
End If
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[anmlhse]

Macropad - Thank You as it worked like a charm!!! You are great!


Thank you everyone! Another new learning experience...I love it!