VBA Mid function: One sources to multiple cells

Hi all,

I would like to know how to create a VBA that can do the above.

Here is an example:

The data before extract is "a=1,995.000 b=2,001.000 c=1,994.000 d=1,996.000 e=1,281".

Need to extract number only to each cell, 1995 to column 1, 2001 to column 2 and so on.

Please check the attachment out.midmultiplecell.xlsx

Thank you.

Try the attached.

Mr.Shorty

Thank you for your advice. I thought mid is the way but I was wrong.

You need to change H to K. Please see attached comment.

Originally Posted by AB33

You need to change H to K. Please see attached comment.

Have changed H to K but error.

I have notice that there are some changes to the code. If the code is mean for when = is f, it is not working. The number with "f" still skipped.

Can you explain the procedure of your code?

I have added a line to exclude a null string value. Could you please attach a sample which showing you the wrong result? I need to see what is going on. The code worked on the sample.

I have added macro splitmeupK.

Please check the attachment if it is right.

midmultiplecell2.xlsm

David,
I have got the same out put as per attached, so I do not see what was wrong with the first code.
Am I missing something?

Okay,
I have now added another (New) code which is probably what you wanted. Try it

Pl see attached file with MID function.

Tried your new code. It work and I have amended to my need.

However, I notice one thing. The code run from the first row of the data to the last row.

Is there a way to code just for one row at a times?

Well, the code runs for rows which data in column H. You have data in 3 rows, the code will exit once it has reached row 3.
It loops from row 1 to row 3.
If you want to fix the loop

Please Login or Register  to view this content.

runs from J2 to J3.

hi DavidRoger, option, please check attachment, press Run button or run code "test"(ALT+F8, select "test", press Run)

Originally Posted by AB33

Please Login or Register  to view this content.

runs from J2 to J3.

Tried (K4:K10) and tested but doesn't work. It does not stop at K10. Attached is the amended copy. Could be I miss something.

For my case, there is no need for loop. Just need to split at one row when I paste data in the column K.

Should be

Please Login or Register  to view this content.

Which means from K4 to K10

Below is referring to the attachment

Referring to your code,

Please Login or Register  to view this content.

.

I keep the front part

Please Login or Register  to view this content.

and deleted the back part

Please Login or Register  to view this content.

. But the code has to change every time new data is insert, K4 to K5 and so on.

Need something like

Please Login or Register  to view this content.

which doesn't work.

Please Login or Register  to view this content.

refers to cell K3 in the Excel. Cell K3 is formulated so the code work on the last data I key in.

For the attachment, I have change K4 to K10 for testing.

David,
I have spent over 3 days on this thread.
As I said, you have 2 options with range. You can have fixed range
For Each r In Range("k4:k10")
which means the loop is fixed . It goes from row 4 to row 10.
The second option is variable range
For Each r In Range("K4", Range("K" & Rows.Count).End(xlUp))
it goes down from row 4 to the last row in column K. The last row in column K could be in row 10, 20 , 40 or 1m.
If you are struggling with basic loop, I do not know how are you going to adjust the rest of the code.

Hi AB33,

I have to thank you for your code and your every patient. I know I am asking the impossible. But I just have to know.

After a day of studying your new code, I have learn new thing. I have concluded my finding

Please Login or Register  to view this content.

. I attached here for your finding.

I thought I can conclude my work here but at the last moment I found out there is one more problem to solve. If I have not prepared for other to read the attachment, I won't know.

It doesn't split if there is no "," or ".0" in the data. ie, e=100, f=999.

It's ok if you don't want to answer this.

Again thank you.

Please check out

http://www.excelforum.com/excel-prog...cell-data.html