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.

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" wrote in message
ups.com:

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.

Hi Jim,

thanks for your quick reply. That did the trick !

Thanks!

Mark

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"

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


"Ixtreme" wrote:


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"

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)

"Ixtreme" wrote:


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"

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" wrote:

Hi Jim,

thanks for your quick reply. That did the trick !

Thanks!

Mark