Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Macro | Excel Programming | |||
Do until loop with use of another macro in loop | Excel Programming | |||
how to put a loop in a macro? | New Users to Excel | |||
Macro - Loop or Next | Excel Programming | |||
Loop Macro | Excel Programming |