Adding data to alternate rows

robcbaker

New Member
Joined
Jul 29, 2010
Messages
13
My question may be pretty simple, complex, or impossible.
First I'm not very familiar with Excel
Heres my question:
I have about 300 rows of numbers in 1 column (A) on Excel 2007
Looks like:
0A08EC
0A0902
0A52A6
0A52BA
0A52D0
I need to insert the following between each number and aside from copy and pasting I have no idea how to do this:
(blank)
M
82
(blank)
(blank)
(blank)
X

Any help will be greatly appreciated thanks.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
How are you deriving the values that have to be inserted?
 

robcbaker

New Member
Joined
Jul 29, 2010
Messages
13
They are commands that go into a software program that I run. I'm trying to make a sheet that I can copy and paste into the software so each of the 300 accounts are so will be updated instead of me manually updating each one.
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
A bit rough and ready, but if you have the numbers in column A and the values you need to insert in column B, the code should 'riffle' the data into column C

Code:
Sub riffle()
    lr = WorksheetFunction.Max( _
        Cells(Rows.Count, "A").End(xlUp).Row, _
        Cells(Rows.Count, "B").End(xlUp).Row)
    r = 1
    For i = 1 To lr
        For j = 0 To 1
            Cells(r + j, "C").Value = _
                Cells(i, j + 1).Value
        Next j
        r = r + 2
    Next i
End Sub
HTH
 

robcbaker

New Member
Joined
Jul 29, 2010
Messages
13

ADVERTISEMENT

Thank you, this macro works great for what I asked for:). However after rereading my question it doesn't appear to be quite clear enough (sorry) the end result I am looking for is:

COLUMNS
A B C

1 A 1
2 B A
3 C B
D C
E D
F E
G F
G
2
A
B
C
D
E
F
G
3
A
B
C
D
E
F
G

As you can see the information in Column B (A-G) will be applied after each individual cell from Column A all compiled in Column C.
 
Last edited:

robcbaker

New Member
Joined
Jul 29, 2010
Messages
13
I couldn't get the information to go in correctly should be:

Column A:
1
2
3

Column B:
A
B
C
D
E
F
G

Column C:
1
A
B
C
D
E
F
G
2
A
B
C
D
E
F
G
3
A
B
C
D
E
F
G
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196

ADVERTISEMENT

Okay, try this then:

Code:
Sub newRiffle()
    Dim lrA, lrB
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
    r = 1
    For a = 1 To lrA
        Cells(r, "C").Value = Cells(a, "A").Value
        r = r + 1
        For b = 1 To lrB
            Cells(r, "C").Value = Cells(b, "B").Value
            r = r + 1
        Next b
    Next a
End Sub
 

robcbaker

New Member
Joined
Jul 29, 2010
Messages
13
WOW!!!
Your a genius, you have no idea how much time this saves me thank you so much for your help on this.....Holy cow I still cant believe it.:biggrin::biggrin::biggrin::biggrin:
 

robcbaker

New Member
Joined
Jul 29, 2010
Messages
13
The last macro worked great, I tried to use it for another worksheet but it doesn't like it I think it is because there are more rows in column B now I tried to figure it out but couldn't can you let me know how to make this work please thank you so much.
Column A:
1
2

Column B:
A
B
C
D
E
F
G
H
I
J
K

Column C:
1
A
B
C
D
E
F
G
H
I
J
K
2
A
B
C
D
E
F
G
H
I
J
K
(etc.)
 

Forum statistics

Threads
1,141,618
Messages
5,707,430
Members
421,508
Latest member
someinternetuser

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