Getting #Spill! code

[Livin404]

I am able to use a macro in a specific sheet with than a module. The macro was created by Mr. Stephen Crump. It works, with the exception I get the error #Spill!. I do know that is unique to 365, and because there is current data in the array that is blocking thus I get spillage.

The following is what Mr. Crump provided for me.

 End If
     Application.EnableEvents = False
    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP($D2,'State Country Code'!$A$2:$B$10388,2,0),TAKE(HSTACK(v,"""",v),,IF(LEN(v)=2,-2,2)))"
    Application.EnableEvents = True
End Sub

The code does work except I get the #Spill! in column E. I tried adding the "@"symbol in front of the look up value range to get my narrowed down to one result, but every time I enter something new I get the #Spill! in Column E if the cell is blank. In the end I do not want to have #Spill in by database.

I had thought about creating yet another Macro to clear out the error code, but the above formula I think will prevent that. I am hope like an "if" option could be used.

Here is my a my file and it only includes the columns/worksheet dealing with the above macro.

Dropbox


Lastly, the worksheet you see is populated with the use of a user form and all new entries are placed at the top.

Thank you,

 

[Livin404]

You're right, the first line was the one getting flagged. I tried with the new code and it is spot on. It is perfect at least with my first several tests.

Thank you so much, and for your patience.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.