Results 1 to 3 of 3

Thread: Connecting 2 formulas into one - i guess very easy

  1. #1

    Connecting 2 formulas into one - i guess very easy



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi I would like to connect 2 separate things into one worksheet_change (better than double click). I am a little green yet so could You please help me out guys?
    I tried "else" and "else if" , didnt work out. Thanks in advance


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    
    
    Set rInt = Intersect(Target, Range("D: D"))
    If Not rInt Is Nothing Then
    For Each rCell In rInt
    Set tCell = rCell.Offset(0, 1)
    If IsEmpty(tCell) Then
    tCell = Now
    tCell.NumberFormat = "hh:mm"
    End If
    Next
    End If
    End Sub







    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal _
    Target As Range, Cancel As Boolean)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    
    
    Set rInt = Intersect(Target, Range("B:B"))
    If Not rInt Is Nothing Then
    For Each rCell In rInt
    Set tCell = rCell.Offset(0, 1)
    If IsEmpty(tCell) Then
    tCell = Now
    tCell.NumberFormat = "hh:mm"
    End If
    Next
    End If
    End Sub
    Last edited by AliGW; 2018-02-13 at 07:31 AM.

  2. #2
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,007
    Articles
    0
    Excel Version
    Office 365 Subscription
    Welcome to the forum! As a courtesy, I've added code tags for you this time - the # button is available by choosing Go Advanced when typing your post. Please do this for yourself in future. Thanks!
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Does this work ?
    (Every line is commented so you can easily follow)
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        'limit to single cell
        If Target.Count > 1 Then Exit Sub
        'only if in column B or D
        If Target.Column <> 2 And Target.Column <> 4 Then Exit Sub
        'might want to exclude header row(s)
        If Target.Row < 2 Then Exit Sub
        
        ' OK to proceed if you get to here
        If IsEmpty(Target.Offset(, 1)) Then
            'prevent this sub from calling itself
            Application.EnableEvents = False
            'write info
            Target.Offset(, 1) = Format(Now, "hh:mm")
            're-enable events
            Application.EnableEvents = True
        End If
    End Sub

Posting Permissions

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