Object Required Error Message

[SAleks]

Hey Guys,

I really need help with the below code. I have data in column F and it is divided in Columns A:D. I want the macro to stop when the cell value in F= "n/a". I get the Object required error message when I run the code....

Sub NameSplit()
Dim var As Variant
    Dim rw As Long, i As Long
    With Worksheets("Test")
        For rw = 2 To .Cells(.Rows.Count, "F").End(xlUp).Row
            var = Split(.Cells(rw, "F").Text, ";")
            .Cells(rw, "A").Resize(1, UBound(var) + 1) = var
        Next rw
        
        If Value.Cells(.Rows.Count, "F").Text = "n/a" Then
        Exit Sub
       
        End If
    End With
End Sub

Appreciate your help with that.... I am quite new to vba, so please don't judge me too hard..
thanks a lot in advance

 

[RoryA]

Welcome to the forum.

Just remove the Value part:

If .Cells(.Rows.Count, "F").Text = "n/a" Then

 

[SAleks]

thanks, but it does not stop.... forgot to mention that column F is populated with vlookup formula,

 

[Fluff]

should it be

If .Cells(Rw, "F").Text = "n/a" Then

 

[RoryA]

Oops - yes, almost certainly!

 

[SAleks]

appreciate your efforts.... but I still get as a result #N/A in Column A...

 

[Fluff]

Untested, but how about

Sub NameSplit()
Dim var As Variant
    Dim rw As Long, i As Long
    With Worksheets("Test")
        For rw = 2 To .Cells(.Rows.Count, "F").End(xlUp).Row
            If Not IsError(.Cells(rw, "F")) Then
            var = Split(.Cells(rw, "F").Text, ";")
            .Cells(rw, "A").Resize(1, UBound(var) + 1) = var
        Next rw
    End With
End Sub

If column F contains an error, the macro will ignore that row & continue.
If you want to quit the macro, when an error is found, let us know

 

[SAleks]

Hey Fluff, it is working, I only added End if before Next rw and the magic happened...
thank you so much for the help.. appreciated

 

[Fluff]

Glad we could help & thanks for the feedback

As for

I only added End if before Next rw and the magic happened