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

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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Re: Run time Error 1004 :-Unable to set the formula array propertyof the reange class

Try something like this...

Code:
Dim sFile As String
Dim sSheet As String
Dim sRef As String


sFile = "Book1.xlsm"
sSheet = "Sheet1"
sRef = "$A$1:$D$100"


Worksheets("Sheet1").Range("B2:D2").FormulaArray = "=vlookup(a2,'[" & sFile & "]" & sSheet & "'!" & sRef & ",{2,3,4},false)"

Notice that the formula is entered in B2:D2, not B2:D3. Once entered in B2:D2, you can then copy the formula to your other cells.

Also, just in case you are not already aware of it, FormulaArray has a character limit of 255.

Hope this helps!
 
Upvote 0
Re: Run time Error 1004 :-Unable to set the formula array propertyof the reange class

Dear sir,

thanks for your guide line its working...but in only your sub procedure if just follow as per your code...

(BUT EVERY TIME -run your code, file open dialog box open & i must lead VBA to my source file for update link...that I don't want.. because ...there are no meaning to vlookup from closed workbook... if you must involve for open to close process of file...


In my below sub--its face error 1004 for range reference...

I change all aspect in my code as per your guideline
I also put just file variable in [sfile] , I also setup path to cell without []

I guess that there are some miracle in [FILENAME] .
.If you put path to cell reference in vlookup via variable or literal path (drive to cell)....its not working but , if you only give just closed file name in [filename] without variable & extra things, its amazingly working... so I thing what is miracle in [ file]??? (but with file update link process)

anyway... as per below my sub...its face error...


Rich (BB code):
'Force explicit declaration of variables
Option Explicit
Sub copytestofvlclosedfile()
   'Declare variables
    Dim sPath As String
    Dim sFile As String
    Dim sSheet As String
    Dim sRef As String
    Dim sFullName As String
    Dim wbSource As Workbook
    Dim bWorkbookOpened As Boolean
    
    'Specify path to source file (change accordingly)
    sPath = "C:\Users\sganuja\Desktop\"
    
    'Check if path exists
    If Len(Dir(sPath, vbDirectory)) = 0 Then
        MsgBox "Path does not exist.", vbInformation
        Exit Sub
    Else
        'Make sure path ends in back slash
        If Right(sPath, 1) <> "\" Then
            sPath = sPath & "\"
        End If
    End If
    
    'Specify source file (change accordingly)
    sFile = "BUYERLIST.xlsx"
    
    sSheet = "Sheet1"
    
    sRef = "$A$1:$E$383"

    
    'Specify path and source file
    sFullName = sPath & sFile
    
    'Check if workbook exists
    If Len(Dir(sFullName, vbNormal)) = 0 Then
        MsgBox "Workbook does not exist.", vbInformation
        Exit Sub
    End If
        
    'Turn off screen updating
    Application.ScreenUpdating = False
    
    'Turn on error handling
    On Error GoTo ErrHandler
    
    'Open specified workbook as read only
    Set wbSource = Workbooks.Open(filename:=sFullName, ReadOnly:=True)
    bWorkbookOpened = True
    
    'Do stuff
   
Worksheets("Sheet1").range("B2:D2").FormulaArray = "=vlookup(a2,'" & sFullName & " & sSheet & " & sRef & ",{2,3,4},false)"
    '
    
ExitSub:
    'If source workbook was opened, close without saving
    If bWorkbookOpened Then
        wbSource.Close SaveChanges:=False
    End If
    'Turn screen updating back on
    Application.ScreenUpdating = True
    
    'Clear from memory
    Set wbSource = Nothing
    
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    Resume ExitSub
    
End Sub

you are requested to guide me in my above code to solve my problem,,

hope-your co-operation.

regards,

Chirag
 
Upvote 0
Re: Run time Error 1004 :-Unable to set the formula array propertyof the reange class

If you were only going to need to enter the formula, there would be no need to open the source workbook, and you would use the following line of code...

Code:
Worksheets("Sheet1").Range("B2:D2").FormulaArray = "=vlookup(a2,'" & sPath & "[" & sFile & "]" & sSheet & "'!" & sRef & ",{2,3,4},false)"

Otherwise, if you need to opened the workbook because you'll need to do other things, you would use the following line of code...

Code:
Worksheets("Sheet1").Range("B2:D2").FormulaArray = "=vlookup(a2," & wbSource.Worksheets(sSheet).Range(sRef).Address(, , , True) & ",{2,3,4},false)"

However, since the source workbook will be open and will be the active workbook when the above line of code is executed, the reference to...

Code:
Worksheets("Sheet1").Range("B2:D2")

... will incorrectly refer to the source workbook. So I think you'll need to amend your code as follows...

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] wbDest [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] bWorkbookOpened [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    [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]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [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]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/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]'Assign the active workbook to a variable[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbDest = ActiveWorkbook [COLOR=green]'or ThisWorkbook[/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]
   
    wbDest.Worksheets("Sheet1").Range("B2:D2").FormulaArray = "=vlookup(a2," & wbSource.Worksheets(sSheet).Range(sRef).Address(, , , [COLOR=darkblue]True[/COLOR]) & ",{2,3,4},false)"
    
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]

And, one last thing. If this code will reside in the same workbook in which the formula will be entered, you should replace...

Code:
Set wbDest = ActiveWorkbook

wtih

Code:
Set wbDest = Thisworkbook

Hope this helps!
 
Upvote 0
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-

Dear Sir,

Amazing, ..you really ..amazingly solve my problem....
just copy paste your code in New Module...
I just test...put any buyer no in A2 ...Run..it
& lightening result flash in cell B2, C2 & D2...as an Vlookup Array formula via VBA.
1 polite request .. i will be thankfull again...if...get this result (b2,c2,d2) till end of A1 data...& made this result as .value (to remove formula ..just result stay) then i can sort by heading as require & get subtotal as require...
I hope for just 2 line code from you..

THIS IS FOR ALL READERS-If you wish vlookup result in any number of columns ..horizontally...
this is become Solid Rock method ..which run without fail..& without face any error.

FOR DOMINIC SIR also I request you to refer this method in world-wide..
who want this method. He or she .just search in web....
and they found this method as first search result..
in first search result page.

thank you very much ...for solve my problem forever
& also for teach is method to vlookup from closed file Via VBA

you are real "GURU"..who hard work for their students
without any personal intense...they work for others due to
just they want to give & help others .
as per above ...again 1 polite request .. i will be thankfull to you...if...get this result (b2,c2,d2) till end of A1 data...& made this result as .value (to remove formula ..just result stay) then i can sort by heading as require & get subtotal as require...
I hope for just 2 line code from you..
Regards,
Chirag
 
Upvote 0
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-

That's great. I'm glad I could help.

And thanks for your feedback, and very kind words. I really appreciate it.

In the following macro, I added code to copy the formula down the columns and convert them to values. In addition, I also made some minor changes with regards to error handling.

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] wbDest [COLOR=darkblue]As[/COLOR] Workbook
    [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]'Specify path to source file (change accordingly)[/COLOR]
    sPath = "c:\users\domenic\desktop\" [COLOR=green]'"C:\Users\sganuja\Desktop\"[/COLOR]
    
    [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 = "Book1.xlsm" [COLOR=green]'"BUYERLIST.xlsx"[/COLOR]
    
    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]'Assign the active workbook to a variable[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbDest = ActiveWorkbook [COLOR=green]'or ThisWorkbook[/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] wbDest.Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .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)
        [COLOR=darkblue]With[/COLOR] .Range("B2:D" & LastRow)
            .Value = .Value
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/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]

Hope this helps!
 
Upvote 0
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-

Dear Domenic sir,

this is working like a miracle...I can not believe that any machine
work like this if you give it right instructions....


people always hesitate & afraid by give instructions to machines what they want.
however they also try to do something minor...but ...first failure ...degrade themselves + machine also...
in their mind...& they established belief that I can't do that...only genius can do this work...
& ....... they put away this try...& don't want to know what machine want....
machine just want some necessary code only in right way...just it.....& it can work
for you like a miracle.

Errors is best friend of any coder that always instruct what is going wrong..
& what is require...& if we try to setup as per what machine want..miracle happened...


people who just work in excel...they mostly satisfy with just rows & columns
When they seen in worksheet...., menus & work area....their mind say “oh...how much this is??”
How can we handle this much matters??...their mind stop there..(there are no fault of Microsoft..
Microsoft hard work for people & they do their best try to reach at user’s ground with all their researches....& possible easy methods for how to take work from machines..& VBA is master key ..to fulfil requirements...

But surly there are also some people like you who is god gifted to world..that teach people how to handle matters of machine..they suddenly strike in their mind what to be done....& this is called God Gift..
That unfortunately not have with all people.

I am also normal excel users but my problem lead me to World Of VBA..
I also newer for invisible Variables that captured things & do their roll...in process...
I also notice that each small process take start & perfect end....& if you put clean start to clean end..
It definitely do they work....without any error...(like you put Dim bWorkbookOpened As Boolean & also
If bWorkbookOpened Then...) i also notice that each small process have their necessary supported
factors which make that process complete & clean....
I am newer but i have curiosity to learn like a child...that always help me to understand things...


Thanks again for your important guideline....if there are need, i reply you in this or another thread..
Regards,
Chirag
 
Upvote 0
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-

Dear Domenic Sir,

1 more point to be noted as per above code.

this macro only work when in Excel-->Formula Tab--> Calculation Option-->Set to Automatic,
if it ticked on Manual , same first result dropdown till end of data on A1.

if we forget this point on some time...
& that time we can not light in mind that Calculation option +ink Update Option
effect on macro...& we become confused that why my macro behave like this???

if this matter (Manual to Automatic) + Data Link Update..involved in macro...
before leave (Close) Source File, possibility of result increased.

what is your point of view ???

Regards,
Chirag
 
Upvote 0
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-

Hi Chirag,

Yes, Excel and VBA are powerful tools. They allow one to do amazing things. I'm glad you find them interesting. There's certainly a lot to learn. In fact, I continue to learn something new everyday. :)

For your last point, we can add a line of code that calculates the formulas on the worksheet before they are converted into values...

Code:
    'Enter lookup formula and convert to values
    With wbDest.Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("B2:D2").FormulaArray = "=vlookup(a2," & wbSource.Worksheets(sSheet).Range(sRef).Address(, , , True) & ",{2,3,4},false)"
        .Range("B2:D2").Copy .Range("B3:D" & LastRow)
        [COLOR=#ff0000].Calculate[/COLOR]
        With .Range("B2:D" & LastRow)
            .Value = .Value
        End With
    End With

Cheers!
 
Last edited:
Upvote 0
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-

Dear Domenic Sir,

I put .Calculate as per your guide..

Not work..
black Ants run around B2,C2, D2
after you give Enter or Escape-
nothing happened & Macro end

.Calculate not work if
Excel Formula Calculation option have default Manual .

I also put .Calculation=True, but no result.

this seems that t...before whole macro Start h
ere are need a process to Verify in Excel Application
& Set Calculation option to automatic but via VBA...

if we remove .calculation. .it's seems work okay...
but some times ...B2,C2 D2's same data fill down till data on A1..
this is just related on calculation option...I don't know exactly but
I guess ...

1 more point.... it work only destination's 1st worksheet only,,
if you want to work any sheet of destination workbook...
"Out of subscription' displayed

what's your suggestion..???

Need to solve 2 matters

  1. Before stat macro ..but via VBA verify & set Calculation option to Automatic
  2. Macro can run any worksheet
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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