Consulting

Results 1 to 6 of 6

Thread: IF Statement using VBA

  1. #1
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    122
    Location

    IF Statement using VBA

    Example.xlsxHi

    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
    Last edited by mykal66; 02-13-2020 at 08:34 AM. Reason: Add example workbook

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    750
    Location
            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

  3. #3
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    122
    Location
    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

  4. #4
    VBAX Expert
    Joined
    Aug 2004
    Posts
    750
    Location
    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

  5. #5
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    122
    Location
    Thank you, slight tweak but you got me there - very much appreciate your help.

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,074
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •