0

I am getting a random error when running my vba code. My code is referring to a function and this one is getting me a run time error.

Here is the code :

Public Function List_maker_sans_vide(list_name) As Variant

Dim LastColumn As Integer, ColumnIndex As Integer, c As Range, liste() As Variant, lastrow As Integer

LastColumn = ThisWorkbook.Sheets("nomenclatures").Cells(1, ThisWorkbook.Sheets("nomenclatures").Columns.Count).End(xlToLeft).Column

With ThisWorkbook.Sheets("nomenclatures").Range("1:" & LastColumn)
    Set c = .Find(list_name, LookIn:=xlValues)
    If Not c Is Nothing Then
        ColumnIndex = c.Column
    End If
End With


lastrow = ThisWorkbook.Sheets("nomenclatures").Cells(1, ColumnIndex).End(xlDown).Row
>>> error occuring here on lastrow
ReDim liste(lastrow - 1)

For i = 0 To lastrow - 1
    liste(i) = ThisWorkbook.Sheets("nomenclatures").Cells(i + 2, ColumnIndex).Value
Next i

ReDim Preserve liste(UBound(liste) - 1)

List_maker_sans_vide = liste

End Function
Error 1004
  • 7,877
  • 3
  • 23
  • 46
Jouvzer
  • 57
  • 6
  • 2
    `ColumnIndex` is probably 0. – BigBen Apr 13 '21 at 13:18
  • Also, [this is the correct way to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Apr 13 '21 at 13:19
  • `.Range("1:" & LastColumn)` is a range containing `.Rows` (not Columns) from `1 to LastColumn` (not LastRow). Please do clarify or correct it. You could do something like `.Columns(1).Resize(, LastColumn)` to refer to the columns from `1 to LastColumn`. – VBasic2008 Apr 13 '21 at 13:23

0 Answers0