Read Data 2

[komark]

Hi,
I need to write a vba macro that would read the data from a certain column and store it in a variable and add 4 to it.

For Example: if I have 11 in that column than it should add 4 and show 15.
Likewise if I have something like 20FF+20d. the macro should start reading that and when it hits the word (F) right after the integer (20) it should stop. Store that value 20 and add 4 to it. Then is should represent 24FF+20d.

another example: 9SF+1d should change to 13SF+1d.

any ideas..anyone?

 

[komark]

PHV,
Actually I have what you posted earlier
a(i) = Replace(a(i), Val(a(i)), 4 + Val(a(i)), 1, 1)

It was a typo that got posted. But still its not working. After the fifth number, i get 0's

 

[komark]

PHV,
I have found the problem. In excel it was formatting the cell in Numbers. And since each comma represent 1000 - it was going out of range and putting in the last numbers after the fifth to be zeros.

So I have to format the cell in text first and then run the code.

Thanks for your help and prompt reply

 

[komark]

PHV,
I just noticed that the program is not doing what I wanted.

20FF+20d,102,103,104,105,15SF+1d
should change to
24FF+20d,106,107,108,109,19SF+1d

 

[PHV]

Works for me:
s="20FF+20d,102,103,104,105,15SF+1d"
a=Split(s, ",")
for i=0 to ubound(a): a(i) = Replace(a(i), Val(a(i)), 4+Val(a(i)), 1, 1): next
? Join(a, ",")
24FF+20d,106,107,108,109,19SF+1d

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

PHV,
Have you tried that in excel?

 

[PHV]

Are the Cells(x, "F").Value different from the Cells(x, "F").Text

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbap]

I like the Split and Join functions. I had not seen them before. Kewl.

 

[komark]

Hey guys...I found my own stupid mistake. Promise wont bug anymore..Its working.