Need Help in VBA !!

[simplyeshu]

Hello Frens,

I am new to VBA programming. So can you please help me.

I have a excel of 4 columns and rows can be anything from 100 to 500.

I want to create a small vba code which should insert a record when cell a2=a1 is false and then copy a2 to b2 column.

Ex:

Columns: a b c d
a1 Excel Sheet
a2 Hero Bestbuy Amazon Lifestyle
a3 Hero Bestbuy Dallas Shopping
a4 Heroine Outright Sydney Sports
a5 Villain Direct Los angels Lifestyle

I want a new row to be created after a1 as a2=a1 is false and Hero word to be copied in b2 after creating a new row. Similarly a new row after a3 and a4 as the condition is false

Can anyone help in providing me VBA code for the same?

 

[simplyeshu]

Hello VOG,

Can you please help me with the below:

My workbook has empty rows A1 and A3. Can you please help in getting the correct output..

A2 has heading and from A4 data is starting

Present output is:
0
books1 books2 books3
0
0
Hero
Bestbuy amazon lifestyle
Bestbuy amazon1 lifestyle1
Bestbuy amazon2 lifestyle2
Rambo
Sale Discount Shopping
Sale Discount1 Shopping1
Sale Discount2 Shopping2
Sale Discount3 Shopping3
Sale Discount4 Shopping4

Desired output should be:

books1 books2 books3

Hero
Bestbuy amazon lifestyle
Bestbuy amazon1 lifestyle1
Bestbuy amazon2 lifestyle2
Rambo
Sale Discount Shopping
Sale Discount1 Shopping1
Sale Discount2 Shopping2
Sale Discount3 Shopping3
Sale Discount4 Shopping4

 

[VoG]

Try

Sub InsRowVal()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 5 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B4:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C[-1]"
    .Value = .Value
End With
Columns("A").Delete
End Sub

 

[simplyeshu]

Hello fren,

Its working but one of the field (Hero) is missing:

books1 books2 books3
Bestbuy amazon lifestyle
Bestbuy amazon1 lifestyle1
Bestbuy amazon2 lifestyle2
Rambo
Sale Discount Shopping
Sale Discount1 Shopping1
Sale Discount2 Shopping2
Sale Discount3 Shopping3
Sale Discount4 Shopping4

Desired output:
books1 books2 books3
Hero
Bestbuy amazon lifestyle
Bestbuy amazon1 lifestyle1
Bestbuy amazon2 lifestyle2
Rambo
Sale Discount Shopping
Sale Discount1 Shopping1
Sale Discount2 Shopping2
Sale Discount3 Shopping3
Sale Discount4 Shopping4

 

[simplyeshu]

Its working fren... I have changed the cell value to B3.

Sub InsRowVal()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 5 Step -1
If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B3:B" & LR)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C[-1]"
.Value = .Value
End With
Columns("A").Delete
End Sub

Thank you very much