0

So, I have created a pretty extensive macro and I am updating/appending to it.

Long story short, the following line triggers the run-time 1004 error, but I do not know why.

ActiveCell.FormulaR1C1 = _ "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" & cn_dif & "],5)=""100-E"",RC[" & brand_dif & "]=""Allen-Bradley®""),""Allen-Bradley® Bulletin 100-E contactors reduce width and weight while saving panel space. With the universal electronic AC/DC coil, selection is easier than ever. And they offer significant energy savings and direct PLC connection. Rockwell Automation® catalog item "" & RC[" & cn_dif & "] & "" is a part of the Bulletin 100-E product family. For more detail, please see the product detail sections below. & CHAR(10) & Allen-Bradley is a trademark of Rockwell Automation."",RC[1])"

This line is contained within a Do While Loop with Multiple If statements underneath it. That is show below:

.
.
.
.
X = 1

Do While X < 53
    If X = 1 Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(LEFT(RC[" & cn_dif & "],4)=""809S"",RC[" & brand_dif & "]=""Allen-Bradley®""),""Get state-of-the-art supplementary protection with Allen-Bradley® Bulletin 809S current monitoring relays. They are easy to add and apply to your motor control circuits. Rockwell Automation® catalog item "" & RC[" & cn_dif & "] & "" is a part of the 809S product family. For more detail, please see the product detail sections below."" & CHAR(10) & ""Allen-Bradley is a trademark of Rockwell Automation."","""")"
        
        Selection.AutoFill Destination:=Range(ActiveCell, Cells(RowCount, ActiveCell.Column))
        Range(Cells(2, ActiveCell.Column), Cells(RowCount, ActiveCell.Column)).Select
                
        Call Paste_1_Column_Right
        
    ElseIf X = 2 Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" & cn_dif & "],5)=""150-F"",RC[" & brand_dif & "]=""Allen-Bradley®""),""With advanced performance, diagnostics, and protection, Allen-Bradley® SMC™ Flex soft starters feature integral bypass and flexible communications. Rockwell Automation® catalog item "" & RC[" & cn_dif & "] & "" is a part of the SMC Flex product family. For more detail, please see the product detail sections below. & CHAR(10) & Allen-Bradley and SMC are trademarks of Rockwell Automation."",RC[1])"

        Selection.AutoFill Destination:=Range(ActiveCell, Cells(RowCount, ActiveCell.Column))
        Range(Cells(2, ActiveCell.Column), Cells(RowCount, ActiveCell.Column)).Select

        Call Paste_1_Column_Right
    
    ElseIf X = 3 Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" & cn_dif & "],5)=""150-C"",RC[" & brand_dif & "]=""Allen-Bradley®""),""Allen-Bradley SMC™-3 soft starters feature compact, three-phase control in a cost-effective package. They include integral bypass and overload protection as well as motor and system diagnostics. Rockwell Automation® catalog item "" & RC[" & cn_dif & "] & "" is a part of the SMC-3 product family. For more detail, please see the product detail sections below. & CHAR(10) & Allen-Bradley and SMC are trademarks of Rockwell Automation."",RC[1])"

        Selection.AutoFill Destination:=Range(ActiveCell, Cells(RowCount, ActiveCell.Column))
        Range(Cells(2, ActiveCell.Column), Cells(RowCount, ActiveCell.Column)).Select

        Call Paste_1_Column_Right
        
    ElseIf X = 4 Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" & cn_dif & "],5)=""100-C"",RC[" & brand_dif & "]=""Allen-Bradley®""),""Space saving, high performance, and low consumption, Allen-Bradley® Bulletin 100-C contactors are designed to control motors and other loads. Catalog item "" & RC[" & cn_dif & "] & "" is a part of the Bulletin 100-C product family. For more detail, please see the product detail sections below. & CHAR(10) & Allen-Bradley is a trademark of Rockwell Automation."",RC[1])"

        Selection.AutoFill Destination:=Range(ActiveCell, Cells(RowCount, ActiveCell.Column))
        Range(Cells(2, ActiveCell.Column), Cells(RowCount, ActiveCell.Column)).Select

        Call Paste_1_Column_Right
        
    'Why is there always an error here????
    ElseIf X = 5 Then '100-E
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" & cn_dif & "],5)=""100-E"",RC[" & brand_dif & "]=""Allen-Bradley®""),""Allen-Bradley® Bulletin 100-E contactors reduce width and weight while saving panel space. With the universal electronic AC/DC coil, selection is easier than ever. And they offer significant energy savings and direct PLC connection. Rockwell Automation® catalog item "" & RC[" & cn_dif & "] & "" is a part of the Bulletin 100-E product family. For more detail, please see the product detail sections below. & CHAR(10) & Allen-Bradley is a trademark of Rockwell Automation."",RC[1])"

        Selection.AutoFill Destination:=Range(ActiveCell, Cells(RowCount, ActiveCell.Column))
        Range(Cells(2, ActiveCell.Column), Cells(RowCount, ActiveCell.Column)).Select

        Call Paste_1_Column_Right
        
    ElseIf X = 6 Then
        ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]="""",RC[1]="""",LEFT(RC[" & cn_dif & "],5)=""100-G"",RC[" & brand_dif & "]=""Allen-Bradley®""),""Get reliable motor load switching with Allen-Bradley® Bulletin 100-G contactors. Rockwell Automation® catalog item "" & RC[" & cn_dif & "] & "" is a part of the Bulletin 100-G product family. For more detail, please see the product detail sections below. & CHAR(10) & Allen-Bradley is a trademark of Rockwell Automation."",RC[1])"

        Selection.AutoFill Destination:=Range(ActiveCell, Cells(RowCount, ActiveCell.Column))
        Range(Cells(2, ActiveCell.Column), Cells(RowCount, ActiveCell.Column)).Select

        Call Paste_1_Column_Right
.
.
.
.
.

There are 52 ElseIf Statements, but only the 5th triggers that error (so far).

These formulas are meant to customize a description field based off certain criteria. That criteria being "IF the first X number of characters in the part number are equal to this, THEN input this generic portion of a description with the part number inserted in a specific place".

The variables that you see in the formulas (i.e. cn_dif and brand_dif) are simply the difference between the current columns index location and two other columns (i.e. catalog number and brand name, respectively). This happens for other columns within the macro as well so it can be more dynamic/flexible. This is because the macro is meant to be distributed and run by different people who carry out the tasks in different ways. Therefore, the locations of specific columns are constantly being updated so that nothing needs to be exported or moved around in order for the macro to run.

I have looked at all the quotes, brackets, parentheses, syntax, etc. and everything seems to be fine.

As I said, it is quite an extensive macro so could it have something to do with memory? Should I clear the memory and then redefine the variables in the macro?

It could very well be that I overlooked a bracket or a comma or something was misplaced, but I have been looking at it for a while and cannot figure it out.

Any and all help is accepted. I could post all the code, but it is quite a bit and the block i posted above is the only place the error occurs. All references seem to be good because the macro runs through the first 4 loops in the Do While and then stops at the 5th.

It is because of this that I assumed I missed a comma or something, but I could be wrong and it could be something else entirely.

Just to reiterate it is only when X=5 that this happens (so far).

Also, I know that the CHAR(10) (i.e. carriage return) has no quotes and ampersands around it, but it seems to be working fine and I have tried adding them in, but it did not resolve this issue.

Thank you!

Let me know if you need the full module (it has multiple sub procedures).

Frosty_Fraz
  • 47
  • 1
  • 11
  • 1
    `Debug.Print` is your friend here. – BigBen Mar 26 '21 at 15:57
  • The `... below. & CHAR(10) & Allen- ...` part of your Formula should be `... below."" & CHAR(10) & ""Allen- ...` ? – chris neilsen Mar 26 '21 at 16:03
  • Ok I will try debug.print. Thank you @BigBen. Thank you too chris neilsen. I have already tried using quotes and ampersands with the carriage return (i.e. CHAR(10)), but I will give that another go as well. I will keep you posted. – Frosty_Fraz Mar 26 '21 at 16:06
  • @BigBen I get FALSE in the immediate window when I run debug.print with the formula I am trying to run – Frosty_Fraz Mar 26 '21 at 17:32
  • @BigBen I checked the other ElseIfs and they come out as FALSE as well, but they work...also, I adjusted the CHAR(10) to have quotes and ampersands as well...same results – Frosty_Fraz Mar 26 '21 at 18:44
  • Are you doing `Debug.Print "yourformula"`? – BigBen Mar 26 '21 at 18:50
  • Yes I am doing Debug.Print Activecell.FormulaR1C1 = _ "" – Frosty_Fraz Mar 26 '21 at 18:52
  • So, what is happening is I have a temporary column that stores the results of each IF in the Do While Loop and then I copy and paste that result out into the column to the right. Essentially, it says if 1 column to the left is blank and one column to the right is blank, then check these other parameters and if they match then input this text. Should I maybe clear the formula from the temporary column each time? Instead of overwriting it? – Frosty_Fraz Mar 26 '21 at 18:54
  • It might be easier to post the full code. I am not a VBA expert, but what I have worked until this point. I can edit the question and put the entire module in if that would help. I could also post an example file to a Google Drive and then post the link so someone can run it and help me troubleshoot because I am at a loss. Can I get more detailed error messages? – Frosty_Fraz Mar 26 '21 at 18:56
  • `Debug.Print "yourformula"`.... drop the `ActiveCell.FormulaR1C1 = ` portion. – BigBen Mar 26 '21 at 19:11
  • Ok I will try that – Frosty_Fraz Mar 26 '21 at 19:15
  • This is the output of the Debug.Print===> =IF(AND(RC[-1]="",RC[1]="",LEFT(RC[3],5)="100-E",RC[5]="Allen-Bradley®"),"Allen-Bradley® Bulletin 100-E contactors reduce width and weight while saving panel space. With the universal electronic AC/DC coil, selection is easier than ever. And they offer significant energy savings and direct PLC connection. Rockwell Automation® catalog item " & RC[3] & " is a part of the Bulletin 100-E product family. For more detail, please see the product detail sections below." & CHAR(10) & "Allen-Bradley is a trademark of Rockwell Automation.",RC[1]) – Frosty_Fraz Mar 26 '21 at 19:31
  • It looks good to me – Frosty_Fraz Mar 26 '21 at 19:31
  • Could it be one of my Locals? I looked there as well and everything seems fine there too. I am starting to think the macro may have too much data/variables/information stored in it. Not sure though. That is just a guess. – Frosty_Fraz Mar 26 '21 at 19:32
  • This is the error I get when I attempt to use that formula: https://i.stack.imgur.com/98aws.png – BigBen Mar 26 '21 at 19:35
  • Ahhhhh ok ok I can do that. No problem. I will try nesting a CONCATENATE in there. I did notice earlier that it was the longest text out of the first 5 IF statements so that could very well be why. I will try and let you know if it ends up working. – Frosty_Fraz Mar 26 '21 at 19:37
  • @BigBen the CONCATENATE worked. I noticed this has been closed and linked to another thread. Can I still give you credit somehow? – Frosty_Fraz Mar 26 '21 at 19:53

1 Answers1

0

This is much too complicated to my taste. Anyway I noted that

 below. & CHAR(10) & Allen-Bradley

should probably be

 below." & CHAR(10) & "Allen-Bradley

Alternatively, you would be better wrting an UDF and calling it with the required parameters: =myFunction(someCell1, somCell2, someCell3)

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Thank you @Patrick Honorez! I have adjusted the inline CHAR(10) reference before like you have said and I still received an error. I am trying again because you and chris neilsen recommended it. In my case, it has worked both ways. – Frosty_Fraz Mar 26 '21 at 17:31