Excel VBA 424 error Object Required, in Cells(row, col).Value

Question

This is my first VBA Script, when i rub below code, i got 424 Error on Cells(row, col).Value = Val(data(index)) and Cells(row, col).Value = data(index). i am freshman on VBA Script. so how to fix this error.

Thanks Very much.

Sub ImportFile()
Dim TextLine
Dim drng As Range
Dim n As Integer
Dim row As Long, col As Long
Dim inde As Integer
Dim data() As String
Dim BegRow As Long, BegCol As Long
Dim index As Integer
Dim cell As Range
BegRow = 2 
BegRow = 1 
BegCol = 1
Columns("A").AutoFit
Columns("B").AutoFit
Columns("C").AutoFit
Columns("D").AutoFit
Columns("E").AutoFit
Columns("F").AutoFit
Columns("G").AutoFit
Columns("H").AutoFit
Columns("I").AutoFit
Columns("J").AutoFit
Columns("K").AutoFit
Columns("L").AutoFit
Columns("M").AutoFit
Columns("M").AutoFit
Columns("O").AutoFit
Columns("P").AutoFit
Columns("Q").AutoFit
Columns("R").AutoFit
Columns("S").AutoFit
Columns("T").AutoFit


With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select txt file"
    .Filters.Add "All Files", "*.txt"
    .FilterIndex = 2
    .AllowMultiSelect = False
    .Show

        f = Trim(.SelectedItems.Item(1))
        Open f For Input As #1        
        Do While Not EOF(1)           
            Line Input #1, TextLine    
            TextLine = DeleteSpace(Trim(TextLine))
            data = Split(TextLine, "|")
            n = UBound(data)
            col = BegCol
            For index = 0 To n
                If IsNumeric(data(inde)) Then
                    `**Cells(row, col).Value = Val(data(index))**`
                Else
                    **Cells(row, col).Value = data(index)**
                End If
                col = col + 1
            Next  ' Index
            col = BegCol
            row = row + 1
        Loop
        Close #1    
End With

End Sub

Function DeleteSpace(ByVal str As String) As String Dim Strtemp As String

Strtemp = str
While Replace(Strtemp, "  ", " ") <> Strtemp
    Strtemp = Replace(Strtemp, "  ", " ")
Wend
DeleteSpace = Strtemp

End Function

`

Did you initialize row value? Also, Use Columns("A:T").AutoFit instead of individual columns — Ravi Yenugu
– Ravi Yenugu, Commented Aug 27, 2014 at 17:41
A) Where are you initializing the value of Row and Col B) Also "IsNumeric(data(inde))"? Missing an "X" in "Inde" — Siddharth Rout
– Siddharth Rout, Commented Aug 27, 2014 at 17:52

Gary's Student · Accepted Answer · 2014-08-27 20:25:13Z

1

row has not been assign a value prior to its use. There may be other errors.

answered Aug 27, 2014 at 20:25

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA 424 error Object Required, in Cells(row, col).Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related