CODEWITHSUNDEEP
vbaexcel
user4581436
user4581436

Reputation: 57

Remove the last two Characters in a cell

I need to remove the last the last two characters of all cells found in a worksheet named Target with the column name Order (column BD).

This macro below would have looked in row 1 Worksheet Target for the word orders. It then would have removed the last two characters (strings).

However since I am new and got these macros from two different sources I likely messed up in assigning variables.

Sub RemoveOrdersTT()
  Dim ws As Worksheet
  Dim rng As Range
  Dim lastCol As Long
  Dim i As Long

  Set ws = Worksheets("Target")
  With ws
     lastCol = .UsedRange.Columns.Count
      For i = 1 To lastCol
       If InStr(1, UCase(.Cells(1, i).Value), "Orders") > 0 Then
         .Cells(1, i).Value = Left(.Cells(1, i).Value, Len(.Cells(1, i).Value) - 2)
        End If
      Next i
  End With
End Sub

A code that would look at worksheet Target and column BD starting at row 2 or a fix to my code would be much appreciated.

Upvotes: 0

Views: 1559

Answers (1)

FreeMan
FreeMan

Reputation: 5687

Change the inner if:

If InStr(1, UCase(.Cells(1, i).Value), "ORDER") > 0 Then
  .cells(1,i).value = left(.cells(1,i).value, Len(.cells(1,i).value) -2)
End If
  1. InStr requires 3 parameters (plus some optional ones), you were missing the first parm telling it where to start looking
  2. In the If statement, InStr will return a position or 0, so testing for > 0 is sufficient, no need for <>, though that will work, it's just unnecessary
  3. Remove the 2nd call to UCase() in the if statement itself, no need to UCase a fixed string, just provide it in upper case.
  4. No need to mess with trying to create another range object and modifying that, you've already got the cell you need.

Also:

Assuming your data is rectangular (i.e. you don't have any columns without headers that you don't want to have searched):

LastCol = .UsedRange.Columns.Count

Is a much easier way of finding out how many columns are in use, and doesn't require moving the active cell about the worksheet.

Complete Code

Sub RemoveOrdersTT()
  Dim ws As Worksheet
  Dim rng As Range
  Dim lastCol As Long
  Dim i As Long
  Dim r as long

  Set ws = Worksheets("Target")
  With ws
     lastCol = .UsedRange.Columns.Count
      For i = 1 To lastCol
       If InStr(1, UCase(.Cells(1, i).Value), "Orders") > 0 Then
         for r = 2 to .usedRange.Rows.Count
           .Cells(r, i).Value = Left(.Cells(r, i).Value, Len(.Cells(r,i).Value) - 2)
         next
        End If
      Next i
  End With
End Sub

Had to add the inner loop For r... to actually get it to traverse the rows in that column

Upvotes: 1

Related Questions