dcsimg
Results 1 to 6 of 6
  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    2

    Runtime error 1004

    Hi! I'm fairly new to VBA and trying to teach myself some things in order to make some work of mine easier and less timeconsuming. therefore i have this code:
    I would appriciate your help

    Code:
    Sub KleurenNIEUW()
    
    Dim I As Integer, j As Integer, intStart As Integer
    Dim Selectie As Range, vergelijk As Range
    Dim spaties As String: spaties = " "
    
    For Each Selectie In Selection.Columns
        Set vergelijk = Selectie.Offset(-1, 0)
        On Error GoTo Error
        SelectieA = Split(Selectie.Text, spaties)
        VergelijkB = Split(vergelijk.Text, spaties)
        For j = LBound(SelectieA) To UBound(SelectieA)
            For I = LBound(VergelijkB) To UBound(VergelijkB)
                If UCase(SelectieA(j)) = UCase(VergelijkB(I)) Then
                    intStart = InStr(1, spaties + UCase(Selectie.Value) + spaties, spaties + UCase(VergelijkB(I)) + spaties)
                    While intStart > 0
                        Selectie.Characters(Start:=intStart, Length:=Len(VergelijkB(I))).Font.ColorIndex = 0
                        intStart = InStr(intStart + 1, spaties + UCase(Selectie.Value) + spaties, spaties + UCase(VergelijkB(I)) + spaties)
    
                    Wend
                End If
            Next I
        Next j
    Next
    Exit Sub
    Error:
    MsgBox "Please do not select multiple rows"
    End Sub
    Now i do want to make it even more useful for other collegues but i cant get it working. but i do have problems with it, a runtime 1004 error and cant figure it out.

    Code:
    Sub NIEUW()
    
    Application.EnableEvents = False
    
    Dim II As Integer
    Dim I As Integer, j As Integer, intStart As Integer
    Dim Selectie As Range, vergelijk As Range
    Dim spaties As String: spaties = " "
    Aantal = Application.WorksheetFunction.CountA(Range("A:A"))
    
    Sheets("Worksheet").Select
    
    For II = 6 To Aantal + 5
    If Range("A" & II) = "OUD" Then GoTo Volgende
    
        Range("N" & II & ":" & "R" & II).Select
    For Each Selectie In Selection.Columns
        Set vergelijk = Selectie.Offset(-1, 0)
        SelectieA = Split(Selectie.Text, spaties)
        VergelijkB = Split(vergelijk.Text, spaties)
        For j = LBound(SelectieA) To UBound(SelectieA)
            For I = LBound(VergelijkB) To UBound(VergelijkB)
                If UCase(SelectieA(j)) = UCase(VergelijkB(I)) Then
                    intStart = InStr(1, spaties + UCase(Selectie.Value) + spaties, spaties + UCase(VergelijkB(I)) + spaties)
                    While intStart > 0
                        Selectie.Characters(Start:=intStart, Length:=Len(VergelijkB(I))).Font.ColorIndex = 0
                        intStart = InStr(intStart + 1, spaties + UCase(Selectie.Value) + spaties, spaties + UCase(VergelijkB(I)) + spaties)
    
                    Wend
                End If
            Next I
        Next j
    Next
    
    
    Volgende:
    Next II
        Sheets("Worksheet").Select
        
    Application.EnableEvents = True
    Last edited by si_the_geek; Feb 25th, 2018 at 05:30 AM. Reason: added Code tags

  2. #2
    Lively Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    120

    Re: Runtime error 1004


  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,863

    Re: Runtime error 1004

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,554

    Re: Runtime error 1004

    on which line does the error 1004 occur?
    does it error every time that line is called?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    2

    Re: Runtime error 1004

    Everytime it gives an error on:
    Range("N" & II & ":" & "R" & II).Select

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,554

    Re: Runtime error 1004

    what is the value of aantal and in turn II when the error occurs?

    post a sample workbook where the error occurs?
    zip first
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.