Macro Button to format cells

[jimmyfinch]

Hi, i have an excel document which can be 1000's of rows long with the following example values in it...

Item Code QTY
0200109002/009/026/072/202 1
0200109074/012/014 23
200109047 22
0.20.0109262 (red) 21
0200147024 / 29 34
0200147029/040 4
02001.09724/729/706 9
0200109262 (blue) 56
0200109709/701 4
200101247 2
200 101 424 1
200101402 1
200101424 3
26679 D 2
29200-64090-9 43 34
292020G020 22
2T24-01020-C4A 12

I want to be able to run through the whole sheet in on operation and do the following...

a) every value that has information in brackets; have the bracketed information moved into another column.

b)If a value is like 0200109074/012/014 then the first column will be formatted just to display 0200109074 then the other values are put into a new column like... 0200109012/014

c) remove all the dashes, spaces, slashes, full stops etc...

d) If there is anything that the computer can not format, they are highlighted or flagged for manual attention.

Please can someone help me figure this one out as this takes hours each day manually formating/copying test around. If 99% of the work can be easily done by computer and the remaining 1% done myself it will really help.

Kindest thanks,

Jim

 

[AnotherHiggins]

b)If a value is like 0200109074/012/014 then the first column will be formatted just to display 0200109074 then the other values are put into a new column like... 0200109012/014

I'm not clear on what you want to do here.

I think it would be easier for everyone to understand if you post the expected results for that same sample data. NOTE: You can wrap your example data in [ignore][tt][/tt][/ignore] tags to have it display correctly when posted.

Also, I'd suggest that you turn on your macro recorder (Tools > Macro > Record New Macro) and step through some of the corrections you're after. Observe the recorded code by going to the VBEditor ([Ctrl]+[F11]) and paste it here in [ignore]

[/ignore] tags. This will provide a starting point.

*Click on the Process TGML link under the posting window for more info on using tags.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jimmyfinch]

Thanks for replying. Never done this before. Will post some results so you can see what im after.

many thanks,

Jim

 

[jimmyfinch]

Hi, here is my original data example...

[tt]
Item Code QTY
0200109002/009/026/072/202 1
0200109074/012/014 23
200109047 22
0.20.0109262 (red) 21
0200147024 / 29 34
0200147029/040 4
02001.09724/729/706 9
0200109262 (blue) 56
0200109709/701 4
200101247 2
200 101 424 1
200101402 1
200101424 3
26679 D 2
29200-64090-9 43 34
292020G020 22
2T24-01020-C4A 12
[/tt]

This is how i would like it to look when complete...

[tt]
Item Code QTY Notes
0200109002 1 0200109009/026/072/202
0200109074 23 0200109012/014
200109047 22
0200109262 21 (Red)
0200147024 34 0200147029
0200147029 4 0200147040
0200109724 9 0200109729/706
0200109262 56 (Blue)
0200109709 4 0200109701
200101247 2
200101424 1
200101402 1
200101424 3
26679D 2
2920064090943 34
292020G020 22
2T2401020C4A 12
[/tt]

Hope this helps!

Kindest regards,

Jim

 

[Bong]

First, I would do all the removals. Let's say you're on row, rw:
strA=replace(cells(rw,1)," ","") 'get rid of spaces
strA=replace(strA,"-","") 'get rid of dashes
strA=replace(strA,".","") 'get rid of periods
...etc

Then you want to see if there's a parenthesis, "(":
intX=instr(strA,"(")
Now if intX>1 put only the substring up to the "(" back in the cell:
cells(rw,1)=left(strA,intX-1)
and the rest in column 3:
cells(rw,3)=right(strA,len(strA)-intX+1)

maybe you'd like to try the rest of the logic and see what you get?


_________________
Bob Rashkin

 

[AnotherHiggins]

Hey, Jim - just looked back at this.

One this still has me confused. Is there a typo on the first line? If not, can you explain the logic of getting from
[tt]
[tab]0200109002/009/026/072/202[/tt]
to[tt]
[tab]0200109009/026/072/202[/tt]
in the notes?

This part:[tt]
[tab]0200109002[/tt]
makes sense to me because it matches everything up to the first slash in the original cell. But I need some help understanding how you got to the part in the notes column.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jimmyfinch]

Hi, this is not a typeo..

[tt]
0200109002/009/026/072/202
to
0200109009/026/072/202
[/tt]

Basically this is what will happen..

[tt]
Original Value: 0200109002/009/026/072/202

After the macro has been run, the original cell will display all the values up to the "/" which will be 0200109002

The cell next to this will display all the values to the left of the "/" but replacing the last x digits with what is between the two "/" "/" in this case it is 009. Then the remaining chars will be added to the end. So the value will look like...

0200109009/026/072/202
[/tt]

Hope this helps!

Many thanks,

Jim

 

[jimmyfinch]

Hi, i have been playing with moving any infotmation in brackets to a new field but cant quite get it working for the entire column. It just moves everything. I just want the information in the brackets moved to a new column and leave the other information alone.

Here is my code..

[tt]

Sub MoveBracketData()
With [A1]
If .Value Like "*(*" Then
[B1].Value = .Value
End If
End With
End Sub
[/tt]

Many thanks,

Jim

 

[ck1999]

Your code is only seeing if the cell contains a bracket then copy a1 into b1 if a1 contains a bracket. It copies all the info in A1.

ReRead Bong message!!

Put his code
intX=instr(strA,"(")
Now if intX>1 put only the substring up to the "(" back in the cell:
cells(rw,1)=left(strA,intX-1)
and the rest in column 3:
cells(rw,3)=right(strA,len(strA)-intX+1)

in your if then statement
Based on your info in the last post
strA should equal your A1.value
rw=1

ck1999