Hi guys, Im trying to put convert an initial N x 1 data, such that, for every 2nd term, it goes to the 2nd column and the 3rd term to the third column. This goes on for the remaining data.
e.g.
1 2 3 4 5 6 7 8 9
to become:
1 2 3 4 5 6 7 8 9
I have this small VBA script:
However when running this, it gives me instead:
Please Login or Register to view this content.
1 2 3 3 4 5 5 6 7 7 8 9
Can someone let me know what is wrong with my VBA code?
Thanks!
Last edited by Corsa88; 09-11-2015 at 10:32 PM. Reason: Table and code format
Hi and welcome to the forum.
Please read the rules here as to format your code correctly.
also, please tell us your expected outcome.
Hi JasperOriginally Posted by JasperD
Thank you for the reply, have formatted my code according to the guide.
Please Login or Register to view this content.
Hi JasperPlease Login or Register to view this content.
Thanks for the solution!
Would there a more general way of getting the range of 'a' ? I might possibly have data spanning greater than 9 rows (likely > 100 rows of data), manual input might be too cumbersome.
You can put anything you want on the line where a is specified.
for example, all cells in column A:Please Login or Register to view this content.
shouldn't be too hard.Please Login or Register to view this content.
Please click the * below if this helps
Hi ! Try this demonstration ‼
Another way :
PHP Code:Sub Demo()
With Cells(1).CurrentRegion: VA = .Value: .Clear: End With
With Cells(1).Resize(UBound(VA) / 3, 3)
For N& = 1 To UBound(VA): .Cells(N).Value = VA(N, 1): Next
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Last edited by Marc L; 09-11-2015 at 08:22 PM. Reason: optimization …
Try this !
A full array way :
PHP Code:Sub Demo2()
With Cells(1).CurrentRegion: AR = .Value: .Clear: End With
C = Array(3, 1, 2): N& = UBound(AR) \ 3: R& = 1
ReDim VA(1 To N - (UBound(AR) > N * 3), 1 To 3)
For N = 1 To UBound(AR): M% = N Mod 3: VA(R, C(M)) = AR(N, 1): R = R - (M = 0): Next
Cells(1).Resize(UBound(VA), 3).Value = VA
End Sub
Thanks for the rep' !
Last edited by Marc L; 09-11-2015 at 09:12 PM.
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)
Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(note: this change is not optional)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Ah, may i know what is the difference between this and the previous one? Both seem to work well with large data
Sure, I made a change to the title. Do see if the title description is adequate?Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)
Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(note: this change is not optional
Re: Try this !
Demo2 could be faster with large data (writes cells range at once)
'cause first Demo has not display off (see ScreenUpdating property) and writes a cell by a cell …
Last edited by Marc L; 09-12-2015 at 07:22 AM. Reason: typo …
I see, that has been extremely helpful. Many thanks Marc !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks