enter UDF cell and drag down

elsg

Active Member
Joined
Mar 16, 2013
Messages
295
Hellow
I have a user defined function.


I'm trying to put it inside another function (native excel), and then drag it down, but it did not work.
Code:
Sub Exp_I()
      Range("D2").Formula = "=LEFT(ExtractNumber(C2);6)"
    Selection.AutoFill Destination:=Range("D2:D2000"), Type:=xlFillDefault
End Sub
Code:
Sub Exp_II()
      Range("D2").FormulaR1C1 = "=LEFT(ExtractNumber(RC[-1]);6)"
    Selection.AutoFill Destination:=Range("D2:D2000"), Type:=xlFillDefault

End Sub
I tried the 2 examples above but did not work

Code:
Function ExtractNumber(rCell As Range)
'Separa número de texto
    Dim iCount As Integer, i As Integer
    Dim sText As String
    Dim lNum As String
    'Written by OzGrid Business Applications
    '[url=http://www.ozgrid.com]Excel Templates | Excel Add-ins and Excel Help with formulas and VBA Macros[/url]
    'Extracts a number from a cell containing text And numbers.
    sText = rCell
     
    For iCount = Len(sText) To 1 Step -1
        If IsNumeric(Mid(sText, iCount, 1)) Then
            i = i + 1
            lNum = Mid(sText, iCount, 1) & lNum
        End If
         
        If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
    Next iCount
    
    ExtractNumber = CLng(lNum)
End Function
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Hi

You need to use commas as argument separators when applying the Formula or FormulaR1C1 property:

Code:
Range("D2").Formula = "=LEFT(ExtractNumber(C2),6)"

Note: This is irrespective of whatever regional settings you have applied in your Operating System
 

elsg

Active Member
Joined
Mar 16, 2013
Messages
295
Good!!
Yes, that's it, I forgot that detail.


But now, when trying to run the entire routine is generating error.
Run time error '1004'
AutoFill method of Range class failed
Code:
Sub Teste()     Worksheets("Tarefas").Activate
    Cells.WrapText = False
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("D:D").Insert shift:=xlRight
    Range("D2").FormulaR1C1 = "=LEFT(ExtractNumber(RC[-1]),6)"
    Selection.AutoFill Destination:=Range("D2:D2000"), Type:=xlFillDefault 'select the fill range'<- This row yellow
End Sub
way?
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
change:

Selection.AutoFill Destination:=Range("D2:D2000"), Type:=xlFillDefault

to:
Range("D2").AutoFill Destination:=Range("D2:D2000"), Type:=xlFillDefault
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638

ADVERTISEMENT

Try this:

Code:
Range("D2").AutoFill Destination:=Range("D2:D2000"), Type:=xlFillDefault
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

thank you very much!!!!

very good!!
Actually, you do not need to use AutoFill at all... simply assign the formula directly to all the cells at the same time.
Code:
Sub Teste()
    Worksheets("Tarefas").Activate
    Cells.WrapText = False
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("D:D").Insert shift:=xlRight
    Range("D2:D2000").FormulaR1C1 = "=LEFT(ExtractNumber(RC[-1]),6)"
End Sub
Also, I thought you might like to see this alternate (shorter) ExtractNumber function which does the same thing as the one you posted.
Code:
Function ExtractNumber(rCell As Range)
  Dim X As Long, Text As String
  Text = rCell.Value
  For X = 1 To Len(Text)
    If Not Mid(Text, X, 1) Like "#" Then Mid(Text, X, 1) = " "
  Next
  ExtractNumber = CLng(Replace(Text, " ", ""))
End Function
 

elsg

Active Member
Joined
Mar 16, 2013
Messages
295
hi Rick Rothstein
So...both codes
Because the code extracted some numbers and not others?
CódigoTítuloFormula
205036Pedido » Carta de Correção#VALUE!
204992AFE, CRT, ALVARA SANITARIO#VALUE!
204457NF:149757-3 - Enc: RES: devolução - 14888 - devolução fora do Prazo.#VALUE!
204455NF:149757-3 - Enc: RES: devolução - 14888 - devolução fora do Prazo.#VALUE!
203986NF:159149-3 - devolução total da nota n-159149 drogaria Barbosa e monteiro#VALUE!
203746NF:157007-3 - 312842 DROG PERFEITA LTDA DEVOLUÇÂO#VALUE!
203549NF:156924-3 - DEVOLUÇÃO TOTAL DA NOTA N--156924 FCIA CAMPI COD-306407#VALUE!
203148Pedido » Devolução » Total » Duplicidade do Pedido#VALUE!
203147Pedido » Devolução » Total » Duplicidade do Pedido#VALUE!
202965NF:155054-3/NF:156726-3 - devolução#VALUE!
202892NF:154825-3 - DEVOLUÇÃO TOTAL NF 154825#VALUE!
202891NF:154825-3 - DEVOLUÇÃO TOTAL NF 154825#VALUE!
202683Reclamações » Empresa#VALUE!
202286NF:152744-3 - FW: devolu ção parcial 25-04-2 013#VALUE!
202281Pedido » Devolução » Parcial » Faturamento Indevido Televendas#VALUE!
202267NF:154733-3 - Devolução parcial do cliente: 312740 - JUNIOR SOBRAL#VALUE!
201624NF:152829-3 - 312857 J A TOZETTI E CIA LTDA DEVOLUÇÃO#VALUE!
201438Reclamações » Empresa#VALUE!
201265Pedido » Devolução » Parcial » Erro do Cliente#VALUE!
201114Pedido » Devolução » Parcial » Faturamento Indevido Televendas#VALUE!
201100Pedido » Falta de Medicamentos#VALUE!
201017Reclamações » Empresa#VALUE!
200962Pedido » Devolução » Parcial » Temperatura Inadequada#VALUE!
200936NF:153520-3 - devolução parcial 23/04/2013#VALUE!
200916NF:151563-3 - DEVOLUÇÃO PARCIAL DA NF Nº151563#VALUE!
200825NF:146463-3/NF:146413-3 - Ocorrência de Devolução#VALUE!
200769NF:148012-3 - 332252 M V MACHADO MEF FATURADO NÃO ENVIADO#VALUE!
200761NF:148911-3 - Registro devolução NF 148911#VALUE!
200751NF:145956-3 - devolução parcial M.Marconi( 20840)#VALUE!
200741NF:148887-3 - FW: DROGARIA MATRIZ LTDA -10.306.460/0001-04#VALUE!
200671NF:147608-3 - DEVOLUÇAÕ TOTAL DA NOTA N-147608 DROG M R DE ABREU#VALUE!
200669NF:147608-3 - DEVOLUÇAÕ TOTAL DA NOTA N-147608 DROG M R DE ABREU#VALUE!
200219Pedido » Falta de Medicamentos#VALUE!
199691NF:145294-3 - devolucao parcial ref o nota 145294#VALUE!
199603Pedido » Devolução » Parcial » Desistência do Cliente#VALUE!
199586NF:142477-3 - DEVOLUCAO PARCIAL REF. NT 142477 DE 15.04.13#VALUE!
199527NF:146416-3 - DEVOLUÇÃO PARCIAL RF NF:146416#VALUE!
199515NF:118216-3 - ENC: Ocorrência Panarello filial 33 Rio das Ostras nf:118216Recebido em:17/4/2013 17:20:35#VALUE!
199464NF:141349-3 - Devolução nota 141349#VALUE!
198950Pedido » Falta de Medicamentos#VALUE!
198493NF:094649-3/NF:094650-3 - Pedido » Devolução » Total » Mercadoria Extraviada#VALUE!

<colgroup><col><col><col></colgroup><tbody>
</tbody>














































































Extract
CódigoTítuloFormula
205169162431 Pedido » Devolução » Parcial » Qtd. Maior que Faturada - Sobra162431
205129NF:164546 Pedido » Devolução » Parcial » Erro do Cliente164546
205096NF:163637 Pedido » Devolução » Parcial » Vencimento Próximo163637
205025NF:160998-3 - devolucao integral160998
205024NF:160998-3 - devolucao integral160998
205019NF:162552-3 Pedido » Falta de Medicamentos162552
205018NF:162273-3 - DEVOLUÇÃO PARCIAL162273
205012NF:163548-3 - Pedido » Devolução » Total » Erro do Estoque163548
205011NF:163548-3 - Pedido » Devolução » Total » Erro do Estoque163548
205002NF:162707-3 - DEVOLUCAO162707
205001NF:162707-3 - DEVOLUCAO162707
204999NF:162163-3 - FALTA DE MEDICAMENTO162163
204995NF:161806-3 - FALTA DE MERCADORIA161806
204991NF:158814-3 - Sobra de Medicamento158814
204972NF:160954-3 pedido» Devolução » Total » Faturamento Indevido Indústria160954
204969NF:160954-3 pedido» Devolução » Total » Faturamento Indevido Indústria160954
204910NF:163686 Pedido » Falta de Medicamentos163686
204879NF:163623 Pedido » Devolução » Parcial » Erro do Cliente163623
204842NF:163354 Pedido » Falta de Medicamentos163354
204783NF:162700-3 Pedido » Devolução » Parcial » Faturamento Indevido Televendas162700
204775NF:161789 Pedido » Carta de Correção161789

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
The reason for the #VALUE! errors is because the original ExtractNumbers function (and I duplicated it in my function) converts the returned number to a Long (maximum value - 2,147,483,647)... the numbers for the failed cells are all bigger than 2147483647. I would also note that some of those numbers are too big to display fully, as real numbers, within a cell. The only thing I can think of is to convert the numbers to text strings before putting them into the cells, then the entire number (no matter how long it is) will display in the cell, but that value with be a Text value, not a numerical value. Here is my code modified to do that...
Code:
Function ExtractNumber(rCell As Range)
  Dim X As Long, Text As String
  Text = rCell.Value
  For X = 1 To Len(Text)
    If Not Mid(Text, X, 1) Like "#" Then Mid(Text, X, 1) = " "
  Next
  ExtractNumber = CStr(Replace(Text, " ", ""))
End Function
 

elsg

Active Member
Joined
Mar 16, 2013
Messages
295
was perfect!!!!!!!!!
amazing!!!!!!!!!
thank you very much!!!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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