vendredi 29 avril 2016

insert formula with function in vba

I have made two functions that makes a calculation from two different linest (UpperCalc and LowerCalc). This works fine. Then I will need to fill in formulas for an unknown number of cells (depending on the input on another sheet). I have been able to fill in formulas for the correct number of cells. But when I try to include an "IF"-formula in the VBA programming together with the function names, it does not work? My VBA code to fill in the formula looks like this now;

Dim lastRow As Long, i As Long, ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1") Set ws2 = Sheets("Sheet2")

lastRow = ws1.Range("B" & Rows.Count).End(xlUp).Row

     With ws1
        For i = 2 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            ws2.Range("A" & i).Value = ws1.Range("A" & i).Value
            ws2.Range("B" & i).Value = ws1.Range("B" & i).Value
            ws2.Range("C" & i).Formula = "=IF(RC[-1]>R4C12,UpperCalc(RC[-1]),LowerCalc(RC[-1]))"
        Next i
    End With
End If

I am able to insert formula with one of these functions (for instance ws2.Range("C" & i).Formula = "=UpperCalc(RC[-1])", and also only with an "IF" formula (for instance ws2.Range("C" & i).Formula = "=IF(RC[-1]>R4C12,RC[-1],RC[-1]^2)" - This is of course not the actual calculation needed - only to test the "IF"-function). Since the calculation behind UpperCalc and LowerCalc is rather "dirty", I would like to utilize the functions. Any ideas?

Aucun commentaire:

Enregistrer un commentaire