Validation and Lookup
I have a spreadsheet with 4 columns. Column 1 is a subject column. Column 2 lists a question, to which there are 4 possible responses which are located in Column 3. Column 3 has a Validation List for one of the 4 possible responses. Each response in Column 3 corresponds to a particular symbol,A,B,C or D in sequential order in the Validation List which I want to populate into Column 4 with a number value corresponding to the symbol. A= 0 points, B = 3 points, C= 6 points, D=10 points. How do I go about defining the lookup formula? I am using Windows 7. Thank you very kindly, Abdool
| Attachment | Size |
|---|---|
| Problem1-PreQualfn.xlsx | 33.93 KB |
Your reply
Expecting your response
Find In Sheet 2 And Put In Sheet 1
You have to change 'which are located in Column 3.'
as 'one of which is located in Column 3.'
Private Sub cmdFindIn2PutIn1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, c As Range, FoundData As Range
Set sh1 = Sheets("Table2.1")
Set sh2 = Sheets("Validation")
Set rng = sh1.Range("C10:C88")
sh2.Select
For Each c In rng
If Len(Trim(c)) > 0 Then
Set FoundData = sh2.Range("A:D").Find(c.Value, , xlValues)
If Not FoundData Is Nothing Then
c.Offset(0, 1) = Cells(1, FoundData.Column)
End If
End If
Next
End Sub