Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT function to specify source data | Charts and Charting in Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Sum Indirect function through multiple sheets | Excel Discussion (Misc queries) | |||
INDIRECT function question | Excel Worksheet Functions |