Adding a =Mid Formula to VBA string

[Farro]

Hello,

I will start by giving a little context to what I am asking in this thread.

I am pulling multiple reports to one consolidation workbook to merge all of the data.

One of the reports pulls in information and the name is Firstname Lastname while all of the other reports are pulled as lastname, firstname.

What I am attempting to do is to change the name column to lastname, firstname before I merge all of these documents together in my VBA string.

I am thinking of adding it to the portion of VBA code I have when I am prepping the report for the merge. I have highlighted the name change portion that I am thinking of adding in red (which is fitting because it does not work)

'Remove Duplicates from FE Reports Sheet
Worksheets("FE Reports").Activate
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A5:E" & Lastrow).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
'Filter out anything before 2015
'Range("A5:E" & Lastrow).AutoFilter Field:=4, Criteria1:=">09/01/2014"
'Range("A5:E" & Lastrow).AutoFilter Field:=4, Criteria1:="Completed"
Range("A2:E" & Lastrow).Sort key1:=Range("A:A"), Order1:=xlAscending, Key2:=Range("B:B"), Order2:=xlAscending, Header:=xlYes

Range("A2:A").Formula = "=MID(A2&", " & A2,FIND(" ",A2)+1,LEN(A2)+1)"

 

[Veritan]

I suspect the reason that line is failing is because you're telling Excel to put in a formula that contains a circular reference. You are asking it to put a formula in cells A2:A# that references cells A2:A# for information. Instead of using a formula, can you try a quick loop instead? I tested this out and it worked. The InStr function is essentially the same as the FIND formula that you can use in the main Excel window. This way, you set a variable to hold your new name value, and then change the cell to be the value of the variable, rather than trying to reference itself.

Sub NameChange()
    Dim myName As String
    Dim r As Range
    
    For Each r In Range("A2:A" & Lastrow)
        myName = Mid(r & ", " & r, InStr(r, " ") + 1, Len(r) + 1)
        r = myName
    Next r
End Sub

 

[Rick Rothstein]

Range("A2:A#").Formula = "=MID(A2&", " & A2,FIND(" ",A2)+1,LEN(A2)+1)"

I removed the color from your code line so I could highlight the problem with it... you are missing the row number where I show the red # sign. My guess is that line should read...

Range("A2:A" & Lastrow).Formula = "=MID(A2&", " & A2,FIND(" ",A2)+1,LEN(A2)+1)"

 

[Veritan]

I did some additional testing and found that the reason you were getting a syntax error is because VBA needs 2 sets of quote marks in order to include an actual set of quote marks in text or a formula. Putting the formula in Column A will still give you a circular reference, but you could use something like this to put the formula in Column B.

Range("B2:B" & Lastrow).Formula = "=MID(A2&"", ""&A2,FIND("" "",A2)+1,LEN(A2)+1)"

Note that the quote marks in the formula are themselves enclosed in quotes. This is what VBA uses to allow you to put them in a formula or text that you are working with.

 

[Rick Rothstein]

I did some additional testing and found that the reason you were getting a syntax error is because VBA needs 2 sets of quote marks in order to include an actual set of quote marks in text or a formula.

I cannot believe I missed that when I responded originally... I was so focused on the missing row number that I never looked at the rest of the code line.

 

[Farro]

Great! Thank you both for the assistance!

This seems to be functioning properly now!

Thanks again!