Thanks so much for your help, but the problem is that the formula appears in
all the cells but now it doesn't calculate it. It leaves it at
"="=if(rc[-1]=10,""Hello"",""Goodbye"")" This is why I did what I did in my
code to make it calculate.

Thanks again. Any more thoughts??

"Dave Peterson" wrote:

Oopsie...

Option Explicit
Sub testme()

With Selection
.FormulaR1C1 = "=if(rc[-1]=10,""Hello"",""Goodbye"")"
Application.Calculate
.Value = .Value
End With
End Sub

The application.calculate shouldn't be necessary if calculation is set to
automatic--but it shouldn't hurt, either.

BJ wrote:

This didn't quite work because I need it to work for the current selection
(range of cells) not just one cell....I tried replacing ActiveCell with
Selection but it was a type mismatch. Thoughts?

"Dave Peterson" wrote:

Dim myCell as range
set mycell = activecell
with mycell
if .offset(0,-1).value = 10 then
.value = "Hello"
else
.value = "Goodbye"
end if
end with



BJ wrote:

The code takes a selection, enters a formula which looks at the column to the
left & same row of the selection, then makes the selection calculate by doing
a replace function, and then copies and pastes as values. Is there a neater,
less round-about way to do this?

Selection.FormulaR1C1 = "=IF(CELL(""contents"",INDIRECT(""RC[-1]"",FALSE)
=10,""Hello"",""Goodbye"")"
Selection.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

--

Dave Peterson


--

Dave Peterson