Q:

Excel vba range array problem, what can i do with it so it does work?

Public Function procent(fv As Double, time As Double) As Double

    procent = fv* (0.1 / 365) * time'time= diffrence between 2 dates from specific cells , fv= numeric value from cell

End Function

 Sub testo()
        Dim i As Integer, n As Integer
        Dim emptyrow As Long
        Dim kom As Double, komz As Double, dw As Double, roz As Double, komr As Double, komn As Double
        Dim napis As String
        Dim dz As Date

        'Make Sheet1 active
        Sheets("procenty").Select
        'Determine emptyRow
        emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1

        dz = Range("A1:A70").Value
        komz = Range("B1: B70").Value
        kom = Range("D1:D70").Value
        dw = Range("C1:C70").Value

        For Each Row In Range("A1:D70")
            komr = komz - kom
            roz = Abs(dz - dw)
            komn = kom - komz

            If kom = komz And dw > dz Then

                Cells(emptyrow, 1).Value = procent(kom, roz)
                Cells(emptyrow, 2).Value = procent(kom, roz) + kom

            ElseIf komz = kom And dw = dz And dz > dw Then

                Cells(emptyrow, 1).Value = napis
                Cells(emptyrow, 2).Value = napis

            ElseIf komz > kom And dz < dw Then

                Cells(emptyrow, 1).Value = procent(kom, roz)
                Cells(emptyrow, 2).Value = procent(kom, roz) + kom
                Cells(emptyrow, 3).Value = komr
                Cells(emptyrow, 4).Value = procent(komr, roz)
                Cells(emptyrow, 5).Value = procent(komr, roz) + komr

            ElseIf komz > kom And dz > dw Then
                Cells(emptyrow, 1).Value = komr

            ElseIf komz < kom Then
                Cells(emptyrow, 1).Value = komn
                Exit For
            End If
        Next Row
    End Sub

this whole sub has to get data form this selected range and then make some operations with it in loop  like multiplying,addition,difference,dividing with another specific cells like this A1*C1 then A2*C2 then check if conditions on which one is good and do what's inside if and so on until all of the cells are done.The whole problem about this is that i get a lot of errors here like type mismatch,by ref argument mismatch and also when I use my own function it doesn't do nothing with it.

A:

Loop Through Array Instead of Range

This should work if the procent function and the "Do your thing"
parts are correct.
Exit for looks pretty suspicious.
NOT tested.

The Code
Option Explicit

Sub testo()

    Const cSheet As String = "Procenty"   ' Source Worksheet Name
    Const cRange As String = "A1:D70"     ' Source Range Address
    Const cTarget As Long = 5             ' Target Number of Columns
    Const cCol As Variant = "A"           ' Last-Row Column

    Dim vntS As Variant   ' Source Array
    Dim vntT As Variant   ' Target Array
    Dim i As Integer      ' Source/Target Array Row Counter
    Dim emptyRow As Long  ' First Empty Row

    Dim kom As Double, komz As Double, dw As Double
    Dim roz As Double, komr As Double, komn As Double
    Dim napis As String
    Dim dz As Date

    ' Write Source Range to Source Array.
    vntS = ThisWorkbook.Worksheets(cSheet).Range(cRange)

    ' Resize Target Array to rows of Source Array and columns of Target
    ' Number of Columns.
    ReDim vntT(1 To UBound(vntS), 1 To cTarget)

    ' In Source Array
    For i = 1 To UBound(vntS)

        ' Write values of current row to variables.
        dz = vntS(i, 1)
        komz = vntS(i, 2)
        dw = vntS(i, 3)
        kom = vntS(i, 4)

        '*******************************
        ' Start of "Do your thing".
        '*******************************

        komr = komz - kom
        roz = Abs(dz - dw)
        komn = kom - komz

        If kom = komz And dw > dz Then

            vntT(i, 1) = procent(kom, roz)
            vntT(i, 2) = procent(kom, roz) + kom

        ElseIf komz = kom And dz >= dw Then

            vntT(i, 1) = napis
            vntT(i, 2) = napis

        ElseIf komz > kom And dz < dw Then

            vntT(i, 1) = procent(kom, roz)
            vntT(i, 2) = procent(kom, roz) + kom
            vntT(i, 3) = komr
            vntT(i, 4) = procent(komr, roz)
            vntT(i, 5) = procent(komr, roz) + komr

        ElseIf komz > kom And dz > dw Then
            vntT(i, 1) = komr

        ElseIf komz < kom Then
            vntT(i, 1) = komn
            Exit For
        End If

        '*******************************
        ' End of "Do your thing".
        '*******************************

    Next

    ' In Source Worksheet
    With ThisWorkbook.Worksheets(cSheet)
        ' Determine emptyRow (Should be more reliable this way.).
        emptyRow = .Columns(cCol).Find("*", , xlFormulas,  _
                xlWhole, xlByColumns, xlPrevious).Row + 1
        'emptyRow = WorksheetFunction.CountA(.Columns(cCol)) + 1
        ' Write Target Array to Target Range.
        .Cells(emptyRow, cCol).Resize(UBound(vntT), UBound(vntT, 2)) = vntT
    End With

End Sub

