You pointed me in the right direction. After some modification here is the
final outcome. <I really need to get my boss to allow me training in code

Sub Formatting()
Dim c As Variant
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Set c = ActiveCell
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 41
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
Set c = ActiveCell
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 42
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
Set c = ActiveCell
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 43
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
Set c = Nothing
ActiveWorkbook.Save

End Sub


I had a problem with the code line:

Set c = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

For some reason it would not take, hence the change to Set c= ActiveCell.
It works now and that is what matters. Thanks.
--
Thank you for your time.
Windows XP
Office 2002


"Martin Fishlock" wrote:

James:

You need something like the following, but there is a problem with stopping
it.
You need a stopper in the find.


Sub Formatting()
Dim c As Variant
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Set c = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Do While TypeName(c) = "Range"
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 41
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
If c.Row = 65535 Then Exit Do
Set c = Cells.FindNext(ActiveCell).Select
' need a stopper in here for the end of the values try:
Loop
Set c = Nothing
ActiveWorkbook.Save

End Sub

--
HTHs Martin