Results 1 to 5 of 5

Thread: macro to fill the formula for a dynamic range

  1. #1

    macro to fill the formula for a dynamic range



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

    i Have a xl sheet with login and logout details.

    in time and out time

    i have the formula to find the time differnece. but the formula has to be applied to the used range.

    for example in my sample in A3 i have in time and in A4 i have out time, the difference has to be in C4.


    same way i have many in and out... please refer the sample file attached and help me with a macro


    sample.xlsx

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,819
    Articles
    0
    Excel Version
    O365
    Code:
    Public Sub AddTime()
    Dim lastrow As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            With .Cells(3, "C").Resize(lastrow - 2)
            
                .Formula = "=IF(B3=""OUT"",A3-A2,"""")"
                .Value = .Value
                .NumberFormat = "h:mm"
            End With
        End With
    
    
        Application.ScreenUpdating = True
    End Sub
    Last edited by Bob Phillips; 2015-03-01 at 12:02 AM.

  3. #3
    Thanks a lot. works great

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,819
    Articles
    0
    Excel Version
    O365
    I have modified the original code to include this

    Code:
    Public Sub AddTime()Const FORMULA_TOTAL As String = _
        "=IF(A1=""NAME"",SUM(INDEX($C$1:$C$<lastrow>,MAX(IF($A$1:$A1=""NAME"",ROW($A$1:$A1)))):INDEX($C$1:$C$<lastrow>,MIN(IF($A2:$A$<lastrow>=""NAME"",ROW($A2:$A$<lastrow>)))-1)),"""")"
    Dim lastrow As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Cells(lastrow + 1, "A").Value = "NAME"
            lastrow = lastrow + 1
            
            With .Cells(2, "C").Resize(lastrow - 2)
            
                .Formula = "=IF(B2=""OUT"",A2-A1,"""")"
                .Value = .Value
                .NumberFormat = "h:mm"
            End With
            
            With .Cells(1, "D")
            
                .FormulaArray = Replace(FORMULA_TOTAL, "<lastrow>", lastrow)
                .AutoFill .Resize(lastrow - 1)
                With .Resize(lastrow - 1)
                
                    .Value = .Value
                    .NumberFormat = "h:mm"
                End With
            End With
            
            .Cells(lastrow + 1, "A").ClearContents
        End With
    
    
        Application.ScreenUpdating = True
    End Sub

  5. #5
    Thanks BOb. u r genius

Posting Permissions

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