Snasui Excel Forum

Code: Select all

Sub EPS_8()

    Dim rAll As Range, r As Range
    Dim lngStart As Long, lngEnd As Long
    Dim rData As Range, rd As Range
    Dim amt As Double
    Dim a, b As Double
        With Sheets("Sheet8")
            Set rAll = .Range("a3", .Range("a" & Rows.Count).End(xlUp))
            rAll.Select
            For Each r In rAll
     '           lngStart = r.End(xlToRight).Offset(0, 1).Column
                lngStart = .Cells(r.Row, "d").End(xlToRight).Offset(0, 0).Column
                .Cells(r.Row, "d").End(xlToRight).Offset(0, 0).Select
                
                lngEnd = .Cells(r.Row, "m").End(xlToLeft).Offset(0, -1).Column
                .Cells(r.Row, "m").End(xlToLeft).Offset(0, -1).Select

                    a = .Cells(r.Row, "d").End(xlToRight).Offset(0, 0).Value
                    .Cells(r.Row, 15) = a
                    b = .Cells(r.Row, "m").End(xlToLeft).Offset(0, -1).Value
                    .Cells(r.Row, 16) = b
                
                If lngEnd - lngStart >= 0 Then
                    Set rData = .Range(.Cells(r.Row, lngStart), .Cells(r.Row, lngEnd))
                    rData.Select
                    amt = Application.Sum(rData)
                    .Cells(r.Row, "n") = amt / (lngEnd - lngStart + 1)
                    .Range("l3", "z700").NumberFormat = "#,##0.00"
                    .Cells(r.Row, 17) = amt
                End If
            Next r
        End With
    Call Sound_2
End Sub

Code: Select all

Sub test()
    Dim rAll As Range, r As Range
    Dim lngStart As Long, lngEnd As Long
    Dim rData As Range
    Dim amt As Double
    With Sheets("Sheet14")
        Set rAll = .Range("a3", .Range("a" & Rows.Count).End(xlUp))
        For Each r In rAll
            lngStart = r.End(xlToRight).Offset(0, 1).Column
            lngEnd = .Cells(r.Row, "m").End(xlToLeft).Offset(0, -1).Column
            If lngEnd - lngStart >= 0 Then
                Set rData = .Range(.Cells(r.Row, lngStart), .Cells(r.Row, lngEnd))
                amt = Application.Sum(rData)
                .Cells(r.Row, "n") = amt / (lngEnd - lngStart + 1)
            End If
        Next r
    End With
End Sub