Macro to copy down formula until answer = a value

[Stebaker]

Hi,

I'm having problems writing some basic code as a newbie:

<CODE>
Sub DateFormulas()
<CODE></CODE>
<CODE>'Firstly, enter in A1 the most recent work day</CODE>
<CODE></CODE>
<CODE>Application.Goto Reference:="R2C1"
ActiveCell.FormulaR1C1 = _
"=IF(WEEKDAY(TODAY(),2)=6,TODAY()-1,IF(WEEKDAY(TODAY(),2 =7,TODAY()-2,TODAY()))"
<CODE>
</CODE>
<CODE></CODE>
<CODE>'Secondly in A2 enter the previous working day date</CODE>
<CODE></CODE>
<CODE>Range("A3").Select
ActiveCell.FormulaR1C1 = _
"=IF(WEEKDAY(R[-3]C[-3],2)=1,R[-3]C[-3]-3,R[-3]C[-3]-1)"
<CODE>

</CODE><CODE><CODE></CODE>
<CODE></CODE>
<CODE>Could you please help by advising on my next lines of code which copies the formula down but only until the cell where the formula result = 02/03/2009?</CODE>
<CODE></CODE>
<CODE>Thanks so much for your help</CODE>
<CODE></CODE>
<CODE>Steve
</CODE></CODE>
</CODE></CODE>
</CODE>

 

[texasalynn]

Welcome to MrExcel board...
so try this

FDate = "2/3/2009"
FDRng = Cells.Find(What:=FDate, After:=ActiveCell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address(0, 0)
Cells(3, 1).Copy Destination:=Range(Cells(3, 1), Cells(Range(FDRng).Row - 1, Range(FDRng).Column))

 

[AlphaFrog]

Hi and welcome.

This fills in the weekday dates in column A without using the cell formulas.

Sub Fill_Weekday_Dates()

    Application.ScreenUpdating = False

    ' Clear previous dates if any
    Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1)).ClearContents

    With Range("A2")
        ' Most recent weekday date in cell A2
        .Value = Date - IIf(Weekday(Date, vbSaturday) > 2, 0, Weekday(Date, vbSaturday))
        
        ' Fill weekdays below A2 down to date "2/3/2009"
        .DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlWeekday, _
                    Step:=-1, Stop:=DateValue("2/3/2009"), Trend:=False
    End With

    ' Format cells in column A
    Range("A:A").NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
    Range("A:A").Columns.AutoFit
    
    Application.ScreenUpdating = True

End Sub

 

[Stebaker]

Thanks to you both for the quick replies!

AlphaFrog, I very much appreciate the improved coding for the formulas, this works just as I wished

If you have any recommended reading I'd love to learn more.

Awesome support,

Steve