asked on

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.

jppinto

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

Saqib Husain

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

Wilder1626

ASKER

Hello all

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

jppinto, i have change it to .value now.

Saqib Husain

sorry change it to string

Saqib Husain

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

Saqib Husain

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

Wilder1626

ASKER

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.

Saqib Husain

what is the value of

Sheets("SDD File master").Cells(i, 1)

Saqib Husain

Will it be possible to upload a file with few sample data to speed up the process?

Wilder1626

ASKER

oh yes, it would be easy with an example.

Classeur1.xlsm

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

This is so great

Thanks for your help