Macro does not run on one computer, runs on others. Other macros run on the problem computer

Question

I've verified that all computers are using Office 365 and are fully up to date.

There is a test grading macro that calls three subroutines. First, the the data to be graded is pasted along with the answers. A test is generated that copies over the categories but not answers--this works on the problem computer.

To start the grading process, comparison columns are called up (XCheckColumnData). This sub also works on the problem computer. The next step is to bring up more columns with formulas that compare the columns, and this is where it fails on one computer; that is XGetScore. When it is attempted it gives the following message: "Run-time error '1004': Application-defined or object-defined error" and highlights this part: ActiveCell.FormulaR1C1 = _ "=[@[" & BoolArgument1(I) & "]]=[@[" & BoolArgument2(I) & "]]"

Here is the XCheckColumnData subroutine that precedes the failed one:

Sub XCheckColumnData()

    Call XDeleteChecks

Dim ranges, columnheader, newcolumnheaders, columnletters, lookupcolumns As Variant
ranges = Array("I:I", "K:K", "M:M", "AE:AE", "AG:AG")
columnheaders = Array("[Column1]", "[Column1]", "[Column1]", "[3 in 1]", "[Column1]")
newcolumnheaders = Array("CPU Check", "FF Check", "OEM Check", "2in1 Check", "Chrome Check")
columnletters = Array("I2", "K2", "M2", "AE2", "AG2")
lookupcolumns = Array("Processor Number", "Form Factor Category", "OEM Brand", "2 in 1", "Chromebook")

Sheets("Platinum").Select

For I = 0 To 4
J = 0

    Columns(ranges(I)).Select
    Selection.Insert Shift:=xlToRight
    Range("Table1[[#Headers]," & columnheaders(I) & "]").FormulaR1C1 = newcolumnheaders(I)
    Range("Table1[" & newcolumnheaders(I) & "]").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Range(columnletters(I)).Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Table2,MATCH([@[Product Identifier]],Table2[Part Number],0),MATCH(""" & lookupcolumns(I) & """,Table2[#Headers],0))"
    
Next I
End Sub

And here is XGetScore which gives the error:

Sub XGetScore()
Dim BoolColumn, BoolName, BoolArgument1, BoolArgument2 As Variant
BoolColumn = Array("AS", "AT", "AU", "AV", "AW", "AX")
BoolName = Array("CPU Bool", "FF Bool", "OEM Bool", "2in1 Bool", "Chrome Bool", "Row Error")
BoolArgument1 = Array("Processor Number", "Form Factor Category", "OEM Brand", "2 in 1", "Chromebook")
BoolArgument2 = Array("CPU Check", "FF Check", "OEM Check", "2in1 Check", "Chrome Check")
I = 0

For I = 0 To 5
Range(BoolColumn(I) & "1").Select
ActiveCell.FormulaR1C1 = BoolName(I)
Range(BoolColumn(I) & "2").Select

If I < 5 Then
ActiveCell.FormulaR1C1 = _
    "=[@[" & BoolArgument1(I) & "]]=[@[" & BoolArgument2(I) & "]]"
ElseIf I = 5 Then
Range("AX2").Formula = _
    "=AND(AS2:AW2)"
End If

Next I


End Sub

Is the data already formatted as a table when you attempt to enter this formula? — BigBen, Jun 15 '23 at 20:24
Side note: recommended reading would be [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), which also applies to avoiding `ActiveCell`. — BigBen, Jun 15 '23 at 20:25
Debug.print the value of `"=[@[" & BoolArgument1(I) & "]]=[@[" & BoolArgument2(I) & "]]"` then try pasting that into the target cell - do you get an error? — Tim Williams, Jun 15 '23 at 20:41
Check to confirm that all of your References are the same on each PC. — Frank Ball, Jun 15 '23 at 21:11

Jean Allard · Answer 1 · 2023-06-15T22:45:45.963

-2

If you use Select you need to add the reference to the sheet. I'm really surprise that your code works correctly on one computer. Without sheet reference you will have an error message 1004.

Try in a new sub to write only

Sub test()
     Range("B2").Select
End Sub

and run your code. You will have the message "Run time error '1004'"
If you try with :

Sub test()
     Sheets(1).Range("B2").Select
End Sub

You won't have the error message.

See on this site How to avoid using Select in Excel VBA This text was written 11 years ago.

edited Jun 15 '23 at 22:45

answered Jun 15 '23 at 22:39

Jean Allard

1
2

1

"Without sheet reference you will have an error message 1004" - this is not true: it will select B2 in the Activesheet (unless in a worksheet code module, where it will select on that sheet, provide it's the active sheet) – Tim Williams Jun 16 '23 at 06:12

Macro does not run on one computer, runs on others. Other macros run on the problem computer

1 Answers1