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?

 

[VoG]

Try this with a copy of your sheet

Sub InsRowVal()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 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("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C[-1]"
    .Value = .Value
End With
End Sub

 

[simplyeshu]

Thank you very much but there is a small difference in output:

Present output is

<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">Books</td> <td class="xl63" style="width: 48pt;" width="64">books1</td> <td class="xl63" style="width: 48pt;" width="64">books2</td> <td class="xl63" style="width: 48pt;" width="64">books3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">
</td> <td class="xl63">Books</td> <td class="xl63">
</td> <td class="xl63">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hero</td> <td>Bestbuy</td> <td>amazon</td> <td>lifestyle</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hero</td> <td>Bestbuy</td> <td>amazon1</td> <td>lifestyle1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hero</td> <td>Bestbuy</td> <td>amazon2</td> <td>lifestyle2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Hero</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>Sale</td> <td>Discount</td> <td>Shopping</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>Sale</td> <td>Discount1</td> <td>Shopping1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>Sale</td> <td>Discount2</td> <td>Shopping2</td> </tr> </tbody></table>
Where as the output should be:

<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Books</td> <td class="xl65" style="width: 48pt;" width="64">books1</td> <td class="xl65" style="width: 48pt;" width="64">books2</td> <td class="xl65" style="width: 48pt;" width="64">books3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">
</td> <td class="xl65">Hero</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hero</td> <td>Bestbuy</td> <td>amazon</td> <td>lifestyle</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hero</td> <td>Bestbuy</td> <td>amazon1</td> <td>lifestyle1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Hero</td> <td>Bestbuy</td> <td>amazon2</td> <td>lifestyle2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Rambo</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo
</td> <td>Sale</td> <td>Discount</td> <td>Shopping</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>Sale</td> <td>Discount1</td> <td>Shopping1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>Sale</td> <td>Discount2</td> <td>Shopping2</td> </tr> </tbody></table>

 

[VoG]

Try

Sub InsRowVal()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 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("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C[-1]"
    .Value = .Value
End With
End Sub

 

[simplyeshu]

Pefectly working my fren... Is it possible to delete first column after getting the result..

I mean something like

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">books1</td> <td class="xl65" style="width: 48pt;" width="64">books2</td> <td class="xl65" style="width: 48pt;" width="64">books3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Hero</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon</td> <td>lifestyle</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon1</td> <td>lifestyle1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon2</td> <td>lifestyle2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount</td> <td>Shopping</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount1</td> <td>Shopping1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount2</td> <td>Shopping2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount3</td> <td>Shopping3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount4</td> <td>Shopping4</td> </tr> </tbody></table>

 

[VoG]

Try

Sub InsRowVal()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 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("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C[-1]"
    .Value = .Value
End With
Columns("A").Delete
End Sub

 

[simplyeshu]

Its working fren but output is something like (two zeros are appearing)


<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">books1</td> <td class="xl65" style="width: 48pt;" width="64">books2</td> <td class="xl65" style="width: 48pt;" width="64">books3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">0</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">0</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Hero</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon</td> <td>lifestyle</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon1</td> <td>lifestyle1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon2</td> <td>lifestyle2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon3</td> <td>lifestyle3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Bestbuy</td> <td>amazon4</td> <td>lifestyle4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Rambo</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount</td> <td>Shopping</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount1</td> <td>Shopping1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount2</td> <td>Shopping2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount3</td> <td>Shopping3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Sale</td> <td>Discount4</td> <td>Shopping4</td> </tr> </tbody></table>

 

[VoG]

Do you have blank cells in your data?

 

[simplyeshu]

I am sorry Brother...its working fine

Thanks a ton

You are simply genius... Can you refer me any book for dummies to learn VBA programming?

 

[VoG]

Take a look at Hiker's List http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5