VBA Macro to type a formula in a cell throws Type missmatch error

[ChrisMd]

Hello,

I am trying to type the below formula to a cell :

 "=TEXTJOIN("","",TRUE,IF(ISNUMBER(" _
                & "SEARCH(B" & i & "," & descRng & ")),ROW(" & descRng & _
                "),""""))"

The formula returns the row index separated by commas of all the occurrences found.

The searched values are in column B of the below screenshot ("HTS Codes for PNs wo HTS.xlsx") while the second screenshot ("HTS Codes for PNs wo HTS.xlsx") shows the range in which the values should be searched.

"HTS Codes for PNs wo HTS.xlsx" "HTS Codes for PNs wo HTS.xlsx"

I am getting a Type missmatch error. I cannot figure what is causing the error. I would appreciate some guidance towards the solution.

Thanks

Sub test ()
    Dim bomWb As Workbook, htsWb As Workbook
    Dim bomWs As Worksheet, htsWs As Worksheet
    Dim bLrow As Long, hLrow As Long

'lrow and i are private variables declared within the same module

    Dim descRng As Range
    Set bomWb = Workbooks("BOM_1926_GM_09.11.24.xlsx")
    Set bomWs = bomWb.Worksheets(1)
    Set htsWb = Workbooks("HTS Codes for PNs wo HTS.xlsx")
    Set htsWs = htsWb.Worksheets(1)
    bLrow = 755
    hLrow = 103
    Set descRng = bomWs.Range(Cells(2, 6), Cells(bLrow, 6))
    With htsWs
        For i = 2 To hLrow
            .Cells(i, 5).Formula = "=TEXTJOIN("","",TRUE,IF(ISNUMBER(" _
                & "SEARCH(B" & i & "," & descRng & ")),ROW(" & descRng & _
                "),""""))"
        Next i
    End With
End Sub

 

[StephenCrump]

Welcome to the Forum!

Given that descRng points to another workbook/worksheet, you need to fully qualify the reference.

One way to do this would be be to redefine descRng as follows (and also note the use of .Formula2 rather than .Formula):

Dim descRng As String

'....
    
descRng = "'[" & bomWb.Name & "]" & bomWs.Name & "'!F$2:F$" & bLrow

With htsWs
    For i = 2 To hLrow
        .Cells(i, 5).Formula2 = "=TEXTJOIN("","",TRUE,IF(ISNUMBER(" _
            & "SEARCH(B" & i & "," & descRng & ")),ROW(" & descRng & _
            "),""""))"
    Next i
End With

And you don't need to loop through each row:

htsWs.Range("E2:E" & hLrow).Formula2 = "=TEXTJOIN("","",TRUE,IF(ISNUMBER(" _
    & "SEARCH(B2," & descRng & ")),ROW(" & descRng & "),""""))"

 

[ChrisMd]

Welcome to the Forum!

Given that descRng points to another workbook/worksheet, you need to fully qualify the reference.

One way to do this would be be to redefine descRng as follows (and also note the use of .Formula2 rather than .Formula):

Dim descRng As String

'....
   
descRng = "'[" & bomWb.Name & "]" & bomWs.Name & "'!F$2:F$" & bLrow

With htsWs
    For i = 2 To hLrow
        .Cells(i, 5).Formula2 = "=TEXTJOIN("","",TRUE,IF(ISNUMBER(" _
            & "SEARCH(B" & i & "," & descRng & ")),ROW(" & descRng & _
            "),""""))"
    Next i
End With

And you don't need to loop through each row:

htsWs.Range("E2:E" & hLrow).Formula2 = "=TEXTJOIN("","",TRUE,IF(ISNUMBER(" _
    & "SEARCH(B2," & descRng & ")),ROW(" & descRng & "),""""))"

Thank you for your help, Stephen.

I need to loop because I want the formula to be in every cell of the range. The searched value "B2" should change in each iteration.

 

[ChrisMd]

Thank you for your help, Stephen.

I need to loop because I want the formula to be in every cell of the range. The searched value "B2" should change in each iteration.

Nevermind, I just tried it. I did not know the cell updates automatically. Thank you!