paste formula based on other column
Posted by Kevin on June 22, 2001 8:23 AM
Hi, I'm trying to write a pretty simple procedure that pastes the "Day" formula into column S based on a date in column E. I'm obviously mishandling the range somehow, but I can't seem to fix it. Here's my code:
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.
Posted by Damon Ostrander on June 22, 2001 8:42 AM
Hi Kevin,
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
