This forum is a great place to learn, contributors I closely follow; Noorain, Asa, Don...
On Monday, July 23, 2012 5:25:31 PM UTC+1, bpascal123 wrote: > > My Vba coding style is much inspired from Kris who from what I know, > posts here and on excelfox.com. If you can read his code, you should > learn many tricks. > > If rCol > 1 Then >> v = Array(r) >> .Rows(i + 1 & ":" & i + rCol - 1).Insert >> >> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, >> 1), .Cells(i + rCol - 1, 7)) >> >> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ > > > > v = Array(r) could actually be changed to v = r or v = r.value > v is an array that stores each code used by user column. Using an array > was the only option since later the code calls a copy transpose method but > as a range (opposite to array), copy transpose can't be used on the same > range as for instance H8 would keep the first value, H9 the second. Using a > range copy method, I would have had to insert an additional row to > transpose the data and then delete the row 8, I hope you can follow me > > .Rows(i + 1 & ":" & i + rCol - 1).Insert > rCol is the variable that stores the number of code used by user, as i'm > using an array, i can keep the value in the first row and add just a new > row (still for H8 example) > > .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i > + rCol - 1, 7)) > This is to copy the data from the initial line to the row i'm > adding...rCol-1 same as line above rCol-1 > > .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ > Application.WorksheetFunction.Transpose(v) > > This took me some time to figure out, without this transpose function, I > would have add to another loop... transpose just re-arrange data from row > to column or the other way around, here v is the array that stores the code > used by user values for one project or one row. > > If rCol > lastc Then lastc = rCol > > This is for cleaning purpose, lastc stores the latest column used so to > delete from H column to that column only instead of using a range H to > columns.count clear contents method that could mess with your worksheet > formatting or else > > --- > As these line are part of a loop, you can see this code in action by > setting a break point (a big red dot on the left margin of a line of code) > using the Vba IDE at the entry of the for loop, click Run until the break > point you have inserted is highlighted in yellow, then minimize your Vba > IDE window so you can see your worksheet and the data as well as some line > of the Vba IDE (horizontal tile window like), then press once F8, look the > position of the code advancing, and look for any changes on the data such > as inserting rows, copy paste transpose... > > You can also check the value of variables or object using the watch or > debug window. The watch window is more straightforward since you just need > to right click a variable like v or an object like r and select "add > watch", you will then be able to monitor values stored in an object or > variable. > > > I think if you already have some knowledge of Vba, you can use these Vba > tools as I'm trying to present you. > > Pascal Baro > > > On Monday, July 23, 2012 2:03:26 PM UTC+1, SG wrote: >> >> hi Pascal.... >> >> Thanku so much....it really helped me....appreciate your help.....& even >> it increased my knowledge in VB... >> plz explain below part of code...& thanks again >> >> If rCol > 1 Then >> v = Array(r) >> .Rows(i + 1 & ":" & i + rCol - 1).Insert >> >> .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, >> 1), .Cells(i + rCol - 1, 7)) >> >> .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ >> > -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
