VBA to manage user input in a workbook

[Guest]

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

 

[wisccal]

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

 

[Dave Peterson]

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

 

[Guest]

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.

 

[wisccal]

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/printCombinations_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.