vba - text to Number issue

[Mallesh23]

Hi Team,

I want to fillter my data and paste into output sheet.
as my data is huge I am using Collections

My code is working, But numbers which has text getting pasted in General format and losing actual value

'02 become 2 only.

[CODE=vba][CODE=vba]Sub seperate_Data()

        Dim coll As New Collection
        Dim rg As Range
        Dim i As Long
        Dim sht_data As Worksheet
        Dim sht_output As Worksheet
        Dim StartRow As Long
        StartRow = 1
        
        
        Set sht_data = ThisWorkbook.Worksheets("Data")
        Set sht_output = ThisWorkbook.Worksheets("Output")
        
        
        Set rg = sht_data.Range("A1").CurrentRegion

        For i = 2 To rg.Rows.Count
                       If rg.Cells(i, 2) = 70 Then
                           coll.Add rg.Rows(i).Value
                       End If
        Next i
        
        
        If coll.Count > 0 Then
                'Write Data to worksheet
                    WriteData sht_output, StartRow + 1, coll
        End If
        
        MsgBox "Macro Successful"

End Sub

Sub WriteData(ByVal sh As Worksheet, ByVal StartRow As Long, coll As Collection)

    Dim item As Variant, Row As Long, Columns As Long

    'sh.Cells.ClearContents
    sh.Range("A1").CurrentRegion.Offset(1).ClearContents

    Row = StartRow
    For Each item In coll
        Columns = UBound(item, 2)
        sh.Cells(Row, 1).Resize(1, Columns).Value = item
        Row = Row + 1
    Next

End Sub

[/CODE][/CODE]


Dummy Data

Book2
	A	B	C	D	E
1	Transaction NO	Customer group	Colx	coly	Colz
2	0265	60	xxx	yyy	zzz
3	0678	70	xxx	yyy	zzz
4	0245	70	xxx	yyy	zzz
5	0365	70	xxx	yyy	zzz
6	0213	70	xxx	yyy	zzz
7	0414	60	xxx	yyy	zzz
Data

Output Via Macro --- and expected Output

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Transaction NO	Customer group	Colx	coly	Colz		EXPECTED	Transaction NO	Customer group	Colx	coly	Colz
2	678	70	xxx	yyy	zzz			0678	70	xxx	yyy	zzz
3	245	70	xxx	yyy	zzz			0245	70	xxx	yyy	zzz
4	365	70	xxx	yyy	zzz			0365	70	xxx	yyy	zzz
5	213	70	xxx	yyy	zzz			0213	70	xxx	yyy	zzz
Output

Thanks in advance for help.

Thanks
mg

 

[jasonb75]

If it is always 4 digits then just apply a custom format of 0000 to column A

As an alternative, have you tried filtering column B then copy visible cells and paste?

 

[Mallesh23]

Hi Jason,

Thanks for your help,
I have converted output sheets column A into text. and pasted collection it worked. both trick worked.

sht_output.Columns(1).NumberFormat = "@"

Thanks
mg