Results 1 to 4 of 4

Runtime error 91 in VBA - Objects set

Threaded View

  1. #1
    Registered User
    Join Date
    11-27-2023
    Location
    Vienna
    MS-Off Ver
    Office 365
    Posts
    12

    Runtime error 91 in VBA - Objects set

    Hello guys,

    I have a problem within the attached file with 1 line here below:

       
    
     Private Sub CommandButton2_Click()
    
    'Integers of weight calculator
    Dim compareValue As Double
    Dim roundUpValue As Double
    Dim searchRange As Range
    Dim resultCell As Range
    Dim Ceiling As Double
    
    'Integers of cost findig tool
    Dim HomeSheet As Worksheet
    Dim gltSheet As Worksheet
    Dim HomeRange As Range
    Dim GLTRange As Range
    Dim resultRange As Range
    Dim r&, c&
    
    Set HomeSheet = ThisWorkbook.Sheets("Home")
    Set gltSheet = ThisWorkbook.Sheets("GLT")
    Set HomeRange = HomeSheet.Range("I12")
    Set GLTRange = gltSheet.Range("A4:A254")
    Set resultRange = HomeSheet.Range("I19")
    
    
    '1) Calculation of the chargeable weight based on loading meter
    
    compareValue = Application.WorksheetFunction.Max(Range("I11"), Range("I12"))
        ' Finds the higher number of weight and loading meter
        
    roundUpValue = WorksheetFunction.Ceiling(compareValue, 100)
        ' Rounds up the higher value to the next whole hundred
        
    Range("Y1") = WorksheetFunction.Ceiling(compareValue, 100)
        ' Gives the the rounded up number to control cell
        
    Set searchRange = Sheets("GLT").Range("A4:A254")
        ' Defines the range, where to find the rounded up value
        
    Set resultCell = searchRange.Find(roundUpValue)
    
    
    'If Not resultCell Is Nothing Then
        ' Do something with the found value
        ' For example, display it in a message box
         'MsgBox resultCell.Value
    'Else
        ' Value not found in the specified range
        ' Do something else
         'MsgBox "HIBA"
         'Exit Sub
    'End If
    
    
    Set gltSheet = ThisWorkbook.Sheets("GLT")
    
    '2) Finds the rate based on calculated chargeable weight
    
        'Cross the cells of A4:A254 and B3:CT03 for the matching result
    
    
        r = GLTRange.Find(HomeRange, , , 1).Row
        c = gltSheet.Range("A3:CT254").Find(resultRange, , , 1).Column
        
        
        'Value written in cell
        HomeSheet.Range("I28    ").Value = gltSheet.Cells(r, c).Value
        HomeSheet.Range("H53    ").Value = gltSheet.Cells(r, c).Value
        HomeSheet.Range("H28    ").Value = "GLT"
        HomeSheet.Range("D53    ").Value = "GLT"
        
        'Pop-up Box of result with diesel surcharge
        MsgBox " Freight charges are " & HomeSheet.Range("I28") & " ,-€ with trucker " & HomeSheet.Range("H28")
    
    
    End Sub
    The error happens every second day, meaning, sometimes run correctly but mostly not. I can't release my code so.

    The debugger says, r & c are not set in row:

        r = GLTRange.Find(HomeRange, , , 1).Row
        c = gltSheet.Range("A3:CT254").Find(resultRange, , , 1).Column
    The task would be to find the matching price level on GLT tab, based on cargo details entered by users.
    Is there another workaround to find column/row matching value?

    Thanks for checking!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Runtime Error 1004 + Runtime 5
    By jrean042 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-07-2020, 08:16 AM
  2. [SOLVED] Runtime Error 1004 and Runtime Error 13
    By Brendan_MS in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-21-2020, 10:52 AM
  3. Replies: 25
    Last Post: 05-10-2019, 03:33 PM
  4. Runtime Error 9 with List Objects
    By kelseygueldalewis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2018, 02:11 PM
  5. Runtime error 1004 & runtime error 424
    By MrWhiskey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-29-2017, 08:57 AM
  6. Assigning properties to objects at Runtime
    By bizmita in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-06-2015, 06:23 PM
  7. [SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed
    By jl22stac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 07:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1