Is there anything wrong with using 'Range.Formula = ' ?

Hi everyone,

To preface this, my VBA knowledge is essentially 0, but otherwise I'm fairly confident with formulas in Excel -- so sorry if this is a bit of a weird question.

I'm looking to automate cleaning some documents where users can easily submit phone numbers and other values in the wrong format. I can think of plenty of ways to do this with formulas like CLEAN, TRIM, SUBSTITUTE etc, but I have no idea how I'd go about doing it in VBA. Normally I'd just fill a formula down, but this cleaning exercise is required multiple times a day, hence why a macro makes sense.

So let's say I just add something like this:

Sub CleanMe()
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("B1:B" & LastRowColumnA).Formula = "=LEFT(A1,3)"
End Sub

Assume that cleaned all the data in A:A. Is there anything wrong with using this method? I'm not really asking for help on writing a text cleaning macro, I'm just asking whether there's any downside to using this method? I only ask because it seems a little strange to me to insert formulas inside a macro when I'm sure there must be some clever ways of doing it with VBA.

Thank you!

Formulas are used all the time in macro code. I see it all the time and most commonly used inside of string variables that are on the right side of equal signs when assigning those variables to cell values. Furthermore, if you want to know everything about the property you are trying to use and whether it makes sense or has any risk associated with it, why not take a look at Microsoft's knowledge base article on it? It is here....

https://docs.microsoft.com/en-us/off....range.formula