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,

 

[JEC]

Dynamic array formula's are not allowed in Tables.

 

[JEC]

Load your State and Country codes into a dictionary in your userform initialize. Use that Dim dic as a global variable.

Dim dic

Private Sub UserForm_Initialize()
 Dim ar, j As Long
 ar = Sheets(2).Cells(1).CurrentRegion
 Set dic = CreateObject("scripting.dictionary")
 
 For j = 1 To UBound(ar)
   dic(ar(j, 1)) = IIf(Len(ar(j, 2)) = 2, Array("", ar(j, 2)), Array(ar(j, 2), ""))
 Next
End Sub

Afterwards, you can just extract the state or country like below. Make sure that you write the data to column E. In case of state code, column E will be empty and F will be filled, just like the formula does.

Sub test()
 a = dic("CHS")
 b = dic("PAP")
' range("E5").Resize(,2) = a   This is how you write it to your sheet
End Sub

 

[Livin404]

Thank you I did try it and I got an 'object define error. I didn't know how the DIM Dic fit (it was the first line on your macro), into the rest of the formula. You also provided a Macro for a test, and again I'm little confused with that. I will continue to explore more in a little while. However, it seems I drifted further away than I was. Nonetheless, I will look into it further.

Private Sub UserForm_Initialize()
  Dim ar, j As Long
 ar = Sheets(2).Cells(1).CurrentRegion
 Set dic = CreateObject("scripting.dictionary")
  For j = 1 To UBound(ar)
   dic(ar(j, 1)) = IIf(Len(ar(j, 2)) = 2, Array("", ar(j, 2)), Array(ar(j, 2), ""))
 Next
End Sub

 

[Alex Blakenburg]

Try replacing this line:

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)))"

With these 2 lines:

    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,v,""""))"
    Range("F2:F" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,"""",v))"

 

[Livin404]

Try replacing this line:

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)))"

With these 2 lines:

    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,v,""""))"
    Range("F2:F" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,"""",v))"

I did try that and nothing seemed to happened. I put a copy of the workbook in a drop box.

Dropbox

Thank you for replying.

 

[JEC]

You have to put the dim dic on top of the module. The test sub is just a demonstration for how to get the data out of the dictionary

 

[Alex Blakenburg]

I did try that and nothing seemed to happened. I put a copy of the workbook in a drop box.

I used your file to come up with it.
And the output after the change is shown below.

Show me the actual code you are using and confirm you have it in the ChangeEvent of the Activity Worksheet module.
Check that the change event is actually running. One way is to delete the formulas in all the rows in columns E & F. The macro should immediately put it back.
Previously the Vlookup generated would have refered to column D, it should now refer to ROUTING ie VLOOKUP([@ROUTING]

20240614 VBA ListObject Formula Livin404.xlsm
	A	B	C	D	E	F
1	Date			ROUTING	COUNTRY	STATE
2	15 Jun 24			DOV		DE
3	13 Jun 24			PAP	HTI
4	31 May 24			DOV		DE
5	31 May 24			PAP	HTI
6	30 May 24			RMS	DEU
7	28 May 24			WRI		NJ
8	28 May 24			WRI		NJ
9	27 May 24			PAP	HTI
10	27 May 24			PAP	HTI
11	27 May 24			PAP	HTI
12	27 May 24			PAP	HTI
ACTIVITY

Cell Formulas
Range	Formula
E2:E12	E2	=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(LEN(v)<>2,v,""))
F2:F12	F2	=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(LEN(v)<>2,"",v))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:N605	Expression	=ODD(ROW())=ROW()	text	NO

 

[Livin404]

Try replacing this line:

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)))"

With these 2 lines:

    Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,v,""""))"
    Range("F2:F" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,"""",v))"

Thanks for getting back, the image you provided is perfect. This is the code I have on Sheet 1 (ACTIVITY). i I will say I'm getting a Run-time error '1004': Application-defined or object-defined error

You help is greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, CellRange As Range
    Dim LastRow As Long
    LastRow = Me.Range("A" & Rows.Count).End(xlUp).Row
    Set CellRange = Me.Range("A2:D" & LastRow & ",F2:N" & LastRow)
    If Not Application.Intersect(CellRange, Target) Is Nothing Then
        With CellRange
            .Font.Bold = False
            .Font.Size = 12
            .Font.Name = "Times New Roman"
        End With
        Dim rngArea As Range
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rngArea In CellRange.Areas
            rngArea = Evaluate("=Upper(" & rngArea.Address & ")")
        Next rngArea
        Columns.AutoFit
        Application.EnableEvents = True
        Application.ScreenUpdating = True
End If
     Application.EnableEvents = False
     Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,v,""""))"
     Range("F2:F" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP([@ROUTING],'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,"""",v))"
        Application.EnableEvents = True
        
        End Sub

 

[Alex Blakenburg]

I will say I'm getting a Run-time error '1004': Application-defined or object-defined error

When you make a statement like this, you need to tell us which line the code errors out on.

The spreadsheet you put on drop box does not have the Table set up as a table. Since you were indicating that you are using a table and the range styling indicated it was a table as some point, the first thing I did was make it into a true table (highlight the area and Ctrl+T).
Using [@ROUTING] will only work if your data is an actual table.

If you use D2 then it will not use Table referencing and work whether its a table or not.

     Range("E2:E" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP(D2,'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,v,""""))"
     Range("F2:F" & Range("D2").End(xlDown).Row).Formula2 = "=LET(v,VLOOKUP(D2,'State Country Code'!$A$2:$B$10388,2,0),IF(len(v)<>2,"""",v))"