String manipulation problem

[ste.parker]

Hi, I've been looking at manipulating data in a spreadsheet and have
quickly come to the limit of my knowledge in Excel, and I've been led
to believe that some form of VBA macro would be the best way to go.
Problem being, I don't know the first thing about Excel macros. I've
been having a search around and whilst I've got a basic understanding
of how they work now, I'e still little idea on how to proceed with what
I want to do. Anyway, I have the following data (please excuse the
formatting; if it goes wrong, each character string and each number is
in it's own cell):

aaa 1 2
bbb 3 4
ccc 5 6
ddd 7 8 9

And I want to end up with (in a seperate worksheet):

value_is(aaa),1
value_is(aaa),2
value_is(bbb),3
value_is(bbb),4
value_is(ccc),5
value_is(ccc),6
value_is(ddd),7
value_is(ddd),8
value_is(ddd),9

So basically I just want to end up with a string in each cell & each on
a seperate row, the "value_is( )," part is just text as an example of
the sort of thing I'll need to put around the data (a requirement for
use elsewhere). The number of rows in the initial worksheet is
changeable, as is the number of columns which contain the numbers
(empty cells are valid, but unwanted in the final result). I was
expecting each xxx/nnn combination per row which I could have just
concatenated easily enough, but unfortunately it's no longer the case.

Any help/pointers would be greatly appreciated.

cheers,

 

[Tom Ogilvy]

Sub ProcessData()
Dim sh as worksheet, sh1 as worksheet
Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range

Dim i1 as Long

set sh = Activesheet
set sh1 = Worksheets.Add(After:=Worksheets(worksheets.count))
i1 = 1
set rng = sh.Range(sh.Cells(1,1),sh.Cells(rows.count,1).End(xlup))
for each cell in rng
set rng1 = sh.range(cell.offset(0,1),sh.cells(cell.row,"IV").end(xltoLeft))
for each cell1 in rng1
if cell1 <> "" then
'
' values in columns A and B
'
sh.cells(i1,1) = "value_is(" & cell.value & ")"
sh.cells(i1,2) = cell1.value
'
' or if you want just a string value in column A
' sh.cells(i,1) = "value_is(" & cell.value & ")," & cell1.value
'
i1 = i1 + 1
end if
Next
Next
end sub

Make your data sheet the active sheet, then run the macro.