Excel VBA formula

[Biz]

Dear All,

Macro works well but I want streamline formula to remove reliance on column A. Not sure how to do this.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o></o>
<o>

[SIZE=3][FONT=Calibri]Sub ShowWorkDays()<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim i As Integer<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim r As Long, NextRow As Long<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Calibri][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Calibri]'Fill Days<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]r = 1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]For i = 1 To Range("S3").Value<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Cells(NextRow, 1) = i<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Cells(NextRow + 1, 1) = i<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    r = r + 1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Next i<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Calibri][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Calibri]'Fill Formula for Workdays<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]With Range("B2:B" & (Range("S3").Value * 2) + 1)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    .Formula = _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        "=IF(WORKDAY(R1C[17]-1,RC1,dHolidays)>R2C[17],"" "",WORKDAY(R1C[17]-1,RC1,dHolidays))"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    .NumberFormat = "dd/mm/yyyy;@"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Calibri][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE]

I believe RC1 needs to be i but I don't know how to do this.
</o>
Reason why 1 day appears twice is because 1 record is from Home to Work and other is Work to Home.<o></o>

Any help would be greatly appreciated.

Biz

 

[GlennUK]

You have to explain what you mean by "I believe RC1 needs to be i" ... what do you want to the formula to do instead of referencing column A?

 

[Biz]

Thanks I fixed it.

'Fill Formula for Workdays
Range("B2:B" & (Range("Workdays").Value * 2) + 1).Formula = _
        "=IF(WORKDAY(StartFY-1,$A2,dHolidays)>EndFY,"" "",WORKDAY(StartFY-1,$A2,dHolidays))"