help with this loop macro
thanks joel
"Joel" wrote:
IAparethesis is missing
isempty(Cells(CellCount, "A"))
"Nolaughmtr" wrote:
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" wrote:
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" wrote:
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" wrote:
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" wrote:
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
|