-
Feb 25th, 2018, 06:14 AM
#1
Thread Starter
New Member
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 06:30 AM.
Reason: added Code tags
-
Feb 25th, 2018, 06:30 AM
#2
Addicted Member
-
Feb 25th, 2018, 06:31 AM
#3
Re: Runtime error 1004
Welcome to VBForums
Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.
-
Feb 25th, 2018, 03:13 PM
#4
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
-
Feb 26th, 2018, 02:07 AM
#5
Thread Starter
New Member
Re: Runtime error 1004
Everytime it gives an error on:
Range("N" & II & ":" & "R" & II).Select
-
Feb 26th, 2018, 04:25 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|