Better Way To Implement Indirect IF Function?

B

BJ

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
 
S

Soo Cheon Jheong

Hi,

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

If TypeName(Selection) <> "Range" Then GoTo e:
If Selection.Areas.Count > 1 Then GoTo e:

With Selection.Columns(1)
.NumberFormat = "General"
.FormulaR1C1 = "=IF(RC[-1]=10,""Hello"",""Goodbye"")"
Application.Calculate
.Value = .Value
End With
e:

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
D

Dave Peterson

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


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
Click to expand...
 
B

BJ

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 said:
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


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
Click to expand...
Click to expand...
 
D

Dave Peterson

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.
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
B

BJ

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 said:
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.
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I thought you were trying to make it calculate--but you were trying to make it a
formula--not text:

Option Explicit
Sub testme()

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

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 said:
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.
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?

:

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
Click to expand...
Click to expand...
Click to expand...
 
B

BJ

Worked perfect....just needed it to say either one text string or another.
Thanks so much for all your help!

BJ

Dave Peterson said:
I thought you were trying to make it calculate--but you were trying to make it a
formula--not text:

Option Explicit
Sub testme()

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

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 said:
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?

:

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
Click to expand...
Click to expand...
Click to expand...
 
Top