Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
display values in cell
I am new to Excel VBA but quite comfortable with Access
VBA. I would like to enter a value in cell C1 based on the following conditions If the value in A1 is < 90 then display OK in B1 If the value in A1 is 100 display Overdue in B1 If the value in A1 is between 90 and 100 print Notice in B1 This is what I have but it is not working Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim celA1 As Integer Dim celB1 As String celA1 = Range("A1:A1") celB1 = Range("B1:B1") If celA1 < 90 Then celB1 = "OK" ElseIf celA1 100 Then celB1 = "Overdue" Else celB1 = "Notice" End If End Sub Thanks for any help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
display values in cell
You've got the structure right, but are struggling with the Range object.
I figure you'll be able to get it going with your If, ElseIf routine, but thought I'd demonstrate another way: Sub test() Dim rng1 As Range, rng2 As Range Set rng1 = Range("A1") Set rng2 = Range("B1") Select Case rng1.Value Case Is < 90: rng2.Value = "OK" Case Is 100: rng2.Value = "Overdue" Case Else: rng2.Value = "Notice" End Select End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "tim" wrote in message ... I am new to Excel VBA but quite comfortable with Access VBA. I would like to enter a value in cell C1 based on the following conditions If the value in A1 is < 90 then display OK in B1 If the value in A1 is 100 display Overdue in B1 If the value in A1 is between 90 and 100 print Notice in B1 This is what I have but it is not working Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim celA1 As Integer Dim celB1 As String celA1 = Range("A1:A1") celB1 = Range("B1:B1") If celA1 < 90 Then celB1 = "OK" ElseIf celA1 100 Then celB1 = "Overdue" Else celB1 = "Notice" End If End Sub Thanks for any help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
display values in cell
Hi Tim
first why are you using VBA for this task. You can put the following formula in B1 =IF(A1<"",IF(A1100,"OVERDUE",IF(A1<90,"OK","Noti ce")),"") For your code example, try the following (if you really want to use the worksheet change event): Private Sub Worksheet_Change(ByVal Target As Range) Dim b_rng as range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub Set b_rng = Range("B1") On Error GoTo CleanUp: With Target if .value <"" then Application.EnableEvents = False If .Value 100 then b_rng.value = "Overdue elseif .value < 90 then b_rng.value = "OK" else b_rng.value = "Notice" end if else b_rng.value = "" end if End With CleanUp: Application.EnableEvents = True End Sub Frank tim wrote: I am new to Excel VBA but quite comfortable with Access VBA. I would like to enter a value in cell C1 based on the following conditions If the value in A1 is < 90 then display OK in B1 If the value in A1 is 100 display Overdue in B1 If the value in A1 is between 90 and 100 print Notice in B1 This is what I have but it is not working Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim celA1 As Integer Dim celB1 As String celA1 = Range("A1:A1") celB1 = Range("B1:B1") If celA1 < 90 Then celB1 = "OK" ElseIf celA1 100 Then celB1 = "Overdue" Else celB1 = "Notice" End If End Sub Thanks for any help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
display values in cell
Frank;
Thanks, I am using VBA because this would give me more flexibility such as writing function, sub procedure. My Access VBA background has skewed me towards this direction. Thanks again Tim I do know that I could do what you said but my Access VBA -----Original Message----- Hi Tim first why are you using VBA for this task. You can put the following formula in B1 =IF(A1<"",IF(A1100,"OVERDUE",IF (A1<90,"OK","Notice")),"") For your code example, try the following (if you really want to use the worksheet change event): Private Sub Worksheet_Change(ByVal Target As Range) Dim b_rng as range If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub Set b_rng = Range("B1") On Error GoTo CleanUp: With Target if .value <"" then Application.EnableEvents = False If .Value 100 then b_rng.value = "Overdue elseif .value < 90 then b_rng.value = "OK" else b_rng.value = "Notice" end if else b_rng.value = "" end if End With CleanUp: Application.EnableEvents = True End Sub Frank tim wrote: I am new to Excel VBA but quite comfortable with Access VBA. I would like to enter a value in cell C1 based on the following conditions If the value in A1 is < 90 then display OK in B1 If the value in A1 is 100 display Overdue in B1 If the value in A1 is between 90 and 100 print Notice in B1 This is what I have but it is not working Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim celA1 As Integer Dim celB1 As String celA1 = Range("A1:A1") celB1 = Range("B1:B1") If celA1 < 90 Then celB1 = "OK" ElseIf celA1 100 Then celB1 = "Overdue" Else celB1 = "Notice" End If End Sub Thanks for any help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to display changing cell values in another cell. | Excel Worksheet Functions | |||
How do I have a data table display cell names rather than values? | Excel Worksheet Functions | |||
display values in cell | Excel Discussion (Misc queries) | |||
display negative values as a blank cell in Excel | Excel Discussion (Misc queries) | |||
How to display cell values in wordarts? | Excel Discussion (Misc queries) |