• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Transfer in format issue

Hello all,

I have a macro that transfer from 1 sheet to another.

 With Sheets("SDD Order File")
                Derlign = .Range("F65000").End(xlUp).Row + 1
                
                'Transfer vendor
                .Cells(Derlign, 6) = Sheets("SDD File master").Cells(i, 23).Text
                
                'Transfer Customers
                .Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), "00000")
                
                
                .Cells(Derlign, 24) = Sheets("SDD File master").Cells(i, 11).Text
                
                'Cube
                .Cells(Derlign, 47) = Format(Sheets("SDD File master").Cells(i, 22), "0")
                
                'Weight
                .Cells(Derlign, 48) = Format(Sheets("SDD File master").Cells(i, 22), "0")
                
                'Pieces
                .Cells(Derlign, 49) = Format(Sheets("SDD File master").Cells(i, 13), "0")
                
                'Palets
                .Cells(Derlign, 50) = Format(Sheets("SDD File master").Cells(i, 14), "0")
                
                'Wac
                .Cells(Derlign, 53) = Format(Sheets("SDD File master").Cells(i, 6), "0")
                
                'Early delivery.Day) + 2 / 1440)
                .Cells(Derlign, 10) = Format(Sheets("SDD File master").Cells(i, 9), "yyyymmdd") & Format(Sheets("SDD File master").Cells(i, 10), "hhmm")
                
                'Late delivery
               ' .Cells(Derlign, 11) = Format((Sheets("SDD File master").Cells(i, 26)) + 1439 / 1440, "yyyymmddhhmm")
    
                'Late delivery
               ' .Cells(Derlign, 3) = Format(Sheets("SDD File master").Cells(i, 12), "0")
    
   End With

Open in new window


The problem is that:
 'Transfer Customers
                .Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), "00000")

Open in new window


and

 'Transfer vendor
                .Cells(Derlign, 6) = Sheets("SDD File master").Cells(i, 23).Text

Open in new window


Needs to be in a specific format.

If the value = 2 digits in the sheet SDD file master, it will transfer it in format "00" in sheet SDD order file.
If the value => 3 gijits in the sheet SDD file master, it will transfer it in format "00000" in sheet SDD order file.


How can i do this please?

Thanks again for your help.
0
Wilder1626
Asked:
Wilder1626
  • 7
  • 4
1 Solution
 
jppintoCommented:
Why do you use .Text?

.Cells(Derlign, 6) = Sheets("SDD File master").Cells(i, 23).Text
try using .Value, like this:

.Cells(Derlign, 6) = Sheets("SDD File master").Cells(i, 23).Value
0
 
Saqib Husain, SyedEngineerCommented:
Try something like

.Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), rept("0",2+3*Sheets("SDD File master").Cells(i, 1)>99))
0
 
Wilder1626Author Commented:
Hello all

It does not take the "rept", it say Sub or Function not define.


jppinto, i have change it to .value now.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Saqib Husain, SyedEngineerCommented:
sorry change it to string
0
 
Saqib Husain, SyedEngineerCommented:
formula revised

.Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), rept(2+3*Sheets("SDD File master").Cells(i, 1)>99,"0"))
0
 
Saqib Husain, SyedEngineerCommented:
Sorry again

.Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), string(2+3*Sheets("SDD File master").Cells(i, 1)>99,"0"))
0
 
Wilder1626Author Commented:
OK, now change to:
.Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), string(2+3*Sheets("SDD File master").Cells(i, 1)>99,"0"))

Open in new window



But now, i have execution error 5. argument procedure incorrect.
0
 
Saqib Husain, SyedEngineerCommented:
what is the value of

Sheets("SDD File master").Cells(i, 1)
0
 
Saqib Husain, SyedEngineerCommented:
Will it be possible to upload a file with few sample data to speed up the process?
0
 
Wilder1626Author Commented:
oh yes, it would be easy with an example.

Classeur1.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
You are right it does not work. Try this instead.

.Cells(Derlign, 7) = Format(Sheets("SDD File master").Cells(i, 1), String(2 + 3 * IIf(Sheets("SDD File master").Cells(i, 1) > 99, 1, 0), "0"))
0
 
Wilder1626Author Commented:
This is so great

Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now