|
=IF(EXACT(W23,"Evaluate"),IF(COUNTBLANK($F23:$J23)<5,LOOKUP("X",$F23:$J23,$F$20:$J$20),0),0)
I am working on a rating Interpretation scale. This is the formula that I have applied to Excel Worksheet. Inside the Range F23 to J33, I have used the Data Validation rule that allows the user to select "X" It basically looks through a range of F23 to J33 and if "X" is seen in any row, it goes back to F20:J20 where the values are stored from 1 to 5. Normally this formulae is working fine but when I try to incorporate it into VBA, it doesn't show any value. This is the code that I am using. Please let me know if I am doing it right.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim ValidationRange As Range
Dim Cell As Range
' Define the worksheet
Set ws = ThisWorkbook.Sheets("Sheet 1")
' Define the validation range (F23:J33)
Set ValidationRange = ws.Range("F23:J33")
' Check if the change occurred within the ValidationRange
If Not Intersect(Target, ValidationRange) Is Nothing Then
Application.EnableEvents = False ' Disable events to prevent re-triggering
For Each Cell In Target.Cells
If Cell.Value = "X" Then
' Get the corresponding value from row 20 (F20 to J20)
Dim ValueToDisplay As Variant
ValueToDisplay = ws.Cells(20, Cell.Column).Value
' Update the value in column AF in the same row
ws.Cells(Cell.Row, "AF").Value = ValueToDisplay
End If
Next Cell
Application.EnableEvents = True ' Re-enable events
End If
End Sub
|