PDA

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