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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,501
Messages
5,511,689
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top