ExcelBanter - Better Way To Implement Indirect IF Function?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Better Way To Implement Indirect IF Function? (https://www.excelbanter.com/excel-discussion-misc-queries/29825-better-way-implement-indirect-if-function.html)

Better Way To Implement Indirect IF Function?

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

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

--
Regards,
Soo Cheon Jheong

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

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

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

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

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

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

BJ wrote:

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

--

Dave Peterson

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

BJ

"Dave Peterson" wrote:

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

BJ wrote:

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

--

Dave Peterson

Thanks! This is exactly what I needed! Works great!

BJ

"Soo Cheon Jheong" wrote:

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

--
Regards,
Soo Cheon Jheong

All times are GMT +1. The time now is 02:19 PM.