adding left action to copy

EDUCATED MONKEY · Aug 28, 2011

win xp professional office 2007 ie8

I have dim Mystring, Newstring as strings
the problem i have is that the items to be copied are 11 chars and i only need the first 10 so i inserted Newstring = Left(Mystring, 10)
as indicated, if you can fix this problem and tell what i am doing wrong

Code:

Nextrow = wsDest.Range("A" & Rows.Count).End(xlUp).Row 'Next empty row on wsDest
    Mystring = wsSource.Cells(1, "A").Resize(q).Value
>    Newstring = Left(Mystring, 10)
    wsDest.Cells(Nextrow, "A").Resize(q).Value = Newstring
    wsDest.Cells(Nextrow, "B").Resize(q).Value = wsSource.Cells(1, "B").Resize(q).Value

Peter_SSs · Aug 29, 2011

Could we see the rest of the code?
Or at least the bits where Mystring, Newstring and q are declared
and where q is set to some value.

EDUCATED MONKEY · Aug 29, 2011

Peter_SSs said:
Could we see the rest of the code?
Or at least the bits where Mystring, Newstring and q are declared
and where q is set to some value.

Thank you for your reply, the source worksheet contains columns of data, an isbn number and title next to each other, these are to be re-assembled on the destination worksheet to form a two column pair so that vlookup can find the isbn and return the tile, the problem has come about as some of the data supplied had a white space at the end of the ten digit isbn number making it eleven so the lookup failed.
the column pairs are 96 rows deep if you need any more info i will respond as quickly as i can regards pete

Code:

Sub TRANSFFER4()
'
' TRANSFFER4 Macro
' Macro recorded 04/08/2011 by Peter Hayward
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
'
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim q As Long, Nextrow As Long
    Dim total As Integer
    
    Dim rawdata As Integer
    Dim MyString As String
    Dim Newstring As String
  
    
    Application.ScreenUpdating = False
      total = 187
    q = 96
    Set wsSource = Worksheets("source")
    Set wsDest = Worksheets("destination")
    For rawdata = 0 To total

    Nextrow = wsDest.Range("A" & Rows.Count).End(xlUp).Row 'Next empty row on wsDest
    
            MyString = wsSource.Cells(1, "A").Resize(q).Value
    
   wsDest.Cells(Nextrow, "A").Resize(q).Value = Newstring
   
      Newstring = Left(MyString, 10)
       
    wsDest.Cells(Nextrow, "B").Resize(q).Value = wsSource.Cells(1, "B").Resize(q).Value
    
  wsSource.Select
 
     Columns("A:B").Select
   Selection.Delete Shift:=xlToLeft
    
   
   Next rawdata
    
    
    Application.ScreenUpdating = True
 
End Sub

Peter_SSs · Aug 29, 2011

I can see a few issues with the code, but before delving into that ..

Are you doing all this just to get rid of the white spaces?
If so, could you just do a Find/Replace to rid the data of the white spaces?

EDUCATED MONKEY · Aug 29, 2011

Peter_SSs said:
I can see a few issues with the code, but before delving into that ..

Are you doing all this just to get rid of the white spaces?
If so, could you just do a Find/Replace to rid the data of the white spaces?

Thank you for your reply, as the original data was entered by lots of individuals some have type the ten digits others have type ten plus a space and other couple of spaces, so the problem is not just to get rid of the white space more to retrieve the ten digits, these are then passed on to a conversion process to generate 13 digit numbers these will then match the bar code scanner, I would very much like the benefit of your knowledge with regards to the issues you have spotted <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

> </o

>
Regards pete<o

></o

>

Peter_SSs · Aug 29, 2011

I still don't have a good idea of the overall data and requirements. However, let's see if we can make some progress.

It seems to me that you want to take values from A1:A96 (I have no idea what the 96 has to do with anything) of wsSource and put the left hand 10 characters of those values into the next available 96 rows of column A in wsDest.

If that is correct, then try this in a copy of your workbook.

 Sub TRANSFFER_5() Dim wsSource As Worksheet, wsDest As Worksheet Dim q As Long, Nextrow As Long Application.ScreenUpdating = False q = 96 Set wsSource = Worksheets("source") Set wsDest = Worksheets("destination") Nextrow = wsDest.Range("A" & Rows.Count).End(xlUp).Row + 1 With wsDest.Cells(Nextrow, "A").Resize(q, 2) .Value = wsSource.Cells(1, "A").Resize(q, 2).Value .Resize(, 1).TextToColumns Destination:=.Cells(1, 1), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 2), Array(10, 9)) End With Application.ScreenUpdating = True End Sub 

I'm not going to try to cover evertything in your original code but here area few observations.

1. To find the next available row (Nextrow) in wsDest, you need to add 1 to the row after using xlup otherwise you will over-write the last item of data. See my code.

2. Your code declares Mystring as a string but then tries to assign the values from 96 cells to it. That won't work.

Code:

MyString = wsSource.Cells(1, "A").Resize(q).Value

3. Your code tries to put Newstring into wsDest before it even tries to say what Newstring is:

Code:

wsDest.Cells(Nextrow, "A").Resize(q).Value = Newstring
   
Newstring = Left(MyString, 10)

4. It may be what you want but your code seems to be trying to transfer the same 96 rows of data 187 times to different sections of wsDest.

Code:

total = 187
For rawdata = 0 To total
.
.
.
Next rawdata

EDUCATED MONKEY · Aug 29, 2011

Peter_SSs said:
I still don't have a good idea of the overall data and requirements. However, let's see if we can make some progress.

It seems to me that you want to take values from A1:A96 (I have no idea what the 96 has to do with anything) of wsSource and put the left hand 10 characters of those values into the next available 96 rows of column A in wsDest.

If that is correct, then try this in a copy of your workbook.

 Sub TRANSFFER_5() Dim wsSource As Worksheet, wsDest As Worksheet Dim q As Long, Nextrow As Long Application.ScreenUpdating = False q = 96 Set wsSource = Worksheets("source") Set wsDest = Worksheets("destination") Nextrow = wsDest.Range("A" & Rows.Count).End(xlUp).Row + 1 With wsDest.Cells(Nextrow, "A").Resize(q, 2) .Value = wsSource.Cells(1, "A").Resize(q, 2).Value .Resize(, 1).TextToColumns Destination:=.Cells(1, 1), _ DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 2), Array(10, 9)) End With Application.ScreenUpdating = True End Sub 

I'm not going to try to cover evertything in your original code but here area few observations.

1. To find the next available row (Nextrow) in wsDest, you need to add 1 to the row after using xlup otherwise you will over-write the last item of data. See my code.

2. Your code declares Mystring as a string but then tries to assign the values from 96 cells to it. That won't work.

Code:

MyString = wsSource.Cells(1, "A").Resize(q).Value

3. Your code tries to put Newstring into wsDest before it even tries to say what Newstring is:

Code:

wsDest.Cells(Nextrow, "A").Resize(q).Value = Newstring Newstring = Left(MyString, 10)

4. It may be what you want but your code seems to be trying to transfer the same 96 rows of data 187 times to different sections of wsDest.

Code:

total = 187 For rawdata = 0 To total . . . Next rawdata

Thanks I will read it all again tomorrow,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

> </o

>
as to the values 96 etc I know they are all a bit odd but they are isbn numbers in ranges and the construction of each number is also rather complicated this is what applied here in the uk first digit be it 0,1 or 9 means the text is in English following that is the publisher id which can be from 2 to 5 digit wide, <o

></o

>
<o

> </o

>
then comes the title id again this is linked to the publisher finally the last digit id the check digit and is calculated by Appling a mathematical formula to all the digits so providing error checking then just to throw a spanner in the works bar codes are 13 digit isbns so the tens need converting to 13 so you can use a bar code reader <o

></o

>
<o

> </o

>
any way thanks for your help I seem to be over the worst of this project now thanks to the effort of members on this forum<o

></o

>
and just got keep turning the handle and hope the expected result come out <o

></o

>
<o

> </o

>
I have learned a good deal about this vba, and there has been times when I wish I had done it all in C++<o

></o

>
<o

> </o

>
If there is anything that you need to know just ask
<o

> </o

>
Regards Pete

EDUCATED MONKEY · Aug 30, 2011

EDUCATED MONKEY said:
Thanks I will read it all again tomorrow,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
as to the values 96 etc I know they are all a bit odd but they are isbn numbers in ranges and the construction of each number is also rather complicated this is what applied here in the uk first digit be it 0,1 or 9 means the text is in English following that is the publisher id which can be from 2 to 5 digit wide, <o></o>
<o> </o>
then comes the title id again this is linked to the publisher finally the last digit id the check digit and is calculated by Appling a mathematical formula to all the digits so providing error checking then just to throw a spanner in the works bar codes are 13 digit isbns so the tens need converting to 13 so you can use a bar code reader <o></o>
<o> </o>
any way thanks for your help I seem to be over the worst of this project now thanks to the effort of members on this forum<o></o>
and just got keep turning the handle and hope the expected result come out <o></o>
<o> </o>
I have learned a good deal about this vba, and there has been times when I wish I had done it all in C++<o></o>
<o> </o>
If there is anything that you need to know just ask
<o> </o>
Regards Pete

Thanks for your help, cannot see how I failed to notice my errors? Guess it’s that I out of practice anyway not had a minute to try the code yet, on another matter I am using two pc’s to retrieve data connected to the ISBN numbers which number about 6.5 million by creating URL’s to the data which work great on one pc that is running xp professional office 2003 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
On the other xp home and office 2007 runs for about 5 hours or 2700 iterations than fails and I cannot see why? And the problem is not with the incoming data as it’s is quite happy to process it when I restart the macro!<o

></o

>
I have been looking around the setting for office 2007 and cannot see anything that may be triggering the failure<o

></o

>
<o

> </o

>
Regards pete

adding left action to copy

EDUCATED MONKEY

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

EDUCATED MONKEY

Board Regular

Peter_SSs

MrExcel MVP, Moderator

EDUCATED MONKEY

Board Regular

Peter_SSs

MrExcel MVP, Moderator

EDUCATED MONKEY

Board Regular

EDUCATED MONKEY

Board Regular

Similar threads

Forum statistics

Share this page

adding left action to copy

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock