title: Invalid procedure call or argument (Error 5) but code executes properly
body: <p>I'm writing a macro that will basically copy and paste data from one sheet to another. The code executes and runs fine up until the last bit where I need to remove the last three characters from each cell in a specified column. </p>
  
  <p>The code does this just fine, but I always get an error afterwards </p>
  
  <blockquote>
    <p>Invalid procedure call or argument (Error 5)</p>
  </blockquote>
  
  <p>This is the piece where Excel identifies the error even though it does its job by removing the last three characters of the cell value. Before the cell values are changed they look like 000123.HK </p>
  
  <p>My code is supposed to remove the .HK at the end</p>
  
  <p>Would appreciate any help on this, also happy to have tips on how to make my code more efficient!</p>
  
  <pre><code>With Worksheets("Earnings Final")
      For i = 1 To lastRow
          .Cells(i, "B") = Left(.Cells(i, "B").Value, Len(.Cells(i, "B").Value) - 3)
      Next i
  End With
  </code></pre>
  
  <p>Full code below:</p>
  
  <pre><code>
  Private Sub button_Click()
  
  Dim ricRange As Range
  Dim ricEveryNth As Range
  Dim ricRow As Long
  
  Dim sRange As Range
  Dim sEveryNth As Range
  Dim sRow As Long
  
  Application.ScreenUpdating = False
  
  Sheets("SheetA").Activate
  
  With Worksheets("SheetA")
      Set ricRange = .Range("A2", Cells(.Rows.Count, "A").End(xlUp))
  End With
  
  Worksheets("SheetA").Range("A1").Cut
  Worksheets("SheetA").Range("B1").Select
  ActiveSheet.Paste
  
  For ricRow = 1 To ricRange.Rows.Count Step 3
  If ricRow = 1 Then
      Set ricEveryNth = ricRange(ricRow, 1)
  Else
      Set ricEveryNth = Union(ricRange(ricRow, 1), ricEveryNth)
  End If
  Next ricRow
  Application.Goto ricEveryNth
  
  Selection.Copy
  Sheets("SheetB").Activate
  Worksheets("SheetB").Range("B1").PasteSpecial Paste:=xlPasteFormulas
  
  With Worksheets("SheetA")
      Set sRange = .Range("B1", Cells(.Rows.Count, "B").End(xlUp))
  End With
  
  For sRow = 1 To sRange.Rows.Count Step 3
  If sRow = 1 Then
      Set sEveryNth = sRange(sRow, 1)
  Else
      Set sEveryNth = Union(sRange(sRow, 1), sEveryNth)
  End If
  Next sRow
  Application.Goto sEveryNth
  
  Selection.Copy
  Sheets("SheetB").Activate
  Worksheets("SheetB").Range("A1").PasteSpecial Paste:=xlPasteFormulas
  
  ' Remove .HK from stock codes
  
  Dim lastRow As Long
  Dim i As Integer
  
  
  Sheets("SheetB").Activate
  lastRow = Cells(Rows.Count, "B").End(xlUp).Row
  
  With Worksheets("SheetB")
      For i = 1 To lastRow
          .Cells(i, "B") = Left(.Cells(i, "B").Value, Len(.Cells(i, "B").Value) - 3)
      Next i
  End With
  
  End Sub
  
  </code></pre>