Using LEFT & FIND in a formula in VBA

user_10617334 0
Viewed 120

I currently use a Workbook with 2 worksheets, one containing the original data (VariantMetrics-Filtered) which looks like:

c.1000T>A;c.1000T>A;c.955T>A

and the other containing a formula combining "LEFT" and "FIND" which allows me grabbing just the first part of the data until the first semicolon. The current formula looks like the one below:

=IFERROR(LEFT('VariantMetrics-Filtered'!C2,FIND(";",'VariantMetrics-Filtered'!C2)-1),'VariantMetrics-Filtered'!C2)

I would like to use a VBA macro to make things cleaner since the formula references cells and this is not optimal. I came up with the code below, but I get an error: Expected: end of statement for the ";". I don't understand why since I think that the syntax for "LEFT" is correct.

Sub Remove_duplicates()
'
'

  Dim cell As Range
 
  Set cell = Range("D2:D4000")
    cell.Formula = IFERROR(Left(ActiveCell, Find(";", ActiveCell) - 1),ActiveCell)
    
    
End Sub

Any help would be appreciated

excel-formula vba excel
3 Answers

Your formula should look something like this. It needs to be a string and quotes inside the string need to be doubled as you can see at …Find("";""…

cell.Formula = "=IFERROR(Left(" & ActiveCell.Address & ", Find("";""," & ActiveCell.Address & ") - 1)," & ActiveCell.Address & ")"

The ActiveCell is probably an issue here. I think you should replace it with the first cell of your Range("D2:D4000") Eg C2.

Use cell.Resize(1, 1).Offset(ColumnOffset:=-1).Address(False, False) instead of ActiveCell.Address above.

user_32196130

If you don't care about retaining the original data then you don't need to use a formula and can just have the macro overwrite the original data.

The instr function is similar to the find formula.

Sub Remove_duplicates()

  Dim cell As Range, rng as Range
  Dim str as String
 
  Set rng = Range("D2:D4000")
    
    For each Cell in Rng
      str = Cell.value
      
      If Instr(str,";")>0 Then
        str = Left(str,Instr(str,";")-1)
        Cell.Value = str
      End if

    Next cell
       
End Sub
user_189008430

OK, so the final code that works (I got help from a colleague) is:

Sub Remove_duplicates()

  Dim cell As Range, rng As Range
  Dim str As String
 
  Set rng = Range("Q2:Q4000")
    
For Each cell In rng
      str = cell.Value
      
      If InStr(str, ";") Then
        str = Left(str, InStr(str, ";") - 1)
        cell.Value = str
      End If

Next cell

       
End Sub

I hope it can be useful to someone. And thanks again for the hints!

user_106173340
Related