vba - text to Number issue

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
840
Office Version
  1. 2010
Platform
  1. Windows
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.


VBA Code:
[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

VBA Code:
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
ABCDE
1Transaction NOCustomer groupColxcolyColz
2026560xxxyyyzzz
3067870xxxyyyzzz
4024570xxxyyyzzz
5036570xxxyyyzzz
6021370xxxyyyzzz
7041460xxxyyyzzz
Data


Output Via Macro --- and expected Output
Book2
ABCDEFGHIJKL
1Transaction NOCustomer groupColxcolyColzEXPECTEDTransaction NOCustomer groupColxcolyColz
267870xxxyyyzzz067870xxxyyyzzz
324570xxxyyyzzz024570xxxyyyzzz
436570xxxyyyzzz036570xxxyyyzzz
521370xxxyyyzzz021370xxxyyyzzz
Output



Thanks in advance for help.

Thanks
mg
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,959
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
840
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,848
Messages
5,598,435
Members
414,238
Latest member
juxion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top