VBA Code to extract number and paste

[Wafee]

Hi,
I am trying to paste data from A,B columns from sheet1 to Aand B sheet2. For the A column , while pasting data I want to paste only numbers from each cells of sheet1. And for B column while pasting i want to remove first 10 letters from left and the paste it. Can someone help me with a code which works faster and dynamic as I am dealing with large amounts of data. Thanks in advance.

 

[Fluff]

How about

Sub Wafee()
   Dim Ary As Variant
   Dim i As Long
   With Sheets("Sheet1")
      Ary = Range("A1:B" & .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 1 To UBound(Ary)
      If Not IsNumeric(Ary(i, 1)) Then Ary(i, 1) = ""
      Ary(i, 2) = Mid(Ary(i, 2), 11)
   Next i
   Sheets("Sheet2").Range("A1").Resize(UBound(Ary), 2).Value = Ary
End Sub

 

[Wafee]

How about

Sub Wafee()
   Dim Ary As Variant
   Dim i As Long
   With Sheets("Sheet1")
      Ary = Range("A1:B" & .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 1 To UBound(Ary)
      If Not IsNumeric(Ary(i, 1)) Then Ary(i, 1) = ""
      Ary(i, 2) = Mid(Ary(i, 2), 11)
   Next i
   Sheets("Sheet2").Range("A1").Resize(UBound(Ary), 2).Value = Ary
End Sub

Hey Fluff.
Hope you are doing good. I tried but getting an error at
Ary = Range("A1:B" & .Range("A" & Rows.Count).End(xlUp)).Value2
Error- Method 'Range' of object '_Global' failed.
Thank you in advance.

 

[Fluff]

Oops, it should be

Ary = Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2

 

[Wafee]

Oops, it should be

Ary = Range("A1:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2

Hey Fulff,

For "A" column what I meant was to extract number from the string which will have both text and numbers. The format will have number followed by text (eg : 12-Matt).

 

[Fluff]

In that case can you please post some sample data using the XL2BB add-in.

 

[Wafee]

Hi Fluff,

Below will be the format. Numbers ranges from 1 to 12 not more than that. String initially starts with a number and the a "-" and the the text. First Column will be source and second one has the required out put.

I also has small addition to the requirement. In C column of sheet2 I need their categories based on numbers which is 1-4 should be under cateogory "A", 5-8 should be under "B", 9-12 Should be under "C"

Column A(Sheet1)	Column A(Sheet2)	Column C(Sheet2)
7 - Jack	7	B
4 - Raj	4	A
12 - Mike	12	C
11 - Susan	11	C

 

[Fluff]

Please post as sample of your data using the XL2BB add-in, not some fictitious pseudo data pasted to the board.

 

[Wafee]

Please post as sample of your data using the XL2BB add-in, not some fictitious pseudo data pasted to the board.

Hi Fluff, Sorry but I am not able to install XL2BB on my device for some reasons I don't know.

 

[Fluff]

Ok, but I still need a sample of your actual data.