Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
I get an error at " <<<<" when i try an run it again. basically im
trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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
|
|||
|
|||
Having trouble with this code
Mid function does not work if parameter starts at 0 therefore if instr does
not find a space then the mid function falis. Try running instr first and then test with if/then/else for greater than zero and if it is greater than zero then run mid function. You should be able to use code similar to your last row code to find extent of data on source sheet. similar code works with columns. Might also try:- Set SourceRange = Sheets("we 9-8-07").UsedRange regards, OssieMac "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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
|
|||
|
|||
Having trouble with this code
Instr will cause an error if it doesn't find a space. Put in this fix. You
may also want to trim text if it doesn't find a space. if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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
|
|||
|
|||
Having trouble with this code
thanks gents,
but now im getting a error "loop with out do"? "Joel" wrote: Instr will cause an error if it doesn't find a space. Put in this fix. You may also want to trim text if it doesn't find a space. if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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
|
|||
|
|||
Having trouble with this code
Probably don't like your InStr sytax:
InStr([start, ]string1, string2[, compare]) InStr(Text, " ")))<<<< You're missing something. "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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
|
|||
|
|||
Having trouble with this code
Maybe because that If isn't closed with an End If?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nolaughmtr" wrote in message ... thanks gents, but now im getting a error "loop with out do"? "Joel" wrote: Instr will cause an error if it doesn't find a space. Put in this fix. You may also want to trim text if it doesn't find a space. if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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
|
|||
|
|||
Having trouble with this code
If you used Joel's code did you add the end if like this:-
if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) end if Regards, OssieMac "Nolaughmtr" wrote: thanks gents, but now im getting a error "loop with out do"? "Joel" wrote: Instr will cause an error if it doesn't find a space. Put in this fix. You may also want to trim text if it doesn't find a space. if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
What version of Excel are you using?
In Excel 2000, I believe that Cells(cellCount, "A") is not valid. Both arguments must be a Long (number), of the form Cells(rowindex, columnindex) If you want to use letters for the column part, then you have to use the Range property, unless this has been changed in Excel 2007. -- Regards, Bill Renaud |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
Test that again.
You'll find that the column argument can be a number or a string (well, as long as it's valid!). Bill Renaud wrote: What version of Excel are you using? In Excel 2000, I believe that Cells(cellCount, "A") is not valid. Both arguments must be a Long (number), of the form Cells(rowindex, columnindex) If you want to use letters for the column part, then you have to use the Range property, unless this has been changed in Excel 2007. -- Regards, Bill Renaud -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
Dave wrote:
<<You'll find that the column argument can be a number or a string (well, as long as it's valid!). Correct! (Incomplete Help documentation? I never trust these "undocumented" features, as Microsoft may change (fix?) them in a later release!! I have enough trouble building robust code as it is! Smile!) -- Regards, Bill Renaud |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
OK. I'll fess up this time!
"Item Property (Range Object)" documents that the arguments are Variants, and therefore "A" is allowed. How many pages of Help do you have to read to get all the answers! -- Regards, Bill Renaud |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
Ossie,
That was the problem right there (end if). But for some reason it doesnt split the first cell like I want it to when I run it again. Also when I run it again it freezes up. Maybe I have my loop wrong? "OssieMac" wrote: If you used Joel's code did you add the end if like this:- if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) end if Regards, OssieMac "Nolaughmtr" wrote: thanks gents, but now im getting a error "loop with out do"? "Joel" wrote: Instr will cause an error if it doesn't find a space. Put in this fix. You may also want to trim text if it doesn't find a space. if InStr(Text, " ") 0 then Text = Trim(Mid(Text, InStr(Text, " "))) "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
Help me when I coppy info from multipil worksheets to asummary one some of
the info are only formulas and not the data. How can i fix this? "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Having trouble with this code
Hi Willem
Use the value copy example then http://www.rondebruin.nl/copy2.htm See the info below the macro -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Willem" wrote in message ... Help me when I coppy info from multipil worksheets to asummary one some of the info are only formulas and not the data. How can i fix this? "Nolaughmtr" wrote: I get an error at " <<<<" when i try an run it again. basically im trying to coppy information from one page to another and then splitting the fist row on the second page. I would also like help on trying to add more information from the 1st sheet to the second sheet. its not constant information so im a bit confused on how to do it. If anyone wants to look at the file please let me know because I know this is pretty confusing. Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("we 9-8-07").Range("F2:G93") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("DIE STATUS") Lr = DestSheet.Cells(Rows.count, "A").End(xlUp).Row 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.COPY DestRange With Application .ScreenUpdating = True .EnableEvents = True End With 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 | |||
IM A NEW USER HAVING TROUBLE WITH CODE | Excel Programming | |||
Trouble with code | Excel Programming | |||
Trouble with Code, but only sometimes | Excel Programming | |||
Trouble with this code | Excel Programming | |||
Trouble with this code | Excel Programming |