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
|