Vba Code if info in cell go to the next one and write something loop

[Zuzazuza]

Hello all,


I have managed to arrange a report with multiple changing data.
After i set a filter on column L on N/A(items not captured in the original formula) i need to check for comments in column K, if there is a comment(some cells are blank), i usually have one out of 3 names there, so it should populate the L cell next to it with the name.

Eg.
filter on N/A
K5,K8,K10 are blank K15 has comment "bla bla bla John Google", i need it to take the name and copy it in L15, only the name John Google. After that in needs to loop until the end of the report which is never the same amount of data.

Could you please help ?

 

[sxhall]

Hi Zuzazuza,

I created a quick table below with the error '#N/A' in column 'Error Column' and the comment with a name at the end in column 'Comment'.

By formatting the data as a table it does not matter if there is one or a thousand entries it will always loop through the entire column. Running the code below it loops through the entire 'Error Column' (no need to filter it) and checks if the value in the cell is an error and there is also a comment. If it is an error and a there is a comment it returns the last two words (assumed the name is always two words and last in the comment) from the 'Comments' column. If there is no comment it skips it.

Row 6 below has no comment so it skips this error whereas row 8 has a comment and it has returnd the name Bob Bing.

Sub CheckforError()

For Each i In Sheet1.Range("table2[Error Column]") '** Name of the column here so it will always check every entry no matter how many entires in the table

    Comment = i.Offset(0, 1).Value '** change the number 1 depending on how many columns over the comment is with the name
        
    If IsError(i.Value) And Comment <> "" Then '** Check that there is an error and there is text in the comment
    
    Name = Split(Comment, " ")
    i.Value = Name(UBound(Name) - 1) & " " & Name(UBound(Name))

    End If
    
Next i

End Sub

Steven