PDA

View Full Version : Need help for Dynamic Data



avadhutd2
09-01-2009, 05:18 AM
Hi,

There is a big situation where I am having a query regarding dynamic data.

Initially let me give some background -

I have two sheets in a excel wbook.
In the Sheet1 there will be a column called ID where in numerical value will be entered by user.
I need to get data corresponding to this ID only for the columns that are selected by the user. These selections are to be done from a UserForm where I populate the available column names from DB in one listbox and User have to make selections & get it in another listbox as per order he wish.
I have to get these columns selected by user with data from DB in Sheet2.
The ID field will be first in the list always.
I need to format the ID field values that are getting fetched from DB.
Finally, I have to search the ID entered in Sheet1 and I have to search it in Sheet 2 & fetch rest of the columns from Sheet2 to Sheet1.
I am done till step 6...that is I am able to get the options user selected along with data in Sheet2.

The only issue I am facing is the dynamic data. Since I am not aware what selections user can do …I have to generalize the things and that is creating a mess here.

I am using a code here as –

ActiveSheet.Range("B3").Formula =
"=IF(ISNA(MATCH(A3,Sheet1!$A$2:$A$20000,0))," & _
Chr$(34) & "N" & Chr$(34) & "," & Chr$(34) & "Y" & Chr$(34) & ")"

ActiveSheet.Range("C3").Formula = "=IF(B3=" & Chr$(34) & "Y" & Chr$(34) & _
",INDEX(Sheet1!$B$1:$B$20000," & _
"MATCH(A3,Sheet1!$A$1:$A$20000,0),1)," & Chr$(34) & Chr$(34) & ")"

First part gives result as “Y” or “N” since check is being done for the ID being present from the data fetched from DB (1st col in Sheet2)

Problem that is faced is with second part where if I try to execute it works for a specific value as above, but I need to generalize the same & I am not facing issues with that.

NEED GUIDANCE AS HOW TO MAKE THIS STATEMENT GENERALIZED in order to handle the dynamic selections?

The way I am trying is use of Cells option as ActiveSheet.Cells(iRow, iCol).Formula
Here I can set the values for the rows & columns in Sheet1, but how to generalize the part like Sheet1!$B$1:$B$20000

I hope the problem is somewhat clear …may be if more explanation needed pls let me know…

Thanks in advance!!

Benzadeus
09-01-2009, 08:18 AM
Use, for first code
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC[-1],Sheet1!R2C1:R20000C1,0)),""N"",""Y"")"


For second ocde
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""Y"",INDEX(Sheet1!R1C2:R20000C2,MATCH(RC[-2],Sheet1!R1C1:R20000C1,0),1),"""")"


Change ActiveCell for Selection, for instance.

avadhutd2
09-03-2009, 11:20 PM
Hi …thanks for the reply, however I couldn’t get back immediately due to some reasons. I started with your first code above …but I am getting result as 0.

I think I can add few more inputs with simple examples to the earlier ones (Please refer text in Blue)


I have two sheets in a excel wbook.
In the Sheet1 there will be a column called ID where in numerical value will be entered by user.
This will be the FIRST column (A) & entry will be starting with “A4” [Till A4 Heading, text etc is appearing]
Also to add, User can enter as much ID’s he can. For example user can enter ID’s in 15 fields starting from A4 to A18.
I need to get data corresponding to this ID only for the columns that are selected by the user. These selections are to be done from a UserForm where I populate the available column names from DB in one listbox and User have to make selections & get it in another listbox as per order he wish.
Structure is simple Make selections from ListBox1 to ListBox2. Example – Col1, Col2, Col3…….Col 100. User selects 4 options Col1, Col3, Col4, Col70.
I have to get these columns selected by user with data from DB in Sheet2.
The ID field will be first in the list always.
As specified above I already done code for getting the above 4 selections by user in Sheet# 2 with ID field as first.
First row will be having headings – Col1, Col3, Col4 & Col70 [i.e A1, B1, C1, D1]
Data from DB starts at 2nd row. [i.e A2, B2, C2, D2]
I need to format the ID field values that are getting fetched from DB.
Finally, I have to search the ID entered in Sheet1 and I have to search it in Sheet 2 & fetch rest of the columns from Sheet2 to Sheet1.
Here I am 1st searching ID entered by USER in Sheet# 1 in FIRST Column with ID in data fetched from DB in Sheet# 2 & adding result as Y or N in next Column – Column B.
Also, if the result is “Y”, I have to copy remaining columns in the Sheet# 1 from Sheet# 2.
Again just to put above in example – User starts with A4 in Sheet1. Then ID in A4 is compared with the entire column A in Sheet# 2. Consider we find that at A1000. Then next step is to copy remaining 3 values – Col3, Col4 & Col70 to Sheet# 1 at C4, D4, E4 respectively.
To summarize……….
ID in Range A4 (Sheet1)
Searching in Sheet2 for entire Column A
Getting match – set B4(Sheet1) as “Y”
If B4=Y, mark the row where we found ID in Sheet2 – to copy remaining columns.
Copy values of other 3 columns from B1000, C1000, D1000(Sheet2) in Sheet1 at C4, D4 & E4 resp.After this code will again operate for Range A5, then A6 …..till A18.
This LOOP will continue till the time user is finished with all the ID’s in Sheet1.

Also, this is an example if user has done 4 selections. User can make all 100 selections as well [i.e the 100 columns from DB will be there instead of 4 columns that we assumed.]

I need to take care of all these points but I am not sure how to handle this dynamic data & get all these in LOOP…..Can anyone let me know to code this situation?

Thanks in advance!

avadhutd2
09-03-2009, 11:24 PM
I had done the sub points as Blue but I can see these are not appearing in Blue except second bullet.

Please refer the sub bullets in the Main Bullets ....these are actually the Blue ones that I am talking about!

Sorry for inconvenience...

mdmackillop
09-04-2009, 05:44 AM
Can you provide a sample workbook?