Problem with macro code

[elgato74]

Dear all


i have the following code

Please Login or Register  to view this content.

which basically puts the value of Column "AN" into Column "C" of another sheet.

I would like to also put values of Column "L" of the same sheet in "C" with the pipe | symbol separating those two values.

Can you help ?

[CK76]

So something like below?

Please Login or Register  to view this content.

Or is column L in same sheet with "C" column?

If so... try.

Please Login or Register  to view this content.

[elgato74]

first is the case ... let me try it

[elgato74]

Yes. Awesome. Thank you!

At a later stage, I need to break these "|" separated values in one cell up into two cells again. How could this be done ?

E.g. 47377373|48834884 in Column A into Column B and C

[CK76]

If in VBA just use Split function.

Please Login or Register  to view this content.

Or if you need it as number and not string type.

Please Login or Register  to view this content.

[elgato74]

That's great - thank you so much ! Solved ! Cheers

[CK76]

You are welcome and thanks for the rep

[elgato74]

sorry to bother again,

Please Login or Register  to view this content.

does only A1

how do I split entire column ?

[CK76]

I am away from pc for rest of the day.
I’ll give more details tomorrow, but basically you need to loop each cell of column.
Or do it in memory using array.

[CK76]

So, lets say you have value in Column A, starting from row 2, but end is not fixed.

You'd loop like below.

Please Login or Register  to view this content.

[elgato74]

i just tried that with

1498945308004|142246606918
1498985904004|140912299431
921787780023|362008752737
1499009563004|142415121092
921792748023|361880684487
921810213023|361907883745
1499047192004|140912298233
921821069023|361994377667
921891390023|361880804032
921897893023|361880786571
1499203234004|142505575523


it gave me a runtime error "overflow" in

Please Login or Register  to view this content.

[CK76]

It would since 1498945308004 is greater than Long type's limit.

If you need it as numeric use CDbl. But from the looks of it, it's some sort of ID, then just use it as string without CLng or CDbl.

[elgato74]

yes, those are item id's and transaction id's

[elgato74]

perfect. now it works like a charm.
Thank you again

[CK76]

You are welcome If this solved your issue, please mark the thread as solved by using Thread tool found at top right of your initial post.