Better Way To Implement Indirect IF Function?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find and Replace is going wrong in some cases 20
Error 400 when running Macro 3
Problem with Macro 7
Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
to read text tablimited file 4
Help with a loop please. 10
Replaceformat problem 2

Back
Top