check a range

K

kevin carter

Hi
i have a worksheet that the user is required to enter data
in row 27 columns c to z
what i want to to is check the cell data entered in and return a value
eg.
if the user enters a value in cells C27 to J27 i want to return A in
cell av1
if the user enters a value in cells k27 to R27 i want to return B in
cell av2
if the user enters a value in cells S27 to Z27 i want to return C in
cell av3

any ideas

thanks

kevin
 
B

Bob Phillips

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE_1 As String = "C27:J27"
Const WS_RANGE_2 As String = "K27:R27"
Const WS_RANGE_3 As String = "S27:Z27"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
Range("AV1").Value = "A"
ElseIf Not Intersect(Target, Me.Range(WS_RANGE_2)) Is Nothing Then
Range("AV2").Value = "B"
ElseIf Not Intersect(Target, Me.Range(WS_RANGE_3)) Is Nothing Then
Range("AV2").Value = "C"
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

MDBJ

Bob Phillips said:
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE_1 As String = "C27:J27"
Const WS_RANGE_2 As String = "K27:R27"
Const WS_RANGE_3 As String = "S27:Z27"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
Range("AV1").Value = "A"
ElseIf Not Intersect(Target, Me.Range(WS_RANGE_2)) Is Nothing Then
Range("AV2").Value = "B"
ElseIf Not Intersect(Target, Me.Range(WS_RANGE_3)) Is Nothing Then
Range("AV2").Value = "C"
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
Click to expand...


Damn... that's deep- try my 3 liner instead maybe...

in av1 =if(max(C27:J27)>0,"A","")
in av2 =if(max(k27:rJ27)>0,"B","")
in av2 =if(max(s27:zJ27)>0,"C","")
 
Top