-
1 Attachment(s)
IF Statement using VBA
Attachment 25972Hi
I have a workbook where as soon as the user enters a value column A (Range A3:A100) the value in Column B is Automatically Set to the value Cell D2 on a separate ADMIN tab and the value in Column E is Set to Cell E2 on thre same ADMIN Tab which works no problem. I was wondering if it is possible to do the same thing using VBA instead?
I have some code that looks at the range and displays messages boxes but can't find anything to replace the message boxes but cannot figure out how to replaces the messages boxes with value from the other tab.
Thanks
Mykal
Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim cel As Range
For Each cel In Range("A3:A100")
If cel.Value >= 1 / 1 / 2020 Then
MsgBox "Date" THIS SHOULD BE THE VALUE OF D2 on the admin tab
Else
MsgBox "Blank " THIS SHOULD BE THE VALUE OF E2 on the admin tab
End If
Next cel
End Sub
-
Code:
If cel.Value >= 1 / 1 / 2020 Then
MsgBox "Date" & Worksheets("admin").Range("D2") ' THIS SHOULD BE THE VALUE OF D2 on the admin tab
Else
MsgBox "Blank " & Worksheets("admin").Range("E2") 'THIS SHOULD BE THE VALUE OF E2 on the admin tab
End If
-
Hi JKwan and thanks for coming back to me but i think i have explained things wrong. I don't want message boxes at all but rather columns B & E to updated with the values from the admin tab when a date is entered into the range in column A. I have uploaded and example workbook to my original post to show what i am trying to achieve
-
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
For Each cel In Range("A3:A100")
If cel.Value >= 1 / 1 / 2020 Then
cel.Offset(0, 1) = Worksheets("admin").Range("D1") ' THIS SHOULD BE THE VALUE OF D2 on the admin tab
Else
cel.Offset(0, 2) = Worksheets("admin").Range("B1") 'THIS SHOULD BE THE VALUE OF E2 on the admin tab
End If
Next cel
Application.EnableEvents = True
End Sub
-
Thank you, slight tweak but you got me there - very much appreciate your help.
-
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3:A100")) Is Nothing then SetBE Target
End Sub
Code:
Private Sub SetBE(Target As Range)
Dim Cel As Range
Set Cel = Target 'Stop using var Target ASAP
If Cel.Count > 1 Then Exit Sub 'Change was not made by a User
Appplication.EnableEvents = False 'Don't let the Changes to B and E trigger another Change Event
With Sheets("ADMIN")
' "Cel" is still on the "Targeted" Sheet
' An unqualified "Space Dot Range" is on the "With'ed" Sheet
' "Cells" with no dot refers to the "Targeted" Sheet
If Cel >= 1/1/2020 Then
Cel.Offset(0, 1) = .Range("D2")
Else
'Alternate style of addressing
Cells(Cel.Row, "E") = .Range("E2")
End If
End With
Appplication.EnableEvents = True
End Sub