excel - Excel VBA Apostrophe Disappears on Copy

Question

I am reworking a macro deals with long integers that cannot be coerced into scientific notation (think product ID numbers). In a normal world I would just transform them into a string, but Excel seems to still see a sting of numbers as a numeric, even when classified as text.

For this reason I have to append an apostrophe to entire column before changing anything else with the following code:

Function CleanColumn(Col As String, ws As Worksheet, Optional wb As Workbook)
    If wb Is Nothing Then Set wb = ThisWorkbook
    With ws
        arr = .Range(.Cells(2, Col), .Cells(.Rows.Count, Col).End(xlUp)).Value2
        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 1) = Chr(39) & Replace(arr(i, 1), Chr(45), vbNullString)
        Next i
        .Cells(2, Col).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    End With
End Function

This works well, however I now need to take the dataset and divide it into seperate worksheets based on the value of one column. For this I used the following:

Function CopyByCriteria(Criteria As String, FilterRange As Range, SourceSheet As Worksheet, DestinationSheet As Worksheet)
    Dim pasteRow As Integer
    Dim c As Range

    pasteRow = 1

    For Each c In FilterRange
        If c = Criteria Then
            DestinationSheet.Rows(pasteRow) = SourceSheet.Rows(c.Row).Value2
            pasteRow = pasteRow + 1
        End If
    Next c
End Function

The problem is that when I use the CopyByCriteria function, the apostrophe that I added in is not present on the new worksheet and the numbers drop into scientific notation.

What is the solution to this?

标签： excelvbalong-integer

Answer 1

Edit: The apostroph is not really a part of a formula/value of a cell. It's stored in a PrefixCharacter property (you can access it with ?ActiveCell.PrefixCharacter in your Immediate window). Source: https://www.ozgrid.com/forum/forum/help-forums/excel-general/136277-apostrophe-in-front-of-text

To be 100% sure that you are copying this character, you would have to copy the value/formula AND the PrefixCharacter. But that's impossible for the whole row at once.

The code below MIGHT work, but might not (for me it worked, but stopped working soon for no particular reason).

Function CopyByCriteria(Criteria As String, FilterRange As Range, SourceSheet As 
Worksheet, DestinationSheet As Worksheet)
    Dim pasteRow As Integer
    Dim c As Range

    pasteRow = 1

    For Each c In FilterRange
        If c = Criteria Then
            DestinationSheet.Rows(pasteRow).formula = SourceSheet.Rows(c.Row).formula
            pasteRow = pasteRow + 1
        End If
    Next c
End Function

To be 100% sure I would suggest using either the Range.Copy method or manually copy (like the OP did with Value) both Formula and PrefixCharacter of each cell in a row seperately. Maybe I will provide code for it when I have some time.