Macro to copy down formula until answer = a value

Stebaker

New Member
Joined
Mar 7, 2011
Messages
6
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>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Welcome to MrExcel board...
so try this

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
Hi and welcome.

This fills in the weekday dates in column A without using the cell formulas.
Code:
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

New Member
Joined
Mar 7, 2011
Messages
6
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
 

Forum statistics

Threads
1,141,844
Messages
5,708,928
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top