VBA Paste special issue

[dario88m]

Hi! Is it possible to paste values formatted as number? Somehow after running the macro certain values in the sheets are "number stored as text", resulting in #N/A for lookups in the following steps/worbooks.

Example:

Set b = Worksheets("Batch").Range("a2:a9999").Find(Worksheets("1").Range("a2"))  ' Finds cell with batch number - this value is 8 digits yymmdd01,02 and so on


If b Is Nothing Then ' Selects first empty line
    ans = MsgBox("This action will set the batch as completed and erase the information in the template. Do you want to continue?", 4, "Batch completion confirmation")
    
    If ans = 6 Then
        Worksheets("1").Activate
        Range("a2:ac2").Select  ' Selects all the roll lines at once
        Selection.Copy
        Worksheets("Batch").Activate
        [A9999].End(xlUp)(2, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False
        

        
        Worksheets("1").Activate
        Range("a4:d6").Select
        Selection.Copy
        Worksheets("Lane").Activate
        [A9999].End(xlUp)(2, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False

Any ideas? I tried already this xlPasteValuesAndNumberFormats without success.

Thanks

 

[aaewalsh]

If b Is Nothing Then ' Selects first empty line
    ans = MsgBox("This action will set the batch as completed and erase the information in the template. Do you want to continue?", 4, "Batch completion confirmation")
    
    If ans = 6 Then
        Worksheets("1").Activate
        Range("a2:ac2").Select  ' Selects all the roll lines at once
        Selection.Copy
        Worksheets("Batch").Activate
        [A9999].End(xlUp)(2, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False
        

        
        Worksheets("1").Activate
        Range("a4:d6").Select
        Selection.Copy
        Worksheets("Lane").Activate
        [A9999].End(xlUp)(2, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False

 

[dario88m]

If b Is Nothing Then ' Selects first empty line
    ans = MsgBox("This action will set the batch as completed and erase the information in the template. Do you want to continue?", 4, "Batch completion confirmation")
   
    If ans = 6 Then
        Worksheets("1").Activate
        Range("a2:ac2").Select  ' Selects all the roll lines at once
        Selection.Copy
        Worksheets("Batch").Activate
        [A9999].End(xlUp)(2, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False
       

       
        Worksheets("1").Activate
        Range("a4:d6").Select
        Selection.Copy
        Worksheets("Lane").Activate
        [A9999].End(xlUp)(2, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False

Hi, as I mentioned in the description that didn't work unfortunately

 

[aaewalsh]

If you are still getting the 'Number Stored as Text' error, it could be because the data you are copying contains cells that have been formatted as text. To fix this, you can use the .NumberFormat property to change the format of the cell to "General" or "Number" before pasting the values.

Additionally, you can try using the .value property to copy the value of the cell and then paste it using the .value property instead of using the .copy and .pasteSpecial methods. This will also paste the values as numbers rather than text.

For example:

Copy code

Worksheets("1").Range("A2:AC2").value = Worksheets("1").Range("A2:AC2").value

It's also possible that the problem is not in the paste operation but rather in the data source you are copying from. It's also important to check that data source and make sure it doesn't contain any text-formatted cells that could be causing this issue.

 

[dario88m]

If you are still getting the 'Number Stored as Text' error, it could be because the data you are copying contains cells that have been formatted as text. To fix this, you can use the .NumberFormat property to change the format of the cell to "General" or "Number" before pasting the values.

Additionally, you can try using the .value property to copy the value of the cell and then paste it using the .value property instead of using the .copy and .pasteSpecial methods. This will also paste the values as numbers rather than text.

For example:

Copy code

Worksheets("1").Range("A2:AC2").value = Worksheets("1").Range("A2:AC2").value

It's also possible that the problem is not in the paste operation but rather in the data source you are copying from. It's also important to check that data source and make sure it doesn't contain any text-formatted cells that could be causing this issue.

bit more information : =IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"), C3),"") is to get the desired value then =IF(F_F!C4<>"",F_F!C4,"") that is used in the macro. cell has number format

 

[aaewalsh]

Based on the additional information you've provided, it seems that the problem is related to how the data is being formatted before it's being copied and pasted. The formula =IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"), C3),"") is used to create a string by concatenating the date in the format "yymmdd" from cell R4 in sheet 3 with the value in cell C3, only if C3 is not empty.

The formula =IF(F_F!C4<>"",F_F!C4,"") is used to extract the value from cell C4 in sheet "F_F", only if it's not empty.

It seems that the problem is that the data being concatenated by the first formula is being treated as text, and not as a number, even though the data source has number format.

You can try using the TEXT() function to format the cell C3 as a number before concatenating it with the date.

For example:

=IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"),TEXT(C3,"0")),"")

This will convert the value in cell C3 to a number format before concatenating it with the date.

Alternatively, you can use the VALUE() function to convert the cell C3 to a number before concatenating it with the date.

=IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"),VALUE(C3)),"")

This will also ensure that the value of C3 is a number before concatenation.

You can also use the .value property to extract the value of the cell C4 in sheet "F_F" instead of using the formula

=IF(F_F!C4<>"",F_F!C4,"")

Worksheets("F_F").Range("C4").value

This will extract the value of the cell as number.

It's important to ensure that the data being copied and pasted is in the correct format, especially when working with formulas that involve concatenation, lookup and other calculations.

 

[dario88m]

Based on the additional information you've provided, it seems that the problem is related to how the data is being formatted before it's being copied and pasted. The formula =IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"), C3),"") is used to create a string by concatenating the date in the format "yymmdd" from cell R4 in sheet 3 with the value in cell C3, only if C3 is not empty.

The formula =IF(F_F!C4<>"",F_F!C4,"") is used to extract the value from cell C4 in sheet "F_F", only if it's not empty.

It seems that the problem is that the data being concatenated by the first formula is being treated as text, and not as a number, even though the data source has number format.

You can try using the TEXT() function to format the cell C3 as a number before concatenating it with the date.

For example:

=IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"),TEXT(C3,"0")),"")

This will convert the value in cell C3 to a number format before concatenating it with the date.

Alternatively, you can use the VALUE() function to convert the cell C3 to a number before concatenating it with the date.

=IF(C3<>"",CONCATENATE(TEXT('3'!R4, "yymmdd"),VALUE(C3)),"")

This will also ensure that the value of C3 is a number before concatenation.

You can also use the .value property to extract the value of the cell C4 in sheet "F_F" instead of using the formula

=IF(F_F!C4<>"",F_F!C4,"")

Worksheets("F_F").Range("C4").value

This will extract the value of the cell as number.

It's important to ensure that the data being copied and pasted is in the correct format, especially when working with formulas that involve concatenation, lookup and other calculations.

The first two examples could work but I'm loosing the 0 after the date. I have a dropdown list where you select 01 , 02 or 03 etc. so today the value would be 23011301 but if I use your formula I get 2301131.

What shold I do to make it more simpler??