VB Help.

[sydinstaller]

Hi,

I am new to VB. This is really the first time I have looked into using it so please excuse my complete lack of understanding.

So here is my question.

I have a line of numbers in one cell (B2). They look like this;

1, 21, 21, 94, 64, 2, 213

These numbers are currently in Decimal format (more on that later).

I need to "split" these numbers into individual cells and drop the comma. It would look like this;

B3 B4 B5 B6 B7 B8 B9
1 21 21 94 64 2 213

I know I can use the "text to column" function but I was hoping for a VB script to do this.

The second part is I then need to convert the numbers from decimal to Hex and add a "0" to the front of any single digit numbers.

I can do this part using standard excel formulas with no problems but if it could be added to the script then that would be great.

The final thing needs to look like this;

B3 B4 B5 B6 B7 B8 B9
01 15 15 5E 40 02 D5


Any help would be greatly appreciated.

Regards
Daniel.

[shg]

Moved to Programming forum.

Please read our Forum Rules about thread titles, and then edit yours to be descriptive of your problem.

[piyushdabomb]

This procedure works if all your strings with commas are in column "A"

Please Login or Register  to view this content.

[shg]

piyushdabomb,

You did it again

Please Login or Register  to view this content.

[shg]

Here's another way:

Please Login or Register  to view this content.

For your example:

Please Login or Register  to view this content.

[piyushdabomb]

I know...I'm lazy .

shg: How do I view all questions unanswered? I want to work on as many as I can before I sleep.

[piyushdabomb]

I like the split function. Thanks for the advice!

[shg]

Please Login or Register  to view this content.

I don't care if you're lazy, but I'd rather not have to follow you around correcting the same mistake so our other members aren't misled.

[sydinstaller]

Hi piyushdabomb,

Thanks for the quick reply.

When I run this script i get the following error;

run-time error 1004
Application-defined or object-defined error


And the debug shows this line;

Please Login or Register  to view this content.

********
SHG,

When I run your string i get the same error but debug shows;

Please Login or Register  to view this content.

Any suggestions?

Thanks again both of you.
Daniel.

[shg]

SHG,

When I run your string i get the same error but debug shows;

Please Login or Register  to view this content.

That can only be because astr is not an array, and that would only occur if there is not a comma-delimited string in B2 as promised.

Singe-step through the code and see what's going on.

[royUK]

I know...I'm lazy .

Laziness=inefficient code.

It's not a good idea to pass bad habits like not declaring variables correctly on to learners. As r1 & r2 are wrongly interpreted as Variants the code will be slower.