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 [email protected]
To unsubscribe, send a blank email to [email protected]
