Having trouble with this code

[Nolaughmtr]

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").Cells(cellCount, "A") = Number
.Cells(cellCount, "C") = Text
cellCount = cellCount + 1


Loop
End With




End Sub

 

[OssieMac]

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

 

[Joel]

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]

thanks gents,
but now im getting a error "loop with out do"?

 

[JLGWhiz]

Probably don't like your InStr sytax:

InStr([start, ]string1, string2[, compare])

InStr(Text, " ")))<<<<

You're missing something.

 

[Bob Phillips]

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)

 

[OssieMac]

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

 

[Bill Renaud]

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.

 

[Dave Peterson]

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]

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!)

 

[Bill Renaud]

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!

 

[Nolaughmtr]

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?

 

[Willem]

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?

 

[Ron de Bruin]

Hi Willem

Use the value copy example then
http://www.rondebruin.nl/copy2.htm

See the info below the macro