Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
I have a formula in a macro that combines (concatenate) to cells, the formula
is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
I have a formula in a macro that combines (concatenate) to cells, the
formula is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. Can you show us your (relevant) macro code? Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
please post the code around the formula
"Curt D." wrote: I have a formula in a macro that combines (concatenate) to cells, the formula is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
Here is the formula and the code around it.
Columns("X:X").Select Selection.Insert Shift:=xlToRight Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-22]0,CONCATENATE(RC[1],"" "",RC[5]),"""")" Range("X2").Select Selection.Copy Columns("X:X").Select ActiveSheet.Paste "Curt D." wrote: I have a formula in a macro that combines (concatenate) to cells, the formula is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
I got it working, for some reason I had to remove a space in the cell, here
is the code I had to use to correct it. Columns("X:X").Select Selection.Insert Shift:=xlToRight Range("X2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-22]0,CONCATENATE(RC[1],"" "",RC[5]),"""")" Range("X2").Select Selection.Copy Columns("X:X").Select ActiveSheet.Paste Columns("X:X").Select Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False "Curt D." wrote: I have a formula in a macro that combines (concatenate) to cells, the formula is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
I have another formula that gives me the same answer everytime when it
shouldn't. The answer I get is "PLANNING", here is the code for this problem. Any ideas on this one? Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-7]0,IF(RC[10]0.1,""Sheetmetal"",IF(OR(RC[-2]=""C"",RC[-2]=""D"",RC[-2]=""M"",RC[-2]=""P"",RC[-2]=""X""),""Deferral"",IF(OR(RC[-2]=""H"",RC[-2]=""N"",RC[-2]=""Q"",RC[-2]=""T""),""Alert"",IF(OR(RC[-2]=""E"",RC[-2]=""F"",RC[-2]=""K"",RC[-2]=""S"",RC[-2]=""U""),""Planning"","""")))),"""")" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("I1").Select Application.CutCopyMode = False Selection.ClearContents "Curt D." wrote: I have a formula in a macro that combines (concatenate) to cells, the formula is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas not working correctly
It seems to be working for me.
It's putting the formula: =IF(B20,IF(S20.1,"Sheetmetal",IF(OR(G2="C",G2="D ",G2="M",G2="P",G2="X"),"Deferral",IF(OR(G2="H",G2 ="N",G2="Q",G2="T"),"Alert",IF(OR(G2="E",G2="F",G2 ="K",G2="S",G2="U"),"Planning","")))),"") into cell I2 Which follows the following logic: Check that B2 0 If it is then check if S2 0.1, otherwise leave the cell blank If it is then the answer is "Sheetmetal", otherwise check if G2 = C,D,M,P or X If it is then the answer is "Deferral", otherwise check if G2 = H,N,Q or T If it is then the answer is "Alert", otherwise check if G2 = F,K,S or U If it then the answer is "Planning", otherwise the cell is left blank So if B2 = 0 then the cell is blank If cell S20.1 then the answer is sheetmetal Otherwise it depends on the value of G2. This is then copied to all rows in column I. And then the values are pasted over the formula. Worked fine for me :) Though you don't have to select the cell to perform an action on it in VBA. Range("I2").FormulaR1C1 = _ "=IF(RC[-7]0,IF(RC[10]0.1,""Sheetmetal"",IF(OR(RC[-2]=""C"",RC[-2]=""D"",RC[-2]=""M"",RC[-2]=""P"",RC[-2]=""X""),""Deferral"",IF(OR(RC[-2]=""H"",RC[-2]=""N"",RC[-2]=""Q"",RC[-2]=""T""),""Alert"",IF(OR(RC[-2]=""E"",RC[-2]=""F"",RC[-2]=""K"",RC[-2]=""S"",RC[-2]=""U""),""Planning"","""")))),"""")" will work just as well as selecting the cell first (and the user won't see the cursor jumping all over the place). "Curt D." wrote: I have another formula that gives me the same answer everytime when it shouldn't. The answer I get is "PLANNING", here is the code for this problem. Any ideas on this one? Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-7]0,IF(RC[10]0.1,""Sheetmetal"",IF(OR(RC[-2]=""C"",RC[-2]=""D"",RC[-2]=""M"",RC[-2]=""P"",RC[-2]=""X""),""Deferral"",IF(OR(RC[-2]=""H"",RC[-2]=""N"",RC[-2]=""Q"",RC[-2]=""T""),""Alert"",IF(OR(RC[-2]=""E"",RC[-2]=""F"",RC[-2]=""K"",RC[-2]=""S"",RC[-2]=""U""),""Planning"","""")))),"""")" Range("I2").Select Selection.Copy Columns("I:I").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("I1").Select Application.CutCopyMode = False Selection.ClearContents "Curt D." wrote: I have a formula in a macro that combines (concatenate) to cells, the formula is correct however when the macro runs it does not combine the cells. If I stop the macro at that point where they should combine and go in to the address bar and go to the end of the formula and press enter the formula will start working. I don't want to go into each formula and press enter just to get it working correctly. Does anyone know what is causing this? Any help or explanation would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal function is not working correctly | Excel Discussion (Misc queries) | |||
vlookup isn't working correctly? | Excel Discussion (Misc queries) | |||
Word Wrap not working correctly | Excel Worksheet Functions | |||
Autofilter not working correctly... | Excel Discussion (Misc queries) | |||
Cursor not working correctly | Excel Discussion (Misc queries) |