+ Reply to Thread
Results 1 to 7 of 7

424 Error

Hybrid View

  1. 02-12-2018, 11:39 AM #1
    rpjwhite
    rpjwhite is offline
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Question 424 Error

    All

    So I am using a lookup table to calculate a date and Iam getting stuck on a 424 error. I am pretty sure I dim'd the hold values at the start no dice. I have tried to remove the holding numbers but again no play...

    Sub AddDataShort(LA As String, BisName As String, BisID As String, DOI As Date, Scope As String, FHS As Variant, FHSNA As Variant, CCP As Variant, CCPNA As Variant, Structural As Variant, StructuralNA As Variant, Label As Variant, LabelNA As Variant, COMPOSITION As Variant, CompositionNA As Variant, FSMS As Variant, CIM As Variant, OptionGroup1 As Boolean, OptionGroup2 As Boolean, OptionGroup3 As Boolean, Comment As Variant, IntTime As Date, AdTime As Date)
Sheets("Data").Activate

Dim NextColumn As Integer
Dim AvgValue As Integer
Dim NextInt As Double



NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

'Text Entry
 NextColumn = 1
 Cells(NextRow, NextColumn) = LA
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = BisName
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = BisID
 NextColumn = NextColumn + 1
 With Cells(NextRow, NextColumn)
    .Value = DOI
    .NumberFormat = "dd/mm/yy"
 End With
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = Scope
 NextColumn = NextColumn + 1
 
 'Business Group
 If OptionGroup1 Then Cells(NextRow, NextColumn) = 1
 If OptionGroup2 Then Cells(NextRow, NextColumn) = 2
 If OptionGroup3 Then Cells(NextRow, NextColumn) = 3
 NextColumn = NextColumn + 1

'Food Hygiene and Systems
 If FHS = ThisWorkbook.Sheets("EM").Range("A2").Value Then Cells(NextRow, NextColumn) = 5
 If FHS = ThisWorkbook.Sheets("EM").Range("A3").Value Then Cells(NextRow, NextColumn) = 4
 If FHS = ThisWorkbook.Sheets("EM").Range("A4").Value Then Cells(NextRow, NextColumn) = 3
 If FHS = ThisWorkbook.Sheets("EM").Range("A5").Value Then Cells(NextRow, NextColumn) = 2
 If FHS = ThisWorkbook.Sheets("EM").Range("A6").Value Then Cells(NextRow, NextColumn) = 1
 If FHS = ThisWorkbook.Sheets("EM").Range("A7").Value Then Cells(NextRow, NextColumn) = 0
 NextColumn = NextColumn + 1

'Cross Contamination
If CCP = ThisWorkbook.Sheets("EM").Range("B2").Value Then Cells(NextRow, NextColumn) = 5
If CCP = ThisWorkbook.Sheets("EM").Range("B3").Value Then Cells(NextRow, NextColumn) = 4
If CCP = ThisWorkbook.Sheets("EM").Range("B4").Value Then Cells(NextRow, NextColumn) = 3
If CCP = ThisWorkbook.Sheets("EM").Range("B5").Value Then Cells(NextRow, NextColumn) = 2
If CCP = ThisWorkbook.Sheets("EM").Range("B6").Value Then Cells(NextRow, NextColumn) = 1
If CCP = ThisWorkbook.Sheets("EM").Range("B7").Value Then Cells(NextRow, NextColumn) = 0
NextColumn = NextColumn + 1

'Structural Performance
If Structural = ThisWorkbook.Sheets("EM").Range("C2").Value Then Cells(NextRow, NextColumn) = 5
If Structural = ThisWorkbook.Sheets("EM").Range("C3").Value Then Cells(NextRow, NextColumn) = 4
If Structural = ThisWorkbook.Sheets("EM").Range("C4").Value Then Cells(NextRow, NextColumn) = 3
If Structural = ThisWorkbook.Sheets("EM").Range("C5").Value Then Cells(NextRow, NextColumn) = 2
If Structural = ThisWorkbook.Sheets("EM").Range("C6").Value Then Cells(NextRow, NextColumn) = 1
If Structural = ThisWorkbook.Sheets("EM").Range("C7").Value Then Cells(NextRow, NextColumn) = 0
NextColumn = NextColumn + 1

'Labelling Performance
If Label = ThisWorkbook.Sheets("EM").Range("D2").Value Then Cells(NextRow, NextColumn) = 5
If Label = ThisWorkbook.Sheets("EM").Range("D3").Value Then Cells(NextRow, NextColumn) = 4
If Label = ThisWorkbook.Sheets("EM").Range("D4").Value Then Cells(NextRow, NextColumn) = 3
If Label = ThisWorkbook.Sheets("EM").Range("D5").Value Then Cells(NextRow, NextColumn) = 2
If Label = ThisWorkbook.Sheets("EM").Range("D6").Value Then Cells(NextRow, NextColumn) = 1
If Label = ThisWorkbook.Sheets("EM").Range("D7").Value Then Cells(NextRow, NextColumn) = 0
NextColumn = NextColumn + 1

'Composition Preformance
If COMPOSITION = ThisWorkbook.Sheets("EM").Range("E2").Value Then Cells(NextRow, NextColumn) = 5
If COMPOSITION = ThisWorkbook.Sheets("EM").Range("E3").Value Then Cells(NextRow, NextColumn) = 4
If COMPOSITION = ThisWorkbook.Sheets("EM").Range("E4").Value Then Cells(NextRow, NextColumn) = 3
If COMPOSITION = ThisWorkbook.Sheets("EM").Range("E5").Value Then Cells(NextRow, NextColumn) = 2
If COMPOSITION = ThisWorkbook.Sheets("EM").Range("E6").Value Then Cells(NextRow, NextColumn) = 1
If COMPOSITION = ThisWorkbook.Sheets("EM").Range("E7").Value Then Cells(NextRow, NextColumn) = 0
NextColumn = NextColumn + 1

'Food Sayety Management System
If FSMS = ThisWorkbook.Sheets("EM").Range("F2").Value Then Cells(NextRow, NextColumn) = 5
If FSMS = ThisWorkbook.Sheets("EM").Range("F3").Value Then Cells(NextRow, NextColumn) = 4
If FSMS = ThisWorkbook.Sheets("EM").Range("F4").Value Then Cells(NextRow, NextColumn) = 3
If FSMS = ThisWorkbook.Sheets("EM").Range("F5").Value Then Cells(NextRow, NextColumn) = 2
If FSMS = ThisWorkbook.Sheets("EM").Range("F6").Value Then Cells(NextRow, NextColumn) = 1
NextColumn = NextColumn + 1

'Confidence in Management
If CIM = ThisWorkbook.Sheets("EM").Range("G2").Value Then Cells(NextRow, NextColumn) = 5
If CIM = ThisWorkbook.Sheets("EM").Range("G3").Value Then Cells(NextRow, NextColumn) = 4
If CIM = ThisWorkbook.Sheets("EM").Range("G4").Value Then Cells(NextRow, NextColumn) = 3
If CIM = ThisWorkbook.Sheets("EM").Range("G5").Value Then Cells(NextRow, NextColumn) = 2
If CIM = ThisWorkbook.Sheets("EM").Range("G6").Value Then Cells(NextRow, NextColumn) = 1
NextColumn = NextColumn + 1

Cells(NextRow, NextColumn) = Comment
NextColumn = NextColumn + 1
Cells(NextRow, NextColumn) = FHSNA
NextColumn = NextColumn + 1
Cells(NextRow, NextColumn) = CCPNA
NextColumn = NextColumn + 1
Cells(NextRow, NextColumn) = StructuralNA
NextColumn = NextColumn + 1
Cells(NextRow, NextColumn) = LabelNA
NextColumn = NextColumn + 1
Cells(NextRow, NextColumn) = CompositionNA
NextColumn = NextColumn + 1
 With Cells(NextRow, NextColumn)
    .Value = IntTime
    .NumberFormat = "hh:mm"
 End With

NextColumn = NextColumn + 1
 With Cells(NextRow, NextColumn)
    .Value = AdTime
    .NumberFormat = "hh:mm"
 End With

NextColumn = NextColumn + 1
With Cells(NextRow, NextColumn)
    .Value = AdTime + IntTime
    .NumberFormat = "hh:mm"
End With

NextColumn = NextColumn + 1
AvgValue = Round(Application.WorksheetFunction.AverageIf(Range(Cells(NextRow, 7), Cells(NextRow, 13)), ">0"), 0)
NextInt = Application.WorksheetFunction.Index(EM.Range("A13:D18"), Application.WorksheetFunction.Match(AvgValue, EM.Range("A13:A18"), 0), Application.WorksheetFunction.Match(Cells(NextRow, 6), EM.Range("A13:D13"), 0)) '<-Error happens in this Row
With Cells(NextRow, NextColumn)
    .Value = NextInt + DOI
    .NumberFormat = "dd/mm/yy"
End With

End Sub
    Reply With QuoteRegister To Reply
  2. 02-12-2018, 11:42 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,379

    Re: 424 Error

    A quick Google suggests you should check for misspellings.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 02-12-2018, 12:07 PM #3
    rpjwhite
    rpjwhite is offline
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Re: 424 Error

    I will give that a shot spelling is not my strong point.
    Reply With QuoteRegister To Reply
  4. 02-12-2018, 11:43 AM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,704

    Re: 424 Error

    Also, if you click on debug when you get the error, Excel should highlight the line of code where the error is occurring.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  5. 02-12-2018, 12:08 PM #5
    rpjwhite
    rpjwhite is offline
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Re: 424 Error

    @alan I did that. I have commented the dodgy line.
    Reply With QuoteRegister To Reply
  6. 02-12-2018, 12:23 PM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: 424 Error

    If this is the problem line you should split out the Application.Match parts to make things easier to debug.
    NextInt = Application.WorksheetFunction.Index(EM.Range("A13:D18"), Application.WorksheetFunction.Match(AvgValue, EM.Range("A13:A18"), 0), Application.WorksheetFunction.Match(Cells(NextRow, 6), EM.Range("A13:D13"), 0)) '<-Error happens in this Row
    For example.
    Dim ResCol As Variant
Dim ResRow As Variant

' other code

    ResCol = Application.Match(Cells(NextRow, 6), EM.Range("A13:D13"), 0)

    ResRow = Application.Match(AvgValue, EM.Range("A13:A18"), 0)

    If Not IsError(ResCol) And Not IsError(ResRow) Then
        NextInt = Application.WorksheetFunction.Index(EM.Range("A13:D18"), ResRow, ResCol)
    Else
        Msgbox "Problem finding match(es)."
    End If
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  7. 02-12-2018, 12:56 PM #7
    rpjwhite
    rpjwhite is offline
    Registered User
    Join Date
    10-11-2016
    Location
    Aberdeen, Scotland
    MS-Off Ver
    2010
    Posts
    49

    Re: 424 Error

    @Norie yeah that seemed to work. Now trying to work out why it is erroring...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA ERROR: run time error 1004: Application-defined or Object-defined error in excel 2013
    By AnanthKrishna in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2015, 06:16 AM
  2. [SOLVED] Run-tim error -2147467259(80004005): Automation error Unspecidied error
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2014, 12:12 PM
  3. Excel macro (compile error. syntax error.) error
    By salar_younis in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-06-2014, 06:11 AM
  4. Receiving following error “Complie error : syntax error” Help
    By masond3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-02-2012, 10:19 AM
  5. Error "run-time Error '1004': General Odbc Error
    By D4WNO77 in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  6. Error 75 File/Path access error, sometimes Error 1004
    By smokebreak in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-16-2011, 02:35 PM
  7. Error Handling - On Error GoTo doesn't trap error successfully
    By David in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-16-2006, 02:10 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1