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!!
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!!