Hi group
Just to complete the record, The macro I wrote for Siraj was
Option Explicit
Sub PopulateResult()
Dim cr As Long 'row on sheet cat
Dim ir As Long 'row on sheet id
Dim rr As Long 'row on sheet result
Dim arr(4) As String ' 5 element array to hold the result
Dim c As Worksheet
Dim i As Worksheet
Dim r As Worksheet
Set c = Worksheets("cat")
Set i = Worksheets("id")
Set r = Worksheets("Result")
'clear result sheet
r.Range("A2:" & r.Range("E2").End(xlDown).Address).Clear
'set up start rows
ir = 2
rr = 2
'step through id
Do While i.Range("A" & ir) <> ""
arr(0) = i.Range("A" & ir)
arr(1) = i.Range("B" & ir)
arr(2) = i.Range("C" & ir)
'now loop through cat
cr = 2
Do While c.Range("A" & cr) <> ""
If c.Range("A" & cr) = arr(2) Then
arr(3) = c.Range("B" & cr)
arr(4) = c.Range("C" & cr)
r.Range("A" & rr, "E" & rr) = arr
rr = rr + 1
End If
cr = cr + 1
Loop
ir = ir + 1
Loop
End Sub
Hi Siraj
Here is your file with the macro. Two points.
1 I have not tried to do any formatting of the results.
2 The list is in ID order rather than Cat order.
It would not be very difficult to change this if you need it. Let me know.
I could pick the colour from the Cat and apply it to the Result. Also I
could sort by Cat then ID when it is complete.
Best Regards
David Grugeon
Excel VBA Group Moderator
From: Siraj Momin (BTG) [mailto:smomin@ccc.ae]
Sent: Wednesday, 20 July 2011 11:20 PM
To: yahoo@grugeon.com.au
Subject: FW: [ExcelVBA]
Thank you very much, take your time it will be great help for me..
Best Regards
Siraj
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of David Grugeon
Sent: 20 July 2011 04:58 PM
To: ExcelVBA@yahoogroups.com
Subject: RE: [ExcelVBA]
OK Siraj
Send it to me (yahoo@grugeon.com.au <mailto:yahoo%40grugeon.com.au> ) and I
will see what I can do. It may
not be straight away because I am in Australia and going to sleep now.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ] On
Behalf
Of Siraj Momin (BTG)
Sent: Wednesday, 20 July 2011 9:29 PM
To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
Subject: RE: [ExcelVBA]
Actually data is more this is the reason I generate this sample data but
there no blank row in my table I don't know how it changes the format when
reaches this board it is in formatted table.
Just I will try to elaborate to you here.
1. I have three sheet named 1) cat 2) id 3) result
2. In cat sheet categories are defined (in this sheet 3 columns
names are : CATCODE, CATCODE-DESC, FORMNO)
3. In id sheet id's are defined and categories are assigned as
defined in cat sheet. defined (in this sheet 3 columns names are : ID, DESC
, CATCODE)
4. In result sheet I want the combination of cat & id sheets (in
this sheet columns are ID, DESC, CATCODE, CATCODE-DESC, FORMNO)
5. Suppose in cat sheet I have 2 records with same CATCODE,
CATCODE-DESC BUT FORMNO DIFFERENT (EXAMPLE: FM-1037-001, FM-1037-002)
6. What I want is if I assign this CATCODE against any of the ID
in my sheet my result sheet should added with that particular id and those 2
FORMNO should be reflected against that id, remember ID AND CATCODE are
plenty that is why I need automatic generation
Can I send the excel file to your personal email because attachments are not
allowed in this group.
From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ] On
Behalf
Of David Grugeon
Sent: 20 July 2011 02:56 PM
To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
Subject: RE: [ExcelVBA]
It seems you have a lot of data in one column in groups separated by blank
rows There seem to be blank rows between each row with Data and 2 or more
blank rows between each set of data which is presumably a record. Each
record seems to have 3 fields. Is it possible for any of the fields to ever
be blank? Will the first two fields of each record always commence 'CVL'
with the second field always having a space after the 'CVL'. Will the third
fields always commence with 'FM-'
How many records would you typically have to deal with? How often will this
occur?
But, Most important: What do you want to do with this data?
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
<mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
<mailto:ExcelVBA%40yahoogroups.com> ] On
Behalf Of Siraj Momin (BTG)
Sent: Wednesday, 20 July 2011 8:04 PM
To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
<mailto:ExcelVBA%40yahoogroups.com>
Subject: [ExcelVBA]
Dear Members
Can any one help me on this
sheet named "cat"
CATCODE
CATCODE-DESC
FORMNO
CVL01
CVL DESC01
FM-0001
CVL01
CVL DESC01
FM-0002
CVL01
CVL DESC01
FM-0003
CVL02
CVL DESC02
FM-0004
CVL02
CVL DESC02
FM-0005
CVL02
CVL DESC02
FM-0006
CVL02
CVL DESC02
FM-0007
sheet named "id"
ID
DESC
CATCODE
A
DESC A
CVL01
B
DESC B
CVL02
C
DESC D
CVL02
D
DESC E
CVL01
sheet named "RESULT"
ID
DESC
CATCODE
CATCODE-DESC
FORMNO
A
DESC A
CVL01
CVL DESC01
FM-0001
A
DESC A
CVL01
CVL DESC01
FM-0002
A
DESC A
CVL01
CVL DESC01
FM-0003
D
DESC C
CVL01
CVL DESC01
FM-0001
D
DESC C
CVL01
CVL DESC01
FM-0002
D
DESC C
CVL01
CVL DESC01
FM-0003
B
DESC B
CVL02
CVL DESC02
FM-0004
B
DESC B
CVL02
CVL DESC02
FM-0005
B
DESC B
CVL02
CVL DESC02
FM-0006
B
DESC B
CVL02
CVL DESC02
FM-0007
C
DESC D
CVL02
CVL DESC02
FM-0004
C
DESC D
CVL02
CVL DESC02
FM-0005
C
DESC D
CVL02
CVL DESC02
FM-0006
C
DESC D
CVL02
CVL DESC02
FM-0007
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles!
Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar