Run time error (I believe) is causing Excel to crash

kingconsto

New Member
Joined
Apr 19, 2017
Messages
31
There seems to be an issue with .SetRange .Range("A3:DG17") I have taken out the dot in front of range. So it now reads .SetRange Range("A3:DG17") . After the change the code compiles just fine.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

kingconsto

New Member
Joined
Apr 19, 2017
Messages
31
.SetRange .Parent.Range("A3:DG" & x) is giving a run-time error '1004': Application-defined or object defined error.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,688
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
.SetRange .Parent.Range("A3:DG" & x) is giving a run-time error '1004': Application-defined or object defined error.
That's odd. Works fine for me.
 

kingconsto

New Member
Joined
Apr 19, 2017
Messages
31
Can I please have the marco code if I wanted to execute the benelux.mac?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,482
Thanks RoryA, corrected (for OP benefit), code is:
Code:
Sub Treats_v1()

    Dim arr()   As Variant
    Dim strFile As Variant
    Dim str     As String
    Dim x       As Long
    Dim y       As Long
    
    str = InputBox("Please enter staff ID:")
    str = Replace("C:\Users@1\AppData\Roaming\IBM\Personal Communications\benelux.mac", "@1", str)
    
    Application.ScreenUpdating = False

    arr = Sheets("Data").Range("AK9:EQ33").Value
    
    With Sheets("Treats")
        .Range("A3").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        Erase arr
        x = .Cells(.Rows.Count, 1).End(xlUp).row
        .Sort.SortFields.Clear
        .Sort.SortFields.add key:=.Range("A3"), SortOn:=xlSortOnValues, Order:=xlAscending
        With .Sort
            .SetRange .Parent.Range("A3:DG" & x)
            .header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        On Error Resume Next
        x = .Cells(1, 1).Resize(x).find(what:="N", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True).row
        On Error GoTo 0

        If .Cells(x, 1).Value <> "N" Then
            MsgBox "Cannot find N value in column A, please check and try again", vbExclamation, "Missing Value"
            Application.ScreenUpdating = True
            Exit Sub
        End If
        Erase arr
        arr = .Cells(3, 1).Resize(x - 2, 111).Value
    End With
           
    With CreateObject("Scripting.FileSystemObject")
        .createtextfile str
        Set strFile = .opentextfile(str, ForWriting)
        strFile.writeline "Description ="
        For x = LBound(arr, 1) To UBound(arr, 1)
            For y = LBound(arr, 2) To UBound(arr, 2)
                s.writeline arr(x, y)
            Next y
        Next x
        Set strFile = Nothing
    End With
   
    Application.ScreenUpdating = True
   
    MsgBox "BENELUX MACRO CREATED", vbOKOnly
            
End Sub
 
Last edited:

kingconsto

New Member
Joined
Apr 19, 2017
Messages
31
There is only one problem with the solution...the marco does not update when the relevant fields are overwritten. I change the data before each run and the marco wont update with the new data.
 

kingconsto

New Member
Joined
Apr 19, 2017
Messages
31
All the fields. The macro should compile data from Sheet "Data" and copy it to sheet "Treats." That information in "Treats" should now be the inputs for benelux.mac and update it accordingly. When I run benelux.mac it should be the same values as what in "Treats" which is the same values as what's in "Data." Currently only the sheets "Data" and "Treats" is being updated.
 

kingconsto

New Member
Joined
Apr 19, 2017
Messages
31
I believe it has something to with the code below. I do not think that it is overwriting the .mac file as planned.

Code:
    With CreateObject("Scripting.FileSystemObject")
        .createtextfile str
        Set strFile = .opentextfile(str, ForWriting)
        strFile.writeline "Description ="
        For x = LBound(arr, 1) To UBound(arr, 1)
            For y = LBound(arr, 2) To UBound(arr, 2)
                s.writeline arr(x, y)
            Next y
        Next x
    End With
 

Forum statistics

Threads
1,089,546
Messages
5,408,876
Members
403,238
Latest member
George_Ong

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top