0

I've run into an issue with my Excel VBA programming. I'm pulling information from a PLC and as I get a "data transfer bit" I'm trying to save off the data and wait for another data transfer bit before saving off data again etc etc. I can easily do this with a button but this needs to be automatic. One problem that I'm running into is that I keep getting a "method range of object _worksheet failed" error whenever I try to automate this process with a cell change command. I figured a second set of eyes couldn't hurt here!

(Sequence of events: Scan two barcodes via PLC. Information is automatically updated since excel file is looking at those two PLC strings. When a transfer data bit goes high (0 to 1 (excel can see this bit go high perfectly fine)), I need to transfer the strings to a certain line and then insert another line.) - The insert and transfer of strings works perfectly fine.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("P2")
    Set Target = Range("P2")
    
If (Range("P3").Value = Range("P4").Value) Then

    Range("b3:k3").Copy Range("b10:k10")
    Range("C10").EntireRow.Insert
    Range("B11").Formula = Range("B11").Value
    Range("C11").Formula = Range("C11").Value
    Range("E11").Formula = Range("E11").Value
    Range("F11").Formula = Range("F11").Value
    Range("G11").Formula = Range("G11").Value
    Range("H11").Formula = Range("H11").Value
    Range("I11").Formula = Range("I11").Value
    Range("J11").Formula = Range("J11").Value
    Range("K11").Formula = Range("K11").Value
    
End If
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Brian Durant
  • 1
  • What line throws the error? – BigBen Feb 25 '22 at 16:48
  • I'm actually not sure how to check that. I'm simply on my excel sheet and I'm changing the value of P2 by changing P4 (1+P4=P2) and eventually it throws a "Run-time error '1004': Method 'Range' of object '_Worksheet' failed. How would I go about checking the line? – Brian Durant Feb 25 '22 at 16:50
  • It then shuts off automatically by the way. I can't debug : – Brian Durant Feb 25 '22 at 16:51
  • 2
    You're causing an infinite loop. You're changing the worksheet programmatically, which fires the Change event in an infinite loop. See the linked thread for how to avoid this. – BigBen Feb 25 '22 at 16:52
  • Ahh Thank you for that. I did wonder if the button acts as a one shot (only one scan) compared to what I'm doing. I'll search through that link you sent to see if there's a way to do a one shot so that it only occurs once per event – Brian Durant Feb 25 '22 at 16:56

0 Answers0