Error on my small VBA when condition is met

[jbesclapez]

Hello,

Here is my code :

Sub VlookupAmeliorer()
'
' Macro2 Macro
'


    Dim i As Integer
    Dim LastRowL As Integer
    LastRowL = Sheets("PlanEX-Origine").Cells(Rows.Count, 12).End(xlUp).Row
    For i = 2 To LastRowL
    
    If Len(Cells(23, i)) < 255 Then
    Range("W" & i).FormulaR1C1 = "=+IF(RC[-4]=1,""ok"",VLOOKUP(RC[-1],C[16]:C[24],2,0))"
    Else
    Range("W" & i).FormulaR1C1 = "test"
        End If
    Next i


End Sub

After a short time, it comes with an error 104 Object (or application) defined error.
I am clueless... please can you guide me to the correct solution
Basically i would like the macro to copy a vlookup in every cell untill the last cell if and adjacent cell has less than 255 characters (otherwise VLOOKUP bring errors). If it is above 255 then writes "test".

Thats all. Thanks

 

[Fluff]

This line

If Len(Cells(23, i)) < 255 Then

should be

If Len(Cells(i,24)) < 255 Then

Asuming that "adjacent cell" means col X

 

[jbesclapez]

This line

If Len(Cells(23, i)) < 255 Then

should be

If Len(Cells(i,24)) < 255 Then

Asuming that "adjacent cell" means col X

Thanks Fluff. It solved the problem. But I discovered another one...
I added a formula if the cell length is above 255.

Sub VlookupAmeliorer()
    Dim i As Integer
    Dim LastRowL As Integer
    LastRowL = Sheets("PlanEX-Origine").Cells(Rows.Count, 12).End(xlUp).Row
    For i = 2 To LastRowL
    
    If Len(Cells(i, 24)) < 255 Then
    Range("W" & i).FormulaR1C1 = "=+IF(RC[-4]=1,""ok"",VLOOKUP(RC[-1],C[16]:C[24],2,0))"
    Else
    Range("W" & i).FormulaR1C1 = "=+IF(RC[-4]=1,""ok"",MyVlookup(RC[-1],C[16]:C[24],2))"
        End If
    Next i
End Sub

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
'It's an exact result only. The function loops through the first column in the lookup range and compares it to the lookup value.
'If it is the same, then it returns the value of the relevant cell to the right.


Dim cl As Range
For Each cl In c.Columns(1).Cells
    If UCase(Lval) = UCase(cl) Then
        MyVlookup = cl.Offset(, oset - 1)
        Exit Function
    End If
    Next
End Function

But it does not work....

My ultimate goal is to use the Vlookup if the character length is above 255 and the formula MyVookup otherwise.
I have to find a solution because of this stupid 255 max characteres for the vlookup.

Thanks

 

[Fluff]

What if you change this

If Len(Cells(i, [COLOR=#ff0000]24[/COLOR])) < 255 Then

to 22 not 24

 

[jbesclapez]

What if you change this

If Len(Cells(i, [COLOR=#ff0000]24[/COLOR])) < 255 Then

to 22 not 24

Thanks for your help an time Fluff.

Here is my solution :

I use an index/Match if character is less than 255 and otherwise the formule in MyVlookup.
=IF(S2=1,"ok",+IF(LEN(V2)>255,MyVlookup(V2,TabPassage,2),INDEX(TabProbOK,MATCH(V2,TabIDPassage,0))))

and I use it with the VBA seen above with the changes you told me to do.

Thanks again.

 

[Fluff]

Glad to help & thanks for the feedback