CODEWITHSUNDEEP
excelvbaexcel-formula
Gavin
Gavin

Reputation: 27

VBA Formula - Adjustment per Row Data

 Sub Sample()

     Dim lastRow As Long, i As Long
     Dim ws As Worksheet

     Set ws = Sheets("Storage NSNR")

     lastRow = ws.Range("K" & Rows.Count).End(xlUp).Row

     With ws
         For i = 1 To lastRow
             If Len(Trim(.Range("K" & i).Value)) <> 0 Then _
             .Range("L" & i).Formula = "=(NETWORKDAYS(J2, K2)-1)*($P$1-$O$1)+IF(NETWORKDAYS(K2,K2),MEDIAN(MOD(K2,1), $P$1,
 $O$1),$P$1)-MEDIAN(NETWORKDAYS(J2,J2)*MOD(J2,1), $P$1, $O$1)"
         Next i
     End With

 End Sub

Hi,

Can anyone help me on how can I adjust the formula:

=(NETWORKDAYS(J2, K2)-1)*($P$1-$O$1)+IF(NETWORKDAYS(K2,K2),MEDIAN(MOD(K2,1), $P$1, $O$1),$P$1)-MEDIAN(NETWORKDAYS(J2,J2)*MOD(J2,1), $P$1, $O$1)

I'm looking to automatically adjust "J2" and "K2" to "J3" and "K3" and so on, similar to dragging down the formula. I've looked everywhere but I can't seem to find the correct way of applying it.

Basically, the formula from the next column, "L1", until the last available entry for "K".

Any help would be appreciated. Thanks.

Upvotes: 0

Views: 83

Answers (1)

user10981853
user10981853

Reputation:

Change to an xlR1C1 style formula and use .Range("L" & i).FormulaR1C1 = ...

.Range("L" & i).Formular1c1 = "=(NETWORKDAYS(rc10, rc11)-1)*(r1c16-r1c15)+IF(NETWORKDAYS(rc11, rc11), MEDIAN(MOD(rc11, 1), r1c16, r1c15), r1c16)-MEDIAN(NETWORKDAYS(rc10, rc10)*MOD(rc10,1), r1c16, r1c15)"

If you are not too familiar with xlR1C1 syntax, let VBA do the work.

 Sub Sample()

     Dim i As Long, fA1 As String, fR1C1 As String

     fA1 = "=(NETWORKDAYS(J2, K2)-1)*($P$1-$O$1)+IF(NETWORKDAYS(K2,K2),MEDIAN(MOD(K2,1), $P$1, $O$1),$P$1)-MEDIAN(NETWORKDAYS(J2,J2)*MOD(J2,1), $P$1, $O$1)"
     fR1C1 = Application.ConvertFormula(Formula:=fA1, _
                                        FromReferenceStyle:=xlA1, _
                                        ToReferenceStyle:=xlR1C1, _
                                        RelativeTo:=Range("L2"))

     Debug.Print fR1C1
     '=(NETWORKDAYS(RC[-2], RC[-1])-1)*(R1C16-R1C15)+IF(NETWORKDAYS(RC[-1],RC[-1]),MEDIAN(MOD(RC[-1],1), R1C16, R1C15),R1C16)-MEDIAN(NETWORKDAYS(RC[-2],RC[-2])*MOD(RC[-2],1), R1C16, R1C15)

     With Worksheets("Storage NSNR")
         With .Range(.Cells(2, "K"), .Cells(Rows.Count, "K").End(xlUp))
            .SpecialCells(xlCellTypeConstants, xlNumbers).Offset(0, 1).FormulaR1C1 = fR1C1
         End With
     End With

 End Sub

Upvotes: 1

Related Questions