Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation problem
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, -- [ste] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String manipulation problem
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. -- Regards, Tom Ogilvy " wrote: 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, -- [ste] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
String manipulation | Excel Programming | |||
string manipulation | Excel Programming | |||
String Manipulation | Excel Programming |