-1

I need VBA code which

  • cuts part of a string, up to the word "HS", in each cell of column A
  • paste that part into column "N" in the previous row.

Some of the strings have nothing before "HS", so the code should not run for these ones. The sheet I'm working on has thousands of rows. I'm unsure how to do this...

Community
  • 1
  • 1
Souza Saulo
  • 61
  • 6
  • What have you tried so far and where are you stuck? People here are not going to write your code for you, SO is **not a free coding service**. Please update your question with your code so we can help out with a specific problem. Also read [How do I ask as good question](https://stackoverflow.com/help/how-to-ask). – Rik Sportel Jul 04 '17 at 05:05
  • I'll make sure it's that way next time. Thank you. – Souza Saulo Jul 05 '17 at 00:47

1 Answers1

0

Here is some commented code which achieves what you want. Note you may find this answer useful for finding the last row.

This code avoids actually using cut and paste because that is slow. Instead, it directly accesses the cells' values.

Sub CopyBeforeHS()
    Dim r As Long, HSindex As Long
    ' There are many ways to find the last row, or just replace 100 manually here
    ' You must start at row 2, as r-1 is used in the loop
    For r = 2 To 100
        ' Your sheet name here...
        With ThisWorkbook.Sheets("Sheet1")
            ' Get location of "HS" in each cell in column A
            HSindex = InStr(.Range("A" & r).Value, "HS")
            ' If HS was found AND it wasn't the first characters
            If HSindex > 1 Then
                ' Copy cell text from before "HS" to previous row, column N
                .Range("N" & r - 1).Value = Left(.Range("A" & r).Value, HSindex - 1)
                ' Remove the same text from column A
                .Range("A" & r).Value = Mid(.Range("A" & r).Value, HSindex)
            End If
        End With
    Next r
End Sub
Wolfie
  • 27,562
  • 7
  • 28
  • 55