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

[chirag050675]

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.

 

[Domenic]

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?

 

[chirag050675]

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

 

[Domenic]

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...

[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]

 

[chirag050675]

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

 

[chirag050675]

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

 

[chirag050675]

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

 

[Domenic]

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.