0

I would like to paste a formula on a range based on a date. the dates are from column L7 to AP7 1st to the 31st. The formula should select a dynamic range below the date and paste the formula.

I did a macro and it only selects the range that was selected on the macro

s_date = Sheets("PnA").Range("L1") 
Range("L5").Select 
Selection.Copy 
Range("L7:AP7").Select 
Selection.Find(What:=s_date, After:=ActiveCell, LookIn:=xlFormulas _ , 
     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
     MatchCase:=False, SearchFormat:=False).Activate 
ActiveCell.Select 
Selection.Offset(1, 0).Select 
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ 
    SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False"
Selection.AutoFill Destination:=Range("L8:L673")
Range("L8:L673").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False"
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Aasif Hamid
  • 1
  • The macro you provided is copying and pasting in the same exact range. It is also unclear to me what the dates have to do with the range as the code you provided does nothing with dates – Tim Stack Feb 11 '19 at 09:56
  • Hi Tim below is the 1st part of the macro"s_date = Sheets("PnA").Range("L1") Range("L5").Select Selection.Copy Range("L7:AP7").Select Selection.Find(What:=s_date, After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Select Selection.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False" – Aasif Hamid Feb 11 '19 at 09:59
  • Using `Select` and `Activate` is bad practice. Use e.g. `Range("L5").Copy` instead – Tim Stack Feb 11 '19 at 10:07
  • 1
    [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Feb 11 '19 at 10:12

1 Answers1

0

This is how far I can get with the limited information you provided, it's really not clear what you are trying to accomplish

Sub finddate ()
With Workbooks(REFERENCE).Sheets(REFERENCE) 'Change

s_date = .Range("L1") 
Set fdate = .Range("L7:AP7").Find(s_date, LookIn:=xlFormulas, LookAt:=xlPart)

If Not fdate is Nothing Then
    fdate.Offset(0,1).AutoFill Destination:=.Range(fdate.Offset(0,1) & ":" & fdate.Offset(1,673))'I am assuming there is a formula to the right of the date which you want autofilled down
End If

End With
End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • Hi Tim. what i am trying to accomplish is to copy the cell L5 with the formula and the search for the current date once the date is found, the macro must select the cell below the date and paste the formula once the formula is pasted i need the formula to be copied and pasted in the selected range. the range needs to be dynamic because the dates are on different columns. once the formula is pasted in selected range it should copy and paste special values. – Aasif Hamid Feb 11 '19 at 10:40
  • Let me get this clear: do you need the formula to be added to a range selected by the user, or do you want the formula to autofilled in the column it was placed in when you found today's date? – Tim Stack Feb 11 '19 at 11:05
  • Yes I want the formula to auto fill in the column it was placed in when It finds the date. – Aasif Hamid Feb 11 '19 at 11:18