PDA

View Full Version : [SOLVED] IF Statement using VBA



mykal66
02-13-2020, 07:48 AM
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


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

JKwan
02-13-2020, 08:15 AM
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

mykal66
02-13-2020, 08:27 AM
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

JKwan
02-13-2020, 09:09 AM
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

mykal66
02-13-2020, 09:30 AM
Thank you, slight tweak but you got me there - very much appreciate your help.

SamT
02-13-2020, 10:07 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3:A100")) Is Nothing then SetBE Target
End Sub


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