-
1 Attachment(s)
Runtime error
I will try explain simple as i can.
Attachment 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"
Code:
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
-
This should be a start:
Code:
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
-
1 Attachment(s)
Sorry not working....
Attachment 22958
-
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:
Code:
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
-
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
Code:
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
-