Script that search a sheet for a cell containing specific text, and replace the text with a specific value.

[RedHawkDk]

Hi

I wan't to split a cell with data, into other cells, based on the first data in the original cell. The data from the original cell looks like this (all in the same cell):

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Field95-[COLOR=#800000]4[/COLOR],Field97-[COLOR=#800000]4[/COLOR],Field98-[COLOR=#800000]0[/COLOR],Field100-[COLOR=#800000]2[/COLOR],Field103-[COLOR=#800000]0[/COLOR],Field105-[COLOR=#800000]3[/COLOR],Field107-[COLOR=#800000]4[/COLOR],Field109-[COLOR=#800000]4[/COLOR],Field110-[COLOR=#800000]2[/COLOR],Field111-[COLOR=#800000]0[/COLOR],Field112-[COLOR=#800000]0[/COLOR],Field113-[COLOR=#800000]192[/COLOR],Field114-[COLOR=#800000]87[/COLOR],Field115-[COLOR=#800000]0[/COLOR],Field116-[COLOR=#800000]0[/COLOR],Field117-[COLOR=#800000]60[/COLOR]</code>

It should be split by "," and I found out that I could do that with a script:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">[COLOR=#00008B]Private[/COLOR] [COLOR=#00008B]Sub[/COLOR] CommandButton1_Click()
[COLOR=#00008B]Dim[/COLOR] X [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Variant
[/COLOR]X = Split(Range([COLOR=#800000]"A1"[/COLOR]).Value, [COLOR=#800000]","[/COLOR])
Range([COLOR=#800000]"A1"[/COLOR]).Resize(UBound(X) - LBound(X) + [COLOR=#800000]1[/COLOR]).Value = Application.Transpose(X)
[COLOR=#00008B]End[/COLOR] [COLOR=#00008B]Sub
[/COLOR]</code>

Then the result is like this:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Field95-[COLOR=#800000]4
[/COLOR]Field97-[COLOR=#800000]4
[/COLOR]Field98-[COLOR=#800000]0
[/COLOR]Field100-[COLOR=#800000]2
[/COLOR]Field103-[COLOR=#800000]0
[/COLOR]Field105-[COLOR=#800000]3
[/COLOR]Field107-[COLOR=#800000]4
[/COLOR]Field109-[COLOR=#800000]4
[/COLOR]Field110-[COLOR=#800000]2
[/COLOR]Field111-[COLOR=#800000]0
[/COLOR]Field112-[COLOR=#800000]0
[/COLOR]Field113-[COLOR=#800000]192
[/COLOR]Field114-[COLOR=#800000]87
[/COLOR]Field115-[COLOR=#800000]0
[/COLOR]Field116-[COLOR=#800000]0
[/COLOR]Field117-[COLOR=#800000]60[/COLOR]</code>

Now I have a sheet with a column with rows containing Field 1 to Field120.

My question is then, how can I script it, so it will search the entire rows with the content Field1 or Field 2 etc. and fill the row with the number. Like Field95 has number 4 etc.

This is an excel file I uploaded, so you can see what my question is about. link

Please tell me if you need more information or if i'm not asking clearly enough.

Thanks in advance !

: )

 

[oldbrewer]

in a macro find the length of the cell and the position of the "-"
say length = 11 and "-" position = 9 11-9 = 2 =right(cells(1,1),2 gives what you want

 

[RedHawkDk]

in a macro find the length of the cell and the position of the "-"
say length = 11 and "-" position = 9 11-9 = 2 =right(cells(1,1),2 gives what you want

Hi oldbrewer. Thanks for replying.

I found a script to split the first cell with ',' the problem is how to search the sheet and then replace.

I am totally new to scripting, so I don't know how write the script from start. Thought there was some kind of base script / technique that could do the trick?

 

[RedHawkDk]

Maybe this, combined with an IF statement could be used?

Sub UpdateWhole()
With ActiveSheet.UsedRange
.Replace "A1", "System", xlWhole
.Replace "A2", "System", xlWhole
.Replace "A3", "System", xlWhole
.Replace "B1", "ACC", xlWhole
.Replace "B2", "ACC", xlWhole
End With
End Sub

I don't know if that's an option?