Consulting

Results 1 to 6 of 6

Thread: Runtime error

  1. #1

    Runtime error

    I will try explain simple as i can.

    Capture.jpg

    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
    Last edited by loop66; 09-30-2018 at 04:16 AM.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,542
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Sorry not working....

    123.xlsm

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,542
    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
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,601
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Perfect.Thanks

Posting Permissions

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