Run time error 1004 while running recorded macro

Manasa · Apr 10, 2014

Hi ppl,

I am doing some data cleaning and I have multiple columns, each with a unique formulas. I recorded each column's execution as separate macros.

However, when I try to run this particular macro it throws a run-time error.All I have is nested SUBSTITUTE functions.

Can somebody help with it as Im a vba noob and cant figure out whats wrong.

Code:

    ActiveCell.FormulaR1C1 = _
        "=IFERROR(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1],""A"",""X""),""AU"",""B""),""LVR"",""CB""),""KKK"","Y""),""YY"",""""),""PO"","""",1),""!"",""""),""B"",""""),""" & _
        "),""PEEE"",""O"),""QP"",""""),""pe"",""90""),""IO"",""L""),""-"",""""),""VVV",""""),""TTT"",""""),""111"",""""),""222"",""X""),""123"",""""),""#3(9670)"",""""),""T"",""""),RC[-1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B40000")
    Range("B2:B40000").Select
End Sub

Thanks,

Manasa
'

NARAYANK991 · Apr 10, 2014

Hi ,

Try this :

"=IFERROR(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1],""A"",""X""),""AU"",""B""),""LVR"",""CB""),""KKK"",""Y""),""YY"",""""),""PO"","""",1),""!"",""""),""B"",""""),""C"",""""),""PEEE"",""O""),""QP"",""""),""pe"",""90""),""IO"",""L""),""-"",""""),""VVV"",""""),""TTT"",""""),""111"",""""),""222"",""X""),""123"",""""),""#3(9670)"",""""),""T"",""""),RC[-1])"

What you have is :

"=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1],""A"",""X""),""AU"",""B""),""LVR"",""CB""),""KKK"","Y""),""YY"",""""),""PO"","""",1)
,""!"",""""),""B"","""")
,""" & _
")
,""PEEE"",""O"),""QP"",""""),""pe"",""90""),""IO"",""L""),""-"","""")
,""VVV",""""),""TTT"",""""),""111"",""""),""222"",""X""),""123"",""""),""#3(9670)"",""""),""T"",""""),RC[-1])"

The portion highlighted in the upper formula is my own introduction ; you will need to change this to whatever is the correct substitution.

The portions highlighted in the lower formula are the mistakes ; the number of double quotes is wrong , and there is no substitution in one portion.

Narayan

Manasa · Apr 10, 2014

Oh my I don't know how that could happen. The formula was pretty good when I ran it. Thanks Narayan

I now have another issue with an Array formula that I recorded.

Code:

Range("P2").Select
    Selection.FormulaArray = _
        "=IF(SUM(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],{1,2,3,4,5,6,7,8,9,0},"""")))=8,SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(RC[-1],MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),RC[-1],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-1]&""0123456789"",1))),1000))),1000-COLUMN(R2),1)%,""SNNA""))),9),REPT(""0"",8)),""0000000"",""pN Ns""),SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(RC[-1],MIN(IFERROR(SEARCH(CHAR(ROW(R65:R90)),RC[-1],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-1]&""0123456789"",1))),1000))),1000-COLUMN(R2),1)%,""SNNA""))),9),REPT(""0"",7)),""0000000"",""pN Ns""))"
    Selection.AutoFill Destination:=Range("P2:P40000")
    Range("P2:P40000").Select
End Sub

I get an error saying " Unable to set formulaArray property of the range class.

NARAYANK991 · Apr 10, 2014

Hi ,

If you see the Excel help on the FormulaArray property , this is what it says :

The FormulaArray property also has a character limit of 255.

The formula string you have posted is exceeding this limit.

To overcome this limitation , what you need to do is split up the formula string into 2 or more parts , of which the main part should result in a valid formula , and adding each of the other parts to this main part should also result in valid formula ; thus you build up your formula instead of presenting it in one go.

I suggest you go through this link , which gives a lot of other information as well.

http://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

If you still have difficulty in resolving this problem , please post back.

Narayan

SirJB7 · Apr 10, 2014

Hi, Manasa!

In this site you can find a today analogue issue:
http://chandoo.org/forum/threads/vba-long-array-formula.15957/#post-96496

Regards!

Manasa · Apr 11, 2014

Thanks for the links guys, I tried attempting the replace idea as suggested in this forum. I am still failing as I am unable to split it into two valid formulas. I would appreciate if you can help me with the code. I'm still an excel beginner.

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Thanks,
M

NARAYANK991 · Apr 11, 2014

Hi ,

Splitting this formula is somewhat difficult ; if you can explain what you wish to do with this , there may be a better way to do it.

Narayan

Manasa · Apr 11, 2014

Sure Narayan. Here you go...

=IF(SUM(LEN(V2)-LEN(SUBSTITUTE(V2,{1,2,3,4,5,6,7,8,9,0},"")))=8

I check if the length of numbers in the alphanumeric string equals 8.

If yes, I extract all the 8 numbers

SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(V2,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),V2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},V2&"0123456789",1))),1000))),1000-COLUMN($2:$2),1)%,"SNNA"))),9),REPT("0",8)),"0000000","pN Ns")

If not 8, I extract the numbers and append leading 0's to make the length 7 digits. If its already seven digits then no issues.

SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(V2,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),V2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},V2&"0123456789",1))),1000))),1000-COLUMN($2:$2),1)%,"SNNA"))),9),REPT("0",7)),"0000000","SN NA"))

So finally my results should be either 8 or 7 digit numbers.

Thanks,

M

NARAYANK991 · Apr 11, 2014

Hi ,

I did not want an explanation of what the formula is doing as far as digits are concerned ; it is the substitutions that need explanation ; what exactly is being done by all the substitute functions ?

Can you give an example of an input string ( more than one would be nice ) and what should be the output ?

You may be aware that regular expressions can be used to extract / substitute patterns from strings ; what you are doing with a 500 character formula can probably be done using a 20 character regular expression.

Narayan

Manasa · Apr 11, 2014

Hi Narayan,

Sorry for that. My bad. I've heard of regular expressions in the forum before but have not used it so far.

Also,I would like to remind you that it was you who helped me with this formula a few weeks ago when I had to do this manually. Now, I am asked to automate it hence this confusion.

I got this formula from this post
http://forum.chandoo.org/threads/extract-number-from-text-special-character.15290/#post-91883

And here is the input:

90870760-XS2678
BB123830
9009
Number not present
NNA
BB-10-24-900
<blank>
lc1319038

Thanks,
M

NARAYANK991 · Apr 11, 2014

Hi ,

There is some confusion here ; given your input strings , what should be the output ?

Let us reproduce the formula here :

"=IF(SUM(LEN(RC[-1])-LEN(SUBSTITUTE(RC[-1],{1,2,3,4,5,6,7,8,9,0},"""")))=8,
SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(RC[-1],MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),RC[-1],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-1]&""0123456789"",1))),1000))),1000-COLUMN(R2),1)%,""SNNA""))),9),REPT(""0"",8)),""0000000"",""pN Ns""),
SUBSTITUTE(TEXT(LEFT(INT(NPV(-0.9,,IFERROR(MID(LEFT(RC[-1],MIN(IFERROR(SEARCH(CHAR(ROW(R65:R90)),RC[-1],MIN(FIND({0,1,2,3,4,5,6,7,8,9},RC[-1]&""0123456789"",1))),1000))),1000-COLUMN(R2),1)%,""SNNA""))),9),REPT(""0"",7)),""0000000"",""pN Ns""))"

Note that the portion in RED is almost identical to the portion in GREEN ; the only difference being that the RED portion has REPT(""0"",8) while the GREEN portion has REPT(""0"",7).

What exactly is going on here ?

What are the substitutions SNNA and pN Ns for ?

Narayan

Manasa · Apr 11, 2014

Hey,

Input

90870760-XS2678---------------------90870760
BB123830------------------------------ 0123830
9009------------------------------------0009009
Number not present--------------------pN Ns
NNA-------------------------------------pN Ns
BB-10-24-900--------------------------1024900
<blank>---------------------------------pN Ns
lc1319038------------------------------1319038

The red portion is when the if condition is TRUE( ie when there are 8 numbers in the string) and the green portion is when I have less than 8. So I will extract the numbers to make them 7 digits in total.

pN Ns is when I have blanks/ all text in my input. SNNA is pretty much a dummy one. Its as good as "".

Thanks,
M

NARAYANK991 · Apr 11, 2014

Hi ,

Can we specify the rules as follows ?

1. If the cell is blank , return pN Ns

2. If the cell contains no digits , return pN Ns

3. If the cell contains digits , return the first sequence of digits ; a sequence of digits starts when a digit is encountered , and ends when an alphabet is encountered. Any other special characters which may occur in between , such as hyphens , can be ignored.

4. When returning the first sequence of digits , if the number of digits is 8 or more , return the left-most 8 digits ; if the number has less than 8 digits , return 7 digits left-padded with 0s ; thus a 4-digit number such as 9009 will be returned as 0009009.

Narayan

Manasa · Apr 11, 2014

You got it right..

-M

NARAYANK991 · Apr 11, 2014

Hi ,

Thanks for the confirmation. However , since it's late at night for me , I can respond only 8 to 10 hours later.

Let us hope someone else can step in. Otherwise , please check back 8 to 10 hours later.

Narayan

Manasa · Apr 11, 2014

No issues Narayan.. Take your time

Thank you so much. Gn !

rumshar · Apr 11, 2014

Hi you may modify this to meet your requirement.

Code:

Function NumberOnly(Entry)
    For i = 1 To Len(Entry)
        ThisChar = Mid(Entry, i, 1)
        Select Case Asc(ThisChar)
            Case 48, 49, 50, 51, 52, 53, 54, 55, 56, 57
                NumberOnly = NumberOnly & ThisChar
        End Select
    Next i
End Function

With Regards
Rudra

SirJB7 · Apr 11, 2014

@rumshar
Hi!
You can replace the Case line by this:

Code:

            Case 48 To 57

Regards!

NARAYANK991 · Apr 12, 2014

Hi Manasa ,

See this file.

The code , since Marcus had earlier suggested that we post the code also , is :

Code:

Public Function Extract_Digits(ByRef cell As Range) As String
                Const ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
                Const DIGITS = "0123456789"
                Const NUMDIGITS = 7
                Const DOT = "."
                Const IFBLANK = "pN Ns"
               
                Dim data_array As Variant, out_array As Variant
                Dim x As String, y As String, ch As String
                Dim i As Integer, j As Integer
                Dim within_number As Boolean
               
                With Application.Caller
                    x = Val(.Rows.Count)
                    y = Val(.Columns.Count)
                End With
               
                If x = 1 Or y = 1 Then
                  number_of_items = 1
                Else
                  number_of_items = Application.WorksheetFunction.Max(x, y)
                End If
               
               
                If number_of_items = 1 Then
                      x = cell.Value
                      y = vbNullString
                      For j = 1 To Len(x)
                          ch = Mid(x, j, 1)
                          If ((InStr(1, ALPHABET, UCase(ch), vbTextCompare) > 0) Or (InStr(1, DIGITS, UCase(ch), vbTextCompare) > 0) Or (ch = DOT)) Then
                              y = y & ch
                          End If
                      Next
                     
                      x = vbNullString
                      within_number = False
                      For j = 1 To Len(y)
                          ch = Mid(y, j, 1)
                          If (InStr(1, DIGITS, UCase(ch), vbTextCompare) > 0) Then within_number = True
                          If within_number Then
                              x = x & ch
                          ElseIf x <> vbNullString Then
                              If ch <> DOT Then Exit For
                          End If
                      Next
                     
                      j = IIf(InStr(1, x, DOT) > 0, NUMDIGITS + 1, NUMDIGITS)
                     
                      If Len(x) > j Then
                          x = Left(x, j + 1)
                      Else
                          x = Left(String(j - Len(x), "0") & x, j)
                      End If
                     
                      If x = String(j, "0") Then x = IFBLANK
                End If
                Extract_Digits = x
End Function

Narayan

Manasa · Apr 13, 2014

Thank you guys. Narayan thanks for the code

Regards,
M

rumshar · Apr 14, 2014

SirJB7 said:
@rumshar
Hi!
You can replace the Case line by this:

Code:

Case 48 To 57

Regards!

Thank you SirJayBeeSeven.

Run time error 1004 while running recorded macro

New Member

Excel Ninja

New Member

Excel Ninja

Excel Rōnin

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Member

Excel Rōnin

Excel Ninja

Attachments

New Member

Member