Formulas not working correctly

[Curt D.]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[bj]

please post the code around the formula

 

[Curt D.]

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

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

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

 

[Darren Bartrup]

It seems to be working for me.

It's putting the formula:
=IF(B2>0,IF(S2>0.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 S2>0.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).

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

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.