ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   help with this loop macro (https://www.excelbanter.com/excel-programming/397360-help-loop-macro.html)

Nolaughmtr
help with this loop macro
 
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
help with this loop macro
 
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

Nolaughmtr
help with this loop macro
 
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

joel
help with this loop macro
 
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

Nolaughmtr
help with this loop macro
 
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

joel
help with this loop macro
 
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

Nolaughmtr
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


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com