help with this loop macro

[Nolaughmtr]

I have this macro but I have a problem with it working when I add more data.
I guess this macro starts from the top and doesnt recognize since its already
done it. I think I need an "if. then" line but not sure how to exacute it.
pls hlp. thanks.

Sub seperatedata()
Worksheets("DIE STATUS").Activate
CELLCount = 2
With Worksheets("Die status")
Do While Cells(CELLCount, "A") <> ""

Number = Val(Cells(CELLCount, "A"))
Text = Cells(CELLCount, "A")
Text = Trim(Mid(Text, InStr(Text, " ")))
.Cells(CELLCount, "A") = Number
.Cells(CELLCount, "C") = Text
CELLCount = CELLCount + 1

Loop
End With
End Sub

 

[Joel]

The only way the loop wouldn't go to the end is if there is blank data in
column A.
Try this

Sub seperatedata()
Worksheets("DIE STATUS").Activate
LastRow = cells(rows.count,"A").end(xlup).row

With Worksheets("Die status")
For CELLCOUNT = 2 to lastRow
if not isemptyCells(CELLCount, "A")) then
Number = Val(Cells(CELLCount, "A"))
Text = Cells(CELLCount, "A")
Text = Trim(Mid(Text, InStr(Text, " ")))
.Cells(CELLCount, "A") = Number
.Cells(CELLCount, "C") = Text
end if
next CELLCOUNT
End With
End Sub

 

[Nolaughmtr]

well see it does go all the way the first time but when I add additional
data, it gets an error because it reads the first line and it cant trim it.
so I guess what im asking is how do i have it start when I enter in the new
data?

 

[Joel]

The problem is instr returns a zero and then the mid function fails. I fixed
the code below. I didn't realize you were getting an error. You didn't say
and I'm not a mind reader.

Sub seperatedata()
Worksheets("DIE STATUS").Activate
LastRow = cells(rows.count,"A").end(xlup).row

With Worksheets("Die status")
For CELLCOUNT = 2 to lastRow
if not isemptyCells(CELLCount, "A")) then
Number = Val(Cells(CELLCount, "A"))
Text = Cells(CELLCount, "A")
if InStr(Text, " ") > 0 then
Text = Trim(Mid(Text, InStr(Text, " ")))
end if
.Cells(CELLCount, "A") = Number
.Cells(CELLCount, "C") = Text
end if
next CELLCOUNT
End With
End Sub

 

[Nolaughmtr]

sorry to not inform you about the error. it was my mistake.
I coppied and pasted the macro but the "isemptyCells(CellCount, "A")) then"
is showing up red.

 

[Joel]

IAparethesis is missing

isempty(Cells(CellCount, "A"))

 

[Nolaughmtr]

thanks joel

IAparethesis is missing

isempty(Cells(CellCount, "A"))