Whats wrong in my code

[Dinesh_Excel]

Hi Friends,

I am trying to run a formula from column m2 to the last known row in that date.
while doing so the following error is being prompted ...

Apprecaite your help on the same.

 

[Hui]

You were close

try the following code

Sub fry3()
Range("A2").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
For i = 2 To lastrow
  Sheets("Sheet1").Range("M" & CStr(i)).Formula = "=IF(MEDIAN(TODAY()-WEEKDAY(TODAY()-2)-7,H" & CStr(i) & ",TODAY()-WEEKDAY(TODAY()-2)-7+5)=H" & CStr(i) & ",""Last Week"","""")"
Next i
End Sub

 

[shrivallabha]

First thing, you don't need a loop for this. Here's how I do it.
- Apply formula in Excel as normal.
- Press F2 and copy formula to Visual Basic Editor.
- Select Text and then do Find (") and replace("") with option "Selected Text"
- And then wrap it with quotes.

Public Sub ApplyFormula()
Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(MEDIAN(TODAY()-WEEKDAY(TODAY()-2)-7,H2,TODAY()-WEEKDAY(TODAY()-2)-7+5)=H2,""Last Week"","""")"
End Sub

 

[Dinesh_Excel]

Thanks Hui and shrivallabha for your help...Apprecaite on the same.