Copy range

[Ixtreme]

I have a list in excel that is automatically populated. In column A I
have values like:

A1: Test_2006_17_1
A2: _2
A3: _3
A4: _4
A5: Test_2006_18_1
A6 _2
A7 _4
A8 _5
A9 Test_2006_19_2
A10: _7
A11: _9

1.What I need is a vb script that runs through the values in columns A
2.Looks for a value that does not start with "Test" (in this case the
first value to be found will be _2), goes 1 cell up until the cell is
found which starts with Test
3. change the value (in this case _2) with the value that has been
found in step 2 while deleting the last 2 positions (in this case _1).
The result in cell A2 will be Test_2006_17_2, in cell A3 Test_2006_17_3
etc.

Should be simple, but my vba knowlegde does not go that far
unfortunately.

 

[JMay]

Give this a try:

Sub Foo()
Dim Temp As String
Range("A1").CurrentRegion.Select
For Each c In Selection
If Left(c, 4) = "Test" Then
Temp = Left(c, 12)
End If
If Left(c, 1) = "_" Then
c.Value = Temp & c
End If
Next c
End Sub

HTH,
Jim May

 

[Ixtreme]

Hi Jim,

thanks for your quick reply. That did the trick !

Thanks!

Mark

 

[Guest]

Hi Ixtreme,
Jim's reply will work in all cases where the number xx in Test_2006_xx_1 is
a two digit number. It does not work if the number is a single digit or a
three digit number
I would suggest replacing the following line
Temp = Left(c, 12)
with
Temp = Left(c, InStrRev(c, "_") - 1)

 

[Ixtreme]

I have another vba question: In column A there are rows that are
populated and rows that are not.

I want the empty cells to look up and copy the first non empty cell
value to the specific cell.

A1: Test
A2:
A3
A4
A5 Bla Bla
A6
A7

The code should give: A2, A3 and A4 to be "Test" as well and A6 and A7
to be "BLa Bla"

 

[Guest]

You need to modify the function a little bit to do what you want
Try this.
Sub Foo2()
Dim Temp As String
Dim c As Range

'Change A50 to whatever..
For Each c In Range("A1:A50")
If c.Value <> "" Then
Temp = c.Value
End If
If c.Value = "" Then
c.Value = Temp
End If
Next c
End Sub

 

[Guest]

There is anothe way of doing what you want. It uses the formulas.

1. Highlight the range of cells Including blank cells. For example highlight
A1:A50
2. Select all blank cells by F5(Goto)/Special../Blanks
3. Now consider the first blank cell. If the first blank cell is A2 then
type =A1, if the first blank cell is A3 then type =A2 etc.
4. Instead of pressing Enter, press Control-Enter. Control Enter places
similar formula in all cells (that is it adjust the formula appropriately)