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 Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: 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 Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: 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 Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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
  •