Greetings,

I am attempting to create a general purpose data extraction utility, which
can extract the contents of one Excel worksheet & copy it into user-defined
columns in another worksheet.

Example:
SOURCE COLUMN ACTION/CRITERIA DESTINATION COLUMN
------------------------------------------------------------------------------------
A EQUALS A
D SHALL BE COPIED TO C

The above would be the interface on the Excel worksheet. The user specifies
the source and destination files via a browse window. The script would then
perform the task of iterating through the rows of the source worksheet,
finding each instance where the source worksheet's 'A' column value matches
the value in an 'A' column of the destination worksheet. When such an
instance is found, it then performs the task of copying the contents of the
current row of Column 'D' from the source worksheet, to Column 'C' of the
matching row in the destination worksheet. All possible criteria that could
be entered into the Action/Criteria cell a EQUALS, CONTAINS, BEGINS WITH,
ENDS WITH, SHALL BE COPIED OVER TO. A dropbox is used for the
Action/Criteria Cell, to prevent erroneous data entry.

I'm already set with coding the actual finding & copying operations. What I
need assistance on (if it is possible), is how to dynamically generate
conditional statements in VBA. By this I mean:

Dim sourcecol as string
Dim actcrit as string
Dim destcol as string

' User enters the info given in the example above, so that the following
' conditional statement (?) can be exercised in the code

If sourcecol(1) & actcrit(1) & destcol(1) Then 'If Src A = Dest A
DESTFILE & destcol(2) = SRCFILE & srccol(2)
End if

I know that I could simply have a case statement or nested if to "find" the
correct operation(s) to perform, but that would be a HUGE set of possible
permutations of criteria (even at a maximum of 5 criteria).

This needs to be an automated process that could potentially have up to 5
criteria (conditions to look for) before performing the operation & may deal
with multiple files (sequentially) from other users. Therefore, using
worksheet functions will not suffice.

Any help with this would be most appreciated!

I've only skim read your post but I think the Evaluate method should
help you.

This will perform a calculation based on a string input, eg.
If Evaluate(sourcecol(1) & actcrit(1) & destcol(1)) Then
....

Best regards,
Andrew


On 28 Sep, 15:45, Garbunkel
wrote:
Greetings,

I am attempting to create a general purpose data extraction utility, which
can extract the contents of one Excel worksheet & copy it into user-defined
columns in another worksheet.

Example:
SOURCE COLUMN * * * * *ACTION/CRITERIA * * * * *DESTINATION COLUMN
---------------------------------------------------------------------------*---------
A * * * * * * * * * * * * * * * * EQUALS * * * * * * * * * * * * A
D * * * * * * * * * * * * * * * * SHALL BE COPIED TO * * *C

The above would be the interface on the Excel worksheet. *The user specifies
the source and destination files via a browse window. *The script would then
perform the task of iterating through the rows of the source worksheet,
finding each instance where the source worksheet's 'A' column value matches
the value in an 'A' column of the destination worksheet. *When such an
instance is found, it then performs the task of copying the contents of the
current row of Column 'D' from the source worksheet, to Column 'C' of the
matching row in the destination worksheet. *All possible criteria that could
be entered into the Action/Criteria cell a EQUALS, CONTAINS, BEGINS WITH,
ENDS WITH, SHALL BE COPIED OVER TO. *A dropbox is used for the
Action/Criteria Cell, to prevent erroneous data entry.

I'm already set with coding the actual finding & copying operations. *What I
need assistance on (if it is possible), is how to dynamically generate
conditional statements in VBA. *By this I mean:

Dim sourcecol as string
Dim actcrit as string
Dim destcol as string

' User enters the info given in the example above, so that the following
' conditional statement (?) can be exercised in the code

If sourcecol(1) & actcrit(1) & destcol(1) Then * * * 'If Src A = Dest A
* * * DESTFILE & destcol(2) = SRCFILE & srccol(2)
End if

I know that I could simply have a case statement or nested if to "find" the
correct operation(s) to perform, but that would be a HUGE set of possible
permutations of criteria (even at a maximum of 5 criteria).

This needs to be an automated process that could potentially have up to 5
criteria (conditions to look for) before performing the operation & may deal
with multiple files (sequentially) from other users. *Therefore, using
worksheet functions will not suffice. *

Any help with this would be most appreciated!