Using LEFT & FIND in a formula in VBA-CodePudding

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

CodePudding user response：

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.