VBA To Enter Formulas Into Range & Copy

  • I'm trying to shed some 'fat' off two macros I have, trying to make them run as quickly as possible. I wonder if my macros below can be further optimized:

    i = 9
    Do Until Worksheets("RST").Cells(i, 2) = ""
    Range("H9").FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[1]=""""),WORKDAY(RC[-2],5,RC[-2]),"""")"
    Range("H9").Copy Destination:=Worksheets("RST").Cells(i, 8)
    i = i + 1


    Sheets("RST Pivot").Select
    Range("G9").PasteSpecial Paste:=xlPasteValues
  • Re: Optimizing Copying Method

    You don't need to use the select statement.

    Application.ScreenUpdating = False
        Sheet1.Range("H6:I1000").Copy 'sheete code name used - alter to suit
        Sheet2.Range("G9").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
  • Re: Optimizing Copying Method

    Sub blah()
    Sheets("RST Pivot").Range("H6:I1000").Value = Sheets("RST").Range("G9:H1003").Value
    End Sub

    or if you won't know the size of the range

    Sub blah2()
    Set SourceRng = Sheets("RST").Range("G9:H1003")
    Sheets("RST Pivot").Range("H6").Resize(SourceRng.Rows.Count, SourceRng.Columns.Count).Value = SourceRng.Value
    End Sub

    For the first macro, you don't need to enter formulae one cell at a time; the likes of

    Range("H9:H1003").FormulaR1C1 = "=IF(AND(RC[-2]<>"""",RC[1]=""""),WORKDAY(RC[-2],5,RC[-2]),"""")"

    will do them all in one hit

  • Re: VBA To Enter Formulas Into Range &amp; Copy

    Thanks for the wonderful suggestions, they work beautifully! I love this community because it's easy to learn new things quickly. I find that no matter how many books I read about VBA, there's rarely an example mentioned to suit my needs. That's why this community works much better than any books.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!