Application Object defined error (1004)

0
votes

I'm trying to create a method that creates a value in a certain column of the excel sheet. Keep running into the runtime error above on the Sheets.Cells.Value = Cint line. Any idea what could be the problem? Thanks!

For Each Cell In Sheets(tab_name).Range(cell_range)
    current_row = Cell.Row
    split_cells = Split(Cell.Value, ".")
    Sheets(tab_name).Cells(current_row, 58).Value = CInt(split_cells(0))
Next Cell
2
excelvbaruntime-error
Does it have "." ? Check the array split_cells first before using it – Nathan_Sav
You should verify if cell is not empty – h2so4
Maybe split_cells = Split(Cell.Value & ".", ".") ...? The split_cells(0) will still be correct if there is something to split and should work as a vbnullstring if not. – user4039065
I can't replicate this with sample data. Note that the 1004 error won't be related to the CInt - that would either be a subscript error or a type mismatch. Set a breakpoint an test the values of Sheets(tab_name).Cells(current_row, 58) to see if it's a valid range. – Comintern
I stepped through it and the cells(current_row, 58) is empty, but I want to change that empty value to a different value. Does it have to have a value there? – ViggieSmalls

2 Answers

0
votes

Would INT(Cell.Value) not suffice for what you are trying to do?

0
votes

The cell must be empty. Split will return an array for any non-empty string.

enter image description here Here is a simpler way to write it:

For Each Cell In Sheets(tab_name).Range(cell_range)

    With Cell.EntireRow
        If Cell <> "" Then 
            split_cells = Split(Cell.Value, ".")
           .Cells(1, 58).Value = CInt(split_cells(0))
        Else
           .Cells(1, 58).Value = 0
        End If

    End With
Next Cell