Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
Sub setDay()
Dim myDay As Range
For Each myDay In Range("E6:E65536")
If myDay <> "" Then
ActiveCell.Offset(0, 14).Activate
ActiveCell.FormulaR1C1 = "=TEXT(R1C1,dddd)"
End If
Next myDay
End Sub
The maddening thing is I have a very similar procedure that works fine in pasting the formula, though it only offsets by 1 column.
Please help.
The problem is simply that the cell myDay is not active, so when the code says
ActiveCell.Offset(0,14).Activate
it bases the ActiveCell on whatever cell was selected prior to running the macro. In addition, the format argument of the TEXT function is a string, and must be enclosed in quotes, which have to be doubled since they are enclosed in quotes. Here is what the code should look like:
Sub setDay()
Dim myDay As Range
For Each myDay In [E6:E65536]
If myDay <> "" Then myDay.Offset(0,14).FormulaR1C1 = "=TEXT(R1C1,""dddd"")"
Next myDay
End Sub
This, of course, will put the contents of cell A1 in column S of every row from six on that has contents in column E, and format it as days.
Good luck.
Damon