Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to manage user input in a workbook
I am using XL 2000
My worksheet features 2 columns A+B, each contain cells formatted with a list containing 4 user selectable options; VL,L,H,VH. The worksheet rows will increase according to need. For each row, a user will select an option from each list, e.g Col A, VL is selected & Col B VH is selected. There are 16 combinations. I would like to to assign a number to each combination, starting with 1 ending with 16 and have that number appear in Col C of the same row after each selection. I have exhausted my formula knowledge and am looking to VB for a solution. Grateful of any suggestions on how I could approach this. Thanks, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to manage user input in a workbook
This macro might help you:
Option Explicit '======================================= 'Sheet2 is the source sheet where column A and Column B 'contain all the values that can be combined 'eg: A B ' VL VL ' L L ' H H ' V V 'Sheet1 is the table where the user select the values in column A & B. 'The numbers of the combinations will be printed to column c Public Sub printCombinations() Dim wb As Excel.Workbook Dim arrComb() As String, rngTable As Excel.Range, rngSrc As Excel.Range, r As Excel.Range Dim i As Integer, j As Integer, cnt As Integer, tmp As String Set wb = ThisWorkbook With wb.Sheets("Sheet2") Set rngSrc = .Range("A1:B" & .Range("A1").End(xlDown).Row) End With With wb.Sheets("Sheet1") Set rngTable = .Range("A1:C" & .Range("A1").End(xlDown).Row) End With cnt = rngSrc.Rows.Count ReDim arrComb((cnt * cnt) - 1) For i = 1 To cnt tmp = Trim(rngSrc.Cells(i, 1).Value) For j = 1 To cnt arrComb((((i - 1) * cnt) + j) - 1) = tmp & Trim(rngSrc.Cells(j, 2).Value) Next j Next i For i = 1 To rngTable.Rows.Count cnt = 0 For j = 0 To UBound(arrComb) Debug.Print Trim(rngTable.Cells(i, 1).Value & rngTable.Cells(i, 2).Value); " / " & arrComb(cnt) If (arrComb(cnt)) = Trim(rngTable.Cells(i, 1).Value & rngTable.Cells(i, 2).Value) Then rngTable.Cells(i, 3).Value = cnt End If cnt = cnt + 1 Next j Next i Set wb = Nothing Set rngTable = Nothing Set rngSrc = Nothing End Sub '======================================= Let me know if this is what you had in mind. Also, I think it would be actually easier to do this with Excel formulas (VLOOKUP would be particular suited). Regards, Steve Paulc schrieb: I am using XL 2000 My worksheet features 2 columns A+B, each contain cells formatted with a list containing 4 user selectable options; VL,L,H,VH. The worksheet rows will increase according to need. For each row, a user will select an option from each list, e.g Col A, VL is selected & Col B VH is selected. There are 16 combinations. I would like to to assign a number to each combination, starting with 1 ending with 16 and have that number appear in Col C of the same row after each selection. I have exhausted my formula knowledge and am looking to VB for a solution. Grateful of any suggestions on how I could approach this. Thanks, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to manage user input in a workbook
I'd create a new worksheet and put this in A1:B16
VL-VL 1 L-VL 2 H-VL 3 VH-VL 4 VL-L 5 L-L 6 H-L 7 VH-L 8 VL-H 9 L-H 10 H-H 11 VH-H 12 VL-VH 13 L-VH 14 H-VH 15 VH-VH 16 (modify the numbers the way you like. And hyphens are there to make it easier to read.) Then in the first worksheet, I'd use a formula like: =if(counta(a1:b1)<2,"",vlookup(a1&"-"&b1,sheet2!a:b,2,false)) And drag down Paulc wrote: I am using XL 2000 My worksheet features 2 columns A+B, each contain cells formatted with a list containing 4 user selectable options; VL,L,H,VH. The worksheet rows will increase according to need. For each row, a user will select an option from each list, e.g Col A, VL is selected & Col B VH is selected. There are 16 combinations. I would like to to assign a number to each combination, starting with 1 ending with 16 and have that number appear in Col C of the same row after each selection. I have exhausted my formula knowledge and am looking to VB for a solution. Grateful of any suggestions on how I could approach this. Thanks, Paul -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to manage user input in a workbook
Steve, thanks for your solution.
It does appear to be what i am looking. I'm never sure of these thing, is the code placed behind the worksheet, workbook or module? Paul. " wrote: This macro might help you: Option Explicit '======================================= 'Sheet2 is the source sheet where column A and Column B 'contain all the values that can be combined 'eg: A B ' VL VL ' L L ' H H ' V V 'Sheet1 is the table where the user select the values in column A & B. 'The numbers of the combinations will be printed to column c Public Sub printCombinations() Dim wb As Excel.Workbook Dim arrComb() As String, rngTable As Excel.Range, rngSrc As Excel.Range, r As Excel.Range Dim i As Integer, j As Integer, cnt As Integer, tmp As String Set wb = ThisWorkbook With wb.Sheets("Sheet2") Set rngSrc = .Range("A1:B" & .Range("A1").End(xlDown).Row) End With With wb.Sheets("Sheet1") Set rngTable = .Range("A1:C" & .Range("A1").End(xlDown).Row) End With cnt = rngSrc.Rows.Count ReDim arrComb((cnt * cnt) - 1) For i = 1 To cnt tmp = Trim(rngSrc.Cells(i, 1).Value) For j = 1 To cnt arrComb((((i - 1) * cnt) + j) - 1) = tmp & Trim(rngSrc.Cells(j, 2).Value) Next j Next i For i = 1 To rngTable.Rows.Count cnt = 0 For j = 0 To UBound(arrComb) Debug.Print Trim(rngTable.Cells(i, 1).Value & rngTable.Cells(i, 2).Value); " / " & arrComb(cnt) If (arrComb(cnt)) = Trim(rngTable.Cells(i, 1).Value & rngTable.Cells(i, 2).Value) Then rngTable.Cells(i, 3).Value = cnt End If cnt = cnt + 1 Next j Next i Set wb = Nothing Set rngTable = Nothing Set rngSrc = Nothing End Sub '======================================= Let me know if this is what you had in mind. Also, I think it would be actually easier to do this with Excel formulas (VLOOKUP would be particular suited). Regards, Steve Paulc schrieb: I am using XL 2000 My worksheet features 2 columns A+B, each contain cells formatted with a list containing 4 user selectable options; VL,L,H,VH. The worksheet rows will increase according to need. For each row, a user will select an option from each list, e.g Col A, VL is selected & Col B VH is selected. There are 16 combinations. I would like to to assign a number to each combination, starting with 1 ending with 16 and have that number appear in Col C of the same row after each selection. I have exhausted my formula knowledge and am looking to VB for a solution. Grateful of any suggestions on how I could approach this. Thanks, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to manage user input in a workbook
Hi Paul,
I would place it into a separate module. However, you can also put it into ThisWorkbook. It doesn't make much of a difference in this case. The ThisWorkbook module just has a reference to the current workbook, which means you do not have to fully qualify your objects (eg, you can say Sheets(1) instead of wb.Sheets(1)). The module I posted uses fully qualified names only, so you may put it in any module inside the workbook you use it for. PS. for your convenience, I uploaded the macro as text file to the following address: http://www.swiss-ins.com/stk/vb/prin...ions_macro.txt The formatting sometimes gets screwed up when code is posted to a news group. The above text file will have the correct formatting. ----------------------------- Regards, Steve www.swiss-ins.com Paulc wrote: Steve, thanks for your solution. It does appear to be what i am looking. I'm never sure of these thing, is the code placed behind the worksheet, workbook or module? Paul. " wrote: This macro might help you: Option Explicit '======================================= 'Sheet2 is the source sheet where column A and Column B 'contain all the values that can be combined 'eg: A B ' VL VL ' L L ' H H ' V V 'Sheet1 is the table where the user select the values in column A & B. 'The numbers of the combinations will be printed to column c Public Sub printCombinations() Dim wb As Excel.Workbook Dim arrComb() As String, rngTable As Excel.Range, rngSrc As Excel.Range, r As Excel.Range Dim i As Integer, j As Integer, cnt As Integer, tmp As String Set wb = ThisWorkbook With wb.Sheets("Sheet2") Set rngSrc = .Range("A1:B" & .Range("A1").End(xlDown).Row) End With With wb.Sheets("Sheet1") Set rngTable = .Range("A1:C" & .Range("A1").End(xlDown).Row) End With cnt = rngSrc.Rows.Count ReDim arrComb((cnt * cnt) - 1) For i = 1 To cnt tmp = Trim(rngSrc.Cells(i, 1).Value) For j = 1 To cnt arrComb((((i - 1) * cnt) + j) - 1) = tmp & Trim(rngSrc.Cells(j, 2).Value) Next j Next i For i = 1 To rngTable.Rows.Count cnt = 0 For j = 0 To UBound(arrComb) Debug.Print Trim(rngTable.Cells(i, 1).Value & rngTable.Cells(i, 2).Value); " / " & arrComb(cnt) If (arrComb(cnt)) = Trim(rngTable.Cells(i, 1).Value & rngTable.Cells(i, 2).Value) Then rngTable.Cells(i, 3).Value = cnt End If cnt = cnt + 1 Next j Next i Set wb = Nothing Set rngTable = Nothing Set rngSrc = Nothing End Sub '======================================= Let me know if this is what you had in mind. Also, I think it would be actually easier to do this with Excel formulas (VLOOKUP would be particular suited). Regards, Steve Paulc schrieb: I am using XL 2000 My worksheet features 2 columns A+B, each contain cells formatted with a list containing 4 user selectable options; VL,L,H,VH. The worksheet rows will increase according to need. For each row, a user will select an option from each list, e.g Col A, VL is selected & Col B VH is selected. There are 16 combinations. I would like to to assign a number to each combination, starting with 1 ending with 16 and have that number appear in Col C of the same row after each selection. I have exhausted my formula knowledge and am looking to VB for a solution. Grateful of any suggestions on how I could approach this. Thanks, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send workbook automatically as attachment based on input from user | Excel Programming | |||
Using excel to manage event - ANY input deeply appreciated! :-) | Excel Discussion (Misc queries) | |||
Copy cell data from workbook based on user input | Excel Programming | |||
Preparing Workbook for user input | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |