Run time Error 1004 :-Unable to set the formulaarray property of the range class

C

chirag050675

Board Regular
Joined
Sep 3, 2016
Messages
69
Dear Sir,

Please help when I try to insert array formula via VBA,


Worksheets("Sheet1").range("B2:D3").FormulaArray = "=vlookup(a2,'[wbsource]',!{2,3,4},false)"


error face below

"RUN TIME ERROR: 1004 UNABLE TO SET THE FORMULA ARRAY PROPERTY OF RANGE CLASS"

this for I try for Vlookup via closed workbook -fetch result in in cell B2 , but I want result next 3 columns also, C2,D2, & E2 as lightening speed (please note:-VBA can not send keys (CTRL+SHIFT+ENTER) so I have this one way ..
if I got this 3 cells, then I can fill down till end of data in A1..& then I remove all links from closed workbook to this current active...running... workbook..

Hope your Co-Operation.
 
Sort by date Sort by votes
Re: 100% SOLVED- VLOOKUP FROM CLOSED WORKBOOK-& RESULT IN 3 COLUMNS IN ONE SHOT -Re: Run time Error 1004 :-Unable to set the formula array propertyof the reange class-

Even though the calculation is set to "Manual", the Calculate method of the Worksheet object should calculate the formulas on the worksheet. And nothing within the code should cause black ants to appear around B2:D2. Can you post the exact code that you're using?

Also, just to be clear, do you want the code to be changed so that the results are entered in which ever sheet is the active sheet as apposed to specifying a sheet?
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Dear sir,
I just copy paste your code in new module..its work...ok...but ..some time ..not......stuck on black ant...
Black ants means suppose when we copy & paste something that time animated black - - - type line display that inform that just now waiting for paste.......
Its okay if you can give me changed code
Because its better then run only "sheet 1"
Hope your co-operation
Regards,
Chirag
 
Upvote 0
The method used in the code to copy the formulas down the columns should not result in the "running black ants". When you run the code, are you getting an error message? If so, what does the message say? And which line causes the error?

In the meantime, I've changed the code so that it uses the active sheet to return the results...

Code: 
[COLOR=green]'Force explicit declaration of variables[/COLOR]
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] copytestofvlclosedfile()

   [COLOR=green]'Declare variables[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sSheet [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sRef [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFullName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] wbSource [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] wksDest [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] bWorkbookOpened [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    [COLOR=green]'Turn off screen updating[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=green]'Turn on error handling[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] ErrHandler
    
    [COLOR=green]'If no worksheet is active, exit sub[/COLOR]
    [COLOR=darkblue]If[/COLOR] TypeName(ActiveSheet) <> "Worksheet" [COLOR=darkblue]Then[/COLOR]
        MsgBox "No worksheet is active.!", vbExclamation
        [COLOR=darkblue]GoTo[/COLOR] ExitSub
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'Assign the active worksheet to a variable[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wksDest = ActiveSheet
    
    [COLOR=green]'Specify path to source file (change accordingly)[/COLOR]
    sPath = "C:\Users\sganuja\Desktop\"
    
    [COLOR=green]'Check if path exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(Dir(sPath, vbDirectory)) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Path does not exist.", vbInformation
        [COLOR=darkblue]GoTo[/COLOR] ExitSub
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=green]'Make sure path ends in back slash[/COLOR]
        [COLOR=darkblue]If[/COLOR] Right(sPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR]
            sPath = sPath & "\"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'Specify source file (change accordingly)[/COLOR]
    sFile = "BUYERLIST.xlsx"
    
    sSheet = "Sheet1"
    
    sRef = "$A$1:$E$383"
    
    [COLOR=green]'Specify path and source file[/COLOR]
    sFullName = sPath & sFile
    
    [COLOR=green]'Check if workbook exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(Dir(sFullName, vbNormal)) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Workbook does not exist.", vbInformation
        [COLOR=darkblue]GoTo[/COLOR] ExitSub
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=green]'Open specified workbook as read only[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbSource = Workbooks.Open(Filename:=sFullName, ReadOnly:=True)
    bWorkbookOpened = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=green]'Do stuff[/COLOR]
   
    'Enter lookup formula and convert to values
    [COLOR=darkblue]With[/COLOR] wksDest
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        [COLOR=darkblue]If[/COLOR] LastRow >= 2 [COLOR=darkblue]Then[/COLOR]
            .Range("B2:D2").FormulaArray = "=vlookup(a2," & wbSource.Worksheets(sSheet).Range(sRef).Address(, , , [COLOR=darkblue]True[/COLOR]) & ",{2,3,4},false)"
            .Range("B2:D2").Copy .Range("B3:D" & LastRow)
            .Calculate
            [COLOR=darkblue]With[/COLOR] .Range("B2:D" & LastRow)
                .Value = .Value
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Else[/COLOR]
            MsgBox "No data is available!", vbExclamation
            [COLOR=darkblue]GoTo[/COLOR] ExitSub
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
ExitSub:
    [COLOR=green]'If source workbook was opened, close without saving[/COLOR]
    [COLOR=darkblue]If[/COLOR] bWorkbookOpened [COLOR=darkblue]Then[/COLOR]
        wbSource.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'Turn screen updating back on[/COLOR]
    Application.ScreenUpdating = True
    
    [COLOR=green]'Clear from memory[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbSource = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbDest = [COLOR=darkblue]Nothing[/COLOR]
    
    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    [COLOR=darkblue]Resume[/COLOR] ExitSub
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Dear Domenic Sir,

That's great,
thank you very much.. its working without
any disturbance in any worksheet...like a charm.
I guess that your code is 100% complete &clean , nothing in that code that i see “black ants”
Some other code or process may be conflicts that time so i can not say
“black ant appear if your macro run”..
i want to forget that “Black Ant”..& request you to ignore that accident...for which your are not responsible..

I changed 'Set wbDest = Nothing' (that's not changed by mistake in this macro)
to Set wksDest = Nothing...(as variable name declared)

& run...amazingly.....

now I can study for learn this Macro deeply for co-relation of
each things....& try test each process in various aspects...
& try to apply many things from this macro to another process.


I can say now that our mission of
"Get 100% result of Vlookup in many columns from closed file in one shot via VBA"
is complete on this time...
I really amazed ..We can do many more things via VBA to improve our productivity.

I will be always thankful to your support & spending spend valuable time for me...

I also always ready to give this method to others for help them in their work.. & also try to
they take interest in VbA ..
If in future, if i need any help from other VBA stuff ,can i hope that time small help from your side?

Regards,
Chirag
 
Upvote 0
Dear Sir,

I want to run more that 1 sub in one module
so I go to ' http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html'
there are I found 1 excel file contain 3 types of process for fetch data from website
& duping in excel file ...& its work like a charm. I also seen its VBA code
it have public sub...& public function...

it is possible to run more that 1 sub in 1 module???
I can not understand that file ...
I request you to please study that file...& please inform if your
found something important if we can run more that 1 sub in one module...

Regards

Chirag
 
Upvote 0
Dear Domenic sir,

sorry ...I start new thread as run more then 1 sub in 1 module.

pardon me if I make a mistake or hurt you.

I just want to learn...

regards,

Chirag
 
Upvote 0
Chirag,

That's great. I'm glad I was able to help. And it's nice to see that you're very enthusiastic about VBA and that you're interested in learning more. You'll find that this forum is a great place to ask questions and to learn from others. There are a lot people here who are very knowledgeable, and who are willing to help others.

Cheers!

P.S. Yes, it's always best to start a new thread whenever you have a new question. ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
FormulaArray Run-time error 1004 unable to set the FormulaArray property of the Range class
Replies
1
Views
199
FreddieITA91
F
L
  • Question Question
Run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class
Replies
5
Views
1K
dreid1011
dreid1011
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
392
Jiraya_00
J
KlausW
  • Solved
Excel VBA Error - Unable to get the Insert property of the Picture Class
Replies
7
Views
433
KlausW
KlausW
I
  • Question Question
Error "Unable to set the Name property of the PivotTable class"
Replies
1
Views
284
RoryA
RoryA

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top