Adding in Information in the middle and end of the string

[RaydenUK]

I have a column labeled with patient ID's and I need to add in a few quality control checkpoints. For example:

Column A
12134
12355
24345
13523
12312
14321

I would like to add into the data a DOA2 in the center and a DOA3 at the end to look like this:

Column A
12134
12355
24345
DOA2
13523
12312
14321
DOA3

What code would allow me to do this?

 

[JoeMo]

Assuming the IDs begin in cell A2:

Sub CenterAndEnd()
'Assume IDs start in A2
Dim lR As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lR + 1).Value = "DOA3"
With Range("A" & Int(lR / 2) + 2)
    .Insert shift:=xlDown
    .Offset(-1, 0).Value = "DOA2"
End With
End Sub

 

[alansidman]

Try this macro

Option Explicit


Sub AddControl()
Dim lr As Long
Dim x As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
x = lr / 2


Range("A" & x) = "DOA2"
Range("A" & lr + 1) = "DOA3"


End Sub

 

[RaydenUK]

Wow thanks guys. I didn't think of it before but how about it there is respective data in column B that needs to stay with column A.

 

[JoeMo]

Wow thanks guys. I didn't think of it before but how about it there is respective data in column B that needs to stay with column A.

Change this line:


.Insert shift:=xlDown

To this:

.EntireRow.Insert
</pre>