Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










WorkDay Function VBA


The function is to show the next workday date in cell a1


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Workday Function Use In VBA
I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$2-1,V2,Z2:Z$11)

P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.

View Replies!   View Related
Workday Function Or Similar
This is probably a simple question, but one I can't figure out after several hours. I am creating a spreadsheet focused on certifications.

I need to take the date each initial certification was achieved, and add x years to each cert, which is simple enough.

The sticky part is that the (date + x years) can't logically be a weekend; if it is, I need to subtract one or two days from the result, so that it's the *weekday* immediately prior.

e.g. if my initial certification date is: 1/23/2006 and my certifcation expires in four 4 years:

A47= date(2006,1,23)
h47= 10

=DATE(YEAR(A47)+H47,MONTH(A47),DAY(A47))

Result = Saturday, January 23, 2010

If the result is a Saturday or Sunday, I need to subtract one or two days from the date. In this example, I'd like: Friday, January 22, 2010 as the result.

I've been playing with 'if', et. al., to no avail.

View Replies!   View Related
Function That Is The Equivalent Of WORKDAY But For Hours Instead
I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:

In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for

* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible

Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.

View Replies!   View Related
WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date.

View Replies!   View Related
Workday Function/Formula Ignoring Weekends
I'm at my wits end trying to make a workday formula work, but am having no luck. I've searched the other posts, but did not quite find a match. I'm trying to subtract a number of days from a workday function, but it seems to bypass ignoring the weekends (as is supposed to be done automatically with Workday). Here's the formula I'm trying to use:

=WORKDAY(H5,L5,' Reference Info'!$E$110:$E$117)-(I5/8)

I5/8 is a column with hours - this just calculates a number of days to subtract based on the number of scheduled hours.

The formula will calculate, but it still ignores weekends. I've tried burying the I5/8 within the parenthesis, moving it around in the formula,

View Replies!   View Related
Force Formulae Recalculation & Return Day Name From Workday Function
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).

I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)

View Replies!   View Related
Calculating If A Period Is During A Workday
I have two dates in this format:

start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00

i need to check if the period in between those is during a workday.

our workday starts at 10:00:00 and finishes at 19:00:00

so my expected result is 3 hours. (1400-1100)

other example:

start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00

result is 1900-1100 + 1100-1000

= 9 hours

because it is during two workdays...

is there a way to calculate this in VBA?

View Replies!   View Related
Convert Date To Workday
If I have a date in cell G6 of 10/7/08, how could I get the workday of 5 in cell H6? I tried the workday function, but have been unsucessful so far.

View Replies!   View Related
Conditional Format If Not Workday
Maybe I'm getting a blank, maybe the answer is so easy, but lately I canīt solve this: How do I use conditional format to change cell color if cell value (date) is not workday?

View Replies!   View Related
Macro For Workday Dates
Im trying to display dates in excel sheet using macros. can anybody help me how to show Workday(x,x) in VBA.

View Replies!   View Related
Workday Holiday Trigger Date
I am trying to work out a formula calculating dates.

basically a piece of work / report is due for completion twenty working days from a trigger date. And I need the formula to calculate this due date. Im using the workday function, which calculates a date using the number of working days given after the trigger date.

this would work fine, except that the trigger date needs to be included in the calculation (inclusive). I have tried changing the value for the number of working days to nineteen, which would logically give the right answer (20 days including the trigger day). BUT the bloody thing doesnt work, because the trigger date can be on a weekend or holiday!! so then it calculates 19 days (as it should) but the result is wrong because the trigger day isnt included (as its a holiday/weekend).

View Replies!   View Related
Filesearch Object With A Datestamp (workday-1)
I need to be able to open a filenamed in the following format.

c:/workbook20060112.txt.

However the lsat part of the string is dyanmic and works with one business days lag (hence format sheet (date, "yyyymmdd") ) is not approriate. i also have the problem that that MS Excel does not have a holiday calendar (so this would not work over bank holidays/easte/exmas ect). The folder also contains archived files as below.

c:/workbook20060112.txt
c:/workbook20061130.txt
c:/workbook20061129.txt

I have been told about a filesearch object as well as a filesystem object. What situations would you use one over the other?

What I want it the most upto data file to pick up, but I dont want to do a loop where it look through all the files or something that counts down from today until it 'hits' the correct file.

View Replies!   View Related
Workday Formula On A Conditional Formatting
I am having an issue, because aparently workday formula does not work in conditional formattings.
I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.

View Replies!   View Related
Formula That Would Calculate What Workday A Date Would Be
I am trying to find a formula that would calculate what workday a date would be.

So if the date is 2/19/2007, I want to know that is the 13th workday.

View Replies!   View Related
Workday - Adding Partial Days
When I use the formula below it works great;

= J10+(1/$D$10)

J10 = 3-1-07
D10 = 1.5 (Production Rate example .2,.5, 1.5 rate per day) The result is:

3-1-07
3-1-07
3-2-07
3-3-07
3-3-07
3-4-07
3-5-07 and so on...

however, when I try to include only workdays, it does not calculate correctly.

= WORKDAY(J10+(1/$D$10),1,Holidays!B75:B88) with the same definitions as above the result is...

3-1-07
3-2-07
3-3-07
3-4-07

View Replies!   View Related
Write The VBA Function Through The Function Wizard
I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

View Replies!   View Related
WORKDAY Formula Replacement W/o Use Of Analysis Add-In
My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

View Replies!   View Related
Workday And Network Day Formula Challenge
I am trying to create a sheet for a project that will identify dates a project will conclude omitting weekends and holidays. I keep getting an error with the formula I have.

In my spreadsheet:
A1 is the start date
B1 is the number of days after the start date.

The formula I am using:
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{""4/6/2007","5/28/2007", "7/4/2007","9/3/2007","11/22/2007","12/25/2007","1/1/2008","1/21/2008","3/21/2008","5/26/2008", "7/4/2008","9/1/2008","11/27/2008","12/25/2008"}"}+0)

The error I get is with the parens (A1,A1+B1). - at least the parens are highlighted in purple.

View Replies!   View Related
=WORKDAY But Includes Weekends In The Specified Number Of Days
I'm looking for a function similar to =WORKDAY but with a slight variation.

I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.

For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).

View Replies!   View Related
Calculate Workday Difference Between Dates/Times
I have a spreadsheet with Received Date/Time in C2 and Resolved Date/Tim in D2, Work Start Time in E2, Work End Time in F2. C2 and D2 are in dd/mm/yyyy hh:mm AM/PM format:04/03/2008 10:27 AM06/03/2008 1:06 PM9:00:00 AM5:00:00 PM

I need to be able to calculate the resolution time of each request in hours/mins excluding weekends and public holidays. I've tried using the NETWORKDAYS function but it doesn't seem to pick up multiple days between the Received and Resolved dates/times. I have put the Public Holidays in my spreadsheet ( attached) in AA2-AG2.

View Replies!   View Related
Changing A VBA Function Into An IF Function
i am using the following code to create a function in excel

i have to put the worksheets onto mac's so i cannot use a macro

can anyone help me change this into an iF function

load is cell D9 and the factor is in cell E9

Function Pmax(Load, Factor) As Double
Application.Volatile
Select Case (Factor)
Case "0"
Pmax = ""
Case "1"
Pmax = Load
Case "2"
Pmax = Load * 1.09
Case "3"

View Replies!   View Related
Function In VBA
why the below code does not work?

Sub AnsiSub()
ActiveSheet.Cells.Select
Selection.Formula = Value(Trim(Substitute(A3, char(160), char(32))))
MsgBox ("Complete")
End Sub

View Replies!   View Related
VBA Function To Sub
I have a user-defined type as follows:

Type DocketIdent
cStore As String
cSuburb As String
cPostcode As String
cCheckOutID As Integer
cDatePurchase As String
cTimePurchase As String
cInvoiceNo As String
cNoOfItems As Integer
End Type

I have a function that uses a DocketIdent variable and assigns values as shown above to it. i.e. cStore, cSuburb etc.

I'm trying to return these individual Docket details from a Function to a Sub procedure.

Is this possible and would I have to return each field of the type individually?

View Replies!   View Related
Function Instead Of One Within Vba
I try to use a worksheet function instead of one within vba.

Here's what I want to do:

Return the value from a cell within a vertical range named "Phases Active." The value to be returned is the one in the same row as the non- blank cell 1 row to the left of this range. So, I can use "x" to make a selection of the range basically. VLookup, right?

=VLOOKUP((ISBLANK=FALSE),PhasesActive,FALSE)

View Replies!   View Related
VBA Round Function Vs Worksheet Round Function
I don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie
Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??


View Replies!   View Related
Weekday/workday Date 1 Month After The Last Contact Date
I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.

I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.

So, if the date is on a weekend, I need the Monday date instead.

View Replies!   View Related
CountA Function Using VBA
How can I make it work ?

Sub test2()
Dim Wbk, aaa, bbb
Wbk = "\server1folder1file1.xls"
aaa = Application.WorksheetFunction.CountA(Workbooks(Wbk).Sheets(1).Range("A:A"))
bbb = MsgBox(aaa)
End Sub

View Replies!   View Related
VBA GPA Function
Currently I am using this formula =AVERAGE(CHOOSE(MATCH(A1:E1,{"A+","A","A","B+","B","B-","C+","C","C-","D+","D","F"},0),4,3.8,3.6,3.4,3.2,3,2.8,2.6,2.4,2.2,2,0)) to calculate the GPA's and it works fine. What I am trying to do is to wite a function for GPA's and I am coming up short. I am still trying to learn VBA can someone please guide me. Thanks in advance Stephen.

Function gpa(r As Range) As String

On Error GoTo Fail

For Each cell In r
Select Case UCase(Trim(r))

Case "A+": gpa = 4#
Case "A": gpa = 3.8
Case "A-": gpa = 3.6
Case "B+": gpa = 3.4
Case "B": gpa = 3.2
Case "B-": gpa = 3#
Case "C+": gpa = 2.8
Case "C": gpa = 2.6
Case "C-": gpa = 2.4
Case "D+": gpa = 2.2..........


View Replies!   View Related
#value Error In VBA Function
my formula called ' conversion' used to convert foreign money in EUR does not
seem to work. My formula gives a value error. I wonder why...


Function Conversion(Position As Integer, Currency As Integer) As Integer

Dim USD As Integer
Dim GBP As Integer
Dim CHF As Integer
Dim JPY As Integer
Dim NOK As Integer
Dim NZD As Integer
Dim SEK As Integer
Dim ZAR As Integer
Dim ISK As Integer
Dim TRY As Integer
Dim AUD As Integer

' here i give a destination to my forex quotes who are in a excel sheet
USD = Range("N9").Value
GBP = Range("N10").Value
CHF = Range("N11").Value
JPY = Range("N12").Value
NOK = Range("N13").Value
NZD = Range("N14").Value
SEK = Range("N15").Value
ZAR = Range("N16").Value
ISK = Range("N17").Value
TRY = Range("N18").Value
AUD = Range("N19").Value

View Replies!   View Related
VBA Function Not Working
I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set"

I have made sure that Microsoft scripting is referenced.
any clues would be appreciated.
I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started.

{code]
Function BrowseForfolderShell() As String
Dim objshell As Object, objfolder As Object
Set obshell = CreateObject("Shell.Application")
'Uncomment next line to start at desktop
'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0)
Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:")
If (Not objfolder Is Nothing) Then

View Replies!   View Related
VBA IF Function Or If IsEmpty
I need a IF funtion to look at a series of cells and if the cells are empty I want it to proceed to userform1. If the cells have data already i want it to go to userform5.

I tried IF IsEmpty(cell list)=True Then UserForm1 and IF IsEmpty(cell list)=False Then UserForm5

But it only bring up userform5 no matter what the cells have in them.

View Replies!   View Related
Yield Function In VBA
I just upgraded to Office 2007 and am having trouble getting the Yield function to work in a macro I wrote in office 2003. I have added the analysis tool pack and the analysis toolpack for VBA and it still does not work.

The error I get is that the object or property is not supported. All variables used in the function are assuming the correct values.

MarketYield = Application.Worksheetfuntion.Yield(Date1, BondMaturityDate, TotalWAC2, MarketPrice2, 100, 2)

View Replies!   View Related
VBA Left Function
I am trying to use the left function to include only the first account number, for example if a cell has "120122 280000" (there may be many spaces between the two numbers), I only want 120122.
So far my programming only returns the 280000 into the cell.

I am working with cell values in column "K".

View Replies!   View Related
VBA Simple Right Function
Is there any reason why this wouldn't work in Excel 2007 despite working fine in 2003?

.Range("C2:C15132") = Right(.Range("C2:C15132"), 7)

View Replies!   View Related
Worksheet Function In VBA
My workbook has a sheet called "MyList", another called "HisList" , a named range called "myNamedRange" which is "MyList!A:A".

How do I get this functionality of this excel formula

View Replies!   View Related
Replace Function In VBA
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:

[url]

Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes

I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.

View Replies!   View Related
Vba Function Not Updating
I have the folowing vba function that i use to check the existance of files but when the workbook is refreshed, the formula doesn't update. It's only updated when i double click it's cell.

View Replies!   View Related
MOD Function In VBA
In the excel worksheet, I use MOD(YEAR();4) to determine whether there are 365 or 366 days in that year. I would like to use this function also in my User Defined Function VBA Code. I cannot find how to do this.

View Replies!   View Related
Using The OFFSET Function In VBA
I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:

View Replies!   View Related
Use The Iferror Function In VBA
I'm trying to use the iferror function in VBA and the whole code is not executing, as the syntax error is being noticed first, and not allowing the whole code to execute. What I want to happen is if the vlookup returns an error, the cell value should be "*". The code is:

View Replies!   View Related
Find Function In Vba
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.

Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
Else
MsgBox "The currency " & local_currency & " does not exist in the currency list"
Sheets("Remarks").Select
End If

View Replies!   View Related
Maxif Function In Vba
I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell:

= SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))

Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types - without success. Can anybody help me?

Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)

AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue) * (calcRange)))"

End Function

View Replies!   View Related
Countif Function In Vba
I'm trying to use the worksheet function countif in my code and it does not recognize it. But it does recognize counta. Here's a sample of my code.

Private Sub UserForm_Activate()
Dim MyUnion As Range
Set MyUnion = Union(Rows(12), Rows(14), Rows(16), Rows(18), Rows(20), Rows(22), _
Rows(29), Rows(31), Rows(33), Rows(35), Rows(37), Rows(39), _
Rows(46), Rows(48), Rows(50), Rows(52), Rows(54), Rows(56), _
Rows(63), Rows(65), Rows(67), Rows(69), Rows(71), Rows(73))
txtTtlDays.Text = Application.worksheetfunction.countif(MyUnion, "&A")
End Sub

View Replies!   View Related
Xirr Function In VBA
I am having trouble with the Xirr function in VBA. The problem revolves around the sensitivity of the solution to the 'initial guess'. specifically the boundary between a +ve and -ve guess. i.e. if the xirr is large and -ve a positive guess will always give me a solution very close to zero, as opposed to the more meaningfull solution say -65%.

To get around this i have constructed the UDF below, which is used as part of a sub, however for some investments i get the type mismatch error (error(13)) even though the arrays are of identical size and the data should be uniform as it is a dump from a database.

Has anyone encountered this 'type mismatch' problem with xirr before? is there a way to access the vba code for xirr so that it can be altered?

Function fkxirr(arrVals, arrDates)

Dim r As Double
Dim Step As Double
Dim tolerance As Double
Dim xir As Double

r = 10
tolerance = 0.000001
Step = 0.1

View Replies!   View Related
Use Function/Formula Not Available In VBA
I want to evaluate Bessel functions as parts of a function in VBA. These functions are available in Excel not VBA. I tried to use range (). value to do this, i.e.

Function Bessel2(x)
Worksheets("Sheet2").Range("C1").Value = x
Bessel2 = Worksheets("Sheet2").Range("C2").Value
End Function

In cell C2, I enter the formula BESSELI(2,C1). But this did not work.

View Replies!   View Related
SUMIF Function In VBA Macro
I have a macro[A] that sums a column based on parameters passed to it from another macro[b]

Macro A

Sub SumColumns(varASheetName As Variant, varFormulaCell As Variant, varSumRange As Variant)

Sheets(varASheetName). Range(varFormulaCell).Value = Application.WorksheetFunction.Sum(varSumRange)

End Sub

' Macro B

Sub SumDebtorsStuff()
Call SumColumns("DebtorsRaw", "D10", Range("D21", Range("D65536").End(xlUp)))
End Sub ...........



View Replies!   View Related
Calling A Custom Function Within VBA
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.

Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)

CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography

End Function

View Replies!   View Related
Writing VBA Macro Or Function
I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).

If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.

Should we write the macro or can it be accomplished by any Excel Function?

View Replies!   View Related
Using Rank Worsheet Function In VBA
I am trying to rank number that do not have a specific adress. My problem is that I do not know how to define all parameters of this function

View Replies!   View Related
Addin Worksheet Function In VBA
I have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments:
- a range for the input data to smooth
- a parameter detailing how to do the smoothing.

It outputs a matrix of smoothed results, and therefore requires the Ctrl-Shift-Enter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results.

This is how the working syntax is in a cell:
=SMOOTH(B3:B1032, "5RSSH,5RSSH,>,5RSSH")

And there the nightmare starts :
- I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue

- Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process).

I have tried an example I found somewhere using a built-in Excel Worksheet function MMult and it worked with ranges:

Sub SomeSub()

Dim xArray As Variant, yArray As Variant, zArray As Variant
Dim Fn As Object
Set Fn = Application.WorksheetFunction

xArray = Range("A1:B2").Value
yArray = Range("D1:E2").Value
zArray = Fn.MMult(xArray, yArray)

ActiveCell.Resize(2, 2).Value = zArray

End Sub


View Replies!   View Related
Copyright Đ 2005-08 www.BigResource.com, All rights reserved