View Full Version : [SOLVED] Runtime error
loop66
09-30-2018, 02:49 AM
I will try explain simple as i can.
22957
when user writes in column K letter "P-" or "POVRV-" in column AN needs to show word "bla1" or
letter "K-" or "KZD-" or "KMP-" in column AN needs to show word "bla2"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("K7:K1000")
For Each myCell In myRange
If myCell Like "*P-" Or myCell Like "*POVRV-" Then
ActiveCell.Offset(-1, 21).Value = "bla1" <--------Run-time error '-2147417848 (80010108) , Method 'Value' of object 'Range' failed
End If
Next myCell
End Sub
p45cal
09-30-2018, 04:07 AM
This should be a start:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo oops
Application.EnableEvents = False
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("K7:K1000")
For Each myCell In myRange
If myCell Like "*P-" Or myCell Like "*POVRV-" Then myCell.Offset(, 29).Value = "bla1"
oops:
Application.EnableEvents = True
End Sub
loop66
09-30-2018, 04:31 AM
Sorry not working....
22958
p45cal
09-30-2018, 04:50 AM
You were too quick!
I'd been testing the On Error Goto oops and added the deliberate mistake:
Set myRange = Range("K75:K10000000")
it should be:
Set myRange = Range("K75:K1000")
Also you have:
Application.EnableEvents = TrueNext
which should be:
Application.EnableEvents = True
You've got merged cells columns K to S. Ugh. That's why offset 29 was wrong (went to column AV). Safer to use:Cells(myCell.Row, "AN")
So with all corrections:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo oops
Application.EnableEvents = False
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("K75:K1000")
For Each myCell In myRange
If myCell Like "*P-" Or myCell Like "*POVRV-" Then Cells(myCell.Row, "AN").Value = "bla1"
Next
oops:
Application.EnableEvents = True
End Sub
Paul_Hossler
09-30-2018, 03:12 PM
It seems to me like you're doing a lot of unnecessary looping
You can make the event handler just check the cell(s) that changed
Option Explicit
Const cVrijednost As Long = 11
Const cNapomena As Long = 40
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myCell As Range
If Intersect(Target, Columns(cVrijednost)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Columns(cVrijednost)).Cells
With myCell.EntireRow
If Len(.Cells(cVrijednost).Value) > 0 Then
If .Cells(2).Value = "RF" Then
If .Cells(cVrijednost).Value Like "P-" Or .Cells(cVrijednost).Value Like "POVRV-" Then
.Cells(cNapomena).Value = "bla1"
ElseIf .Cells(cVrijednost).Value Like "K-" Or .Cells(cVrijednost).Value Like "KZD-" Or .Cells(cVrijednost).Value Like "KMP-" Then
.Cells(cNapomena).Value = "bla2"
End If
End If
End If
End With
Next
Application.EnableEvents = True
End Sub
loop66
10-01-2018, 09:56 AM
Perfect.Thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.