VBA Split Issue

[Gordon85]

Hi,

I am trying to split a cell and get the lower value of it from one Excel to another

Please Login or Register  to view this content.

Any help would be mostly appreciated.

Thanks!

[Gordon85]

This is the error:

'Expected array' on vbCr

Please Login or Register  to view this content.

[Rick Rothstein]

I believe VBA is complaining about your Cell keyword... it should be Cells (with an "s" at the end).

You have the (1), which I think should be (0) because the first element of the array produced by the Split function is 0, not 1, in the wrong location. Try it this way...

wscopy("A" & NR).Value = Split(cell.Offset(0, 1), vbCr)(1)

[Gordon85]

Hi
Thank you for your fast response, I appreciate it.
You are right.

I tried like this:

Please Login or Register  to view this content.

Because I want to target the lower part of the cell (as it is literally split into 2 in Excel) but the above code gives the following:

Runtime error '9' subscript out of range

If i use (0) after vbCr it gives back the upper part of the cell value..

[Rick Rothstein]

Reread what I posted in Message #3 as I think I changed it while you were responding to what I wrote initially.

[Gordon85]

Hi,

My bad, sorry about that.

I changed it, but it still gives Subscript out of range Runtime error 9 on this line wscopy("A" & NR).Value = Split(cell.Offset(0, 1), vbCr)(1)

If I change the (1) after vbCr to (0) it will give back the upper part of the cell..while I need the lower part

Also going offset(0,2) with (0) after vbCr won't help either

[Rick Rothstein]

I think we might need to see all of the code for the procedure that line of code is in.

In addition to posting the above requested code, can you show us a few example cell values that you are splitting and show us what you want returned for each of them?

[Gordon85]

Yes, sure, just a moment.

[Gordon85]

Nevermind, solved it, due to syntax.
Thank you!