I currently use a Workbook with 2 worksheets, one containing the original data (VariantMetrics-Filtered) which looks like:
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:
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
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
cell.Formula = "=IFERROR(Left(" & ActiveCell.Address & ", Find("";""," & ActiveCell.Address & ") - 1)," & ActiveCell.Address & ")"
ActiveCell is probably an issue here. I think you should replace it with the first cell of your
cell.Resize(1, 1).Offset(ColumnOffset:=-1).Address(False, False) instead of