Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox selection copy data to various cells
I have a 3 column ListBox with a growing list of clients. I have a way to
delete them if necessary. What I now need is a way to pull their profile (based on a condition) to Sheet1 or Sheet2. I select the name in the ListBox, which corresponds to one row and columns A:AP in Sheet"SAVE" if column B contains "L", and goes to Sheet2 (cells all over in the sheet: E7, e14,e16,b22,b24,r2,r5,r6 and so on), if column B contains "C" or "F" (it will be one of the 3 L,F,C), then it goes to Sheet1 (cells all over, Columns A through AH are the same, for both Sheet1 & Sheet2, Sheet2 needs the data from AI:AP if column B contains the "L"). I don't know where to begin. I have code to take the data out of Sheet1 & 2 and put it in Sheet"SAVE", I just don't know how to reverse it. Any help is appreciated very much! Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox selection copy data to various cells
I am sure it is clear to you what you said, but based on the lack of
response, I would doubt it is clear to anyone else. The best I could say is you would need to look at sample code for the FindNext statement. Us an approach like shown there to find the unique combination of data values that would specify the location of the entry you are looking for. Look for the most unique item. Each time it is found, check a related value at a specific offset from the semi unique item that the combination of would make it really unique and see if it matches what you are looking for. If so, then process that location. If not, then continue to search. -- Regadrs, Tom Ogilvy "mikeolson" wrote: I have a 3 column ListBox with a growing list of clients. I have a way to delete them if necessary. What I now need is a way to pull their profile (based on a condition) to Sheet1 or Sheet2. I select the name in the ListBox, which corresponds to one row and columns A:AP in Sheet"SAVE" if column B contains "L", and goes to Sheet2 (cells all over in the sheet: E7, e14,e16,b22,b24,r2,r5,r6 and so on), if column B contains "C" or "F" (it will be one of the 3 L,F,C), then it goes to Sheet1 (cells all over, Columns A through AH are the same, for both Sheet1 & Sheet2, Sheet2 needs the data from AI:AP if column B contains the "L"). I don't know where to begin. I have code to take the data out of Sheet1 & 2 and put it in Sheet"SAVE", I just don't know how to reverse it. Any help is appreciated very much! Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox selection copy data to various cells
I suppose it was clear as mud. My ListBox contains my customer list, say I
select the 10th one down, this would be contained in Sheet("SAVE") row 11 (row one is my header). When I select my load button I want the data in row 11 to go to sheet1 or 2 (I can write the code to distinguish which sheet - I think). Sheet("SAVE") A11 goes to Sheet1 B2; B11 goes to R3, C11 goes to R5, D11 goes to R6, E11 goes to R7 and so on. Maybe a little clearer: Private Sub Form_Button_Load_Click() Sheets("SAVE").Rows(ListBox1.ListIndex + 2).Select If column B = "L", then 'the only choices are C,L,F Sheets("Sheet2").Range("B2") = Sheets("SAVE").Range("A11") Sheets("Sheet2").Range("R3") = Sheets("SAVE").Range("B11") Sheets("Sheet2").Range("R5") = Sheets("SAVE").Range("C11") Sheets("Sheet2").Range("R6") = Sheets("SAVE").Range("D11") Sheets("Sheet2").Range("R7") = Sheets("SAVE").Range("E11") Sheets("Sheet2").Range("E11") = Sheets("SAVE").Range("F11") ' This goes out to column AH for Sheet1 data and column AP for Sheet2, I can repeat the commands once you get me started. ELSE Sheets("Sheet1").Range("R3") = Sheets("SAVE").Range("B11") Sheets("Sheet1").Range("R5") = Sheets("SAVE").Range("C11") Sheets("Sheet1").Range("R6") = Sheets("SAVE").Range("D11") Sheets("Sheet1").Range("R7") = Sheets("SAVE").Range("E11") Sheets("Sheet1").Range("E11") = Sheets("SAVE").Range("F11") END IF End Sub I know a lot of tis code doesn't make sense, but I think it clears up the goal? or not? Thank you Tom! Mike "Tom Ogilvy" wrote: I am sure it is clear to you what you said, but based on the lack of response, I would doubt it is clear to anyone else. The best I could say is you would need to look at sample code for the FindNext statement. Us an approach like shown there to find the unique combination of data values that would specify the location of the entry you are looking for. Look for the most unique item. Each time it is found, check a related value at a specific offset from the semi unique item that the combination of would make it really unique and see if it matches what you are looking for. If so, then process that location. If not, then continue to search. -- Regadrs, Tom Ogilvy "mikeolson" wrote: I have a 3 column ListBox with a growing list of clients. I have a way to delete them if necessary. What I now need is a way to pull their profile (based on a condition) to Sheet1 or Sheet2. I select the name in the ListBox, which corresponds to one row and columns A:AP in Sheet"SAVE" if column B contains "L", and goes to Sheet2 (cells all over in the sheet: E7, e14,e16,b22,b24,r2,r5,r6 and so on), if column B contains "C" or "F" (it will be one of the 3 L,F,C), then it goes to Sheet1 (cells all over, Columns A through AH are the same, for both Sheet1 & Sheet2, Sheet2 needs the data from AI:AP if column B contains the "L"). I don't know where to begin. I have code to take the data out of Sheet1 & 2 and put it in Sheet"SAVE", I just don't know how to reverse it. Any help is appreciated very much! Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protect cells after listbox selection | Excel Discussion (Misc queries) | |||
protect cells after listbox selection | Excel Discussion (Misc queries) | |||
protect cells after listbox selection | Excel Discussion (Misc queries) | |||
protect cells after listbox selection | Excel Discussion (Misc queries) | |||
protect cells after listbox selection | Excel Discussion (Misc queries) |