Add Ranges In VBA To Worksheet Function
Sep 7, 2006
VBA will not allow the worksheet function MMult to be called during the subroutine. The error that flashes is: "Run-time error 1004. Unable to get the MMult property of the WorksheetFunction property"
Worksheets("MatrixOutput"). Range("ak4:di35").Select
Worksheets("MatrixOutput").Range("c4:ah80").Select
XTranspX = Application.WorksheetFunction.MMult("c4:ah80", "ak4:di35")
Worksheets("MatrixOutput").Range("b84:ag115").FormulaArray = XTranspX
View 4 Replies
ADVERTISEMENT
Sep 26, 2011
I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..
Here is the code:
Sub test1()
Dim myvalue As Variant
Dim wsname As String
Dim i As Integer
Dim j As Integer
Dim r1 As range
Dim r2 As range
Dim r3 As range
Dim r4 As range
wsname = "CustomerIDbyParts"
[Code] .......
The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?
View 3 Replies
View Related
Nov 30, 2007
How can I use a function similar to the "max Function" using more than 30 numbers?
View 9 Replies
View Related
May 18, 2014
I am trying to create an IF function and how to create it for multiple ranges. The for the following criteria is what I am trying to work with: If a checking balance is $500 dollars or below return "low", If it is between 500 and 2000 return "Medium", and IF greater than 2000 return "high"
View 2 Replies
View Related
Nov 8, 2008
I have used the Networkday Function to get the Working Days in a month excluding the Saturdays and Sundays and also the Holidays as Networkday Function by default excludes the Sat/Sun and any of Regional Holidays. This works absolutely fine, howevr in the realistic scenarion we need to also add any leaves allotted to an employee.
Now if this List of Holidays is entered in a seperate column, how can this be consolidated to get the actual Working Days = Total Days in a Month - ( Sat/Sun + Regional Holidays) - Leaves ). In the below syntax, the range $I$2:$I$4 contains the Regional Holidays and can be defined as a Name= Holidays
NETWORKDAYS(B2,C2,$I$2:$I$4)
NETWORKDAYS(Startdate,EndDate,Holidays)
Can we incorporate something like a double range like this : Leaves without writing it below the Holiday Range, this new Range by using Offset function can be made into a Dynamic Range. < =NETWORKDAYS(Startdate,EndDate,Holidays & Leaves)>
View 5 Replies
View Related
Feb 1, 2010
I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.
if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))
Does anybody know of an easy way around this?
View 6 Replies
View Related
Mar 15, 2012
I would like to utilize the IF function to show the following:
If the date in cell J11 is before April 30, J19 will show J18 * .01, if the date in cell J11 is between May 1 and May 30, J10 will show J18 * .02, and if the date in cell J11 is between June 1 and June 30, J10 will show J18 * .03.
View 3 Replies
View Related
Apr 21, 2013
I have a table (approx 10 rows x 10 columns) that I am trying to lookup. I have to first look across the top of the table (cols 2-10) to find a name, then look down for a value (exact match) within than array and then find the corresponding value (in that position) in the 1st column. I have used 'name manager' to name these arrays (in columns, rows 2-10).
I have setup the names of the arrays with a drop down list (as per some utube videos). When I try to evaluate the match function (with the array name as a cell reference) i get #value. When I directly type in the name of the array into the match function I get a correct answer.
Should I be using other functions such as indirect, choose, etc instead?
View 9 Replies
View Related
Jun 7, 2006
I have named range in column A = holfrom and another in column B = holtill
When I try to use NETWORKDAYS(holfrom;holtill) I get an error VALUE
Is it even possible to use this function with named ranges
View 5 Replies
View Related
Sep 13, 2006
I am familiar with the SUMPRODUCT function, but is there a similar function that will divide the cells of two columns and then add them together?
View 3 Replies
View Related
Oct 4, 2007
i have a function ("function1") that takes a range as an input:
function1 (a As Range) As Double
i have another function ("function2") that internally creates an array "a" that I need to be the input for function1. I tried, inside function2,:
...
function1(a)
...
but of course (?) it does not work...
View 9 Replies
View Related
Feb 14, 2014
This time I have a situation like this:
Column A - Dates
Column B - Equipment Type
Column C - Load Start Time
Column D - Load End Time
I need to do some statistical analysis (average, mode, medium, st dev) on the load times, which is load start time - load end time. As far as I know, I can't subtract ranges (column d i column c) within Averageifs function. I assume I would need to use an array function for this.
View 11 Replies
View Related
Apr 28, 2009
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges.
I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells.
Has anyone ever done something similar? Is it hard to program?
View 14 Replies
View Related
May 10, 2006
I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:
Public Function xyz(a() as Variant) as Double
There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?
A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.
An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.
View 7 Replies
View Related
Dec 28, 2006
I am trying to write a User Defined Function in VBA to perform a simple two-dimensional table lookup. I have the temperature distribution in a solid given in a table in Sheet2. The first column of the table contains the time values, the first row contains the spacial values (radii), and the intersections contain temperature values. These ranges are named times, radii, and temperatures. In Excel, I can perform the lookup using: =index(temperatures, match(time, times, 1), match(radius, radii, 1))
But how do you do this in VBA? More specifically, how do you deal with passing ranges to the WorksheetFunctions?
Function temperature_ref(time_range As Range, radii_range As Range, temperatures_range As Range, time As Double, raduis As Double)
r = WorksheetFunction.Match(time, time_range, 1)
c = WorksheetFunction.Match(radius, radii_range, 1)
temperature = WorksheetFunction.Index(temperatures_range, r, c)
End Function
This function would be called (from any worksheet) as..................
View 4 Replies
View Related
Jun 2, 2014
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
ExampleFile.xlsm
View 13 Replies
View Related
Oct 31, 2011
I have macro that brings information from outlook to excel. In the beginning of macro, it deletes range of cells. That destroys the functions that target those cells. Is there a way avoid that? Using some different method or ?
Code:
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim myCalItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
[Code] ........
Running the macro messes up all funtions that targets those cells.
Like:
Code:
=DATEVALUE(MID(data!#REF!;4;2)&"."&LEFT(data!#REF!;2)&"."&RIGHT(data!#REF!;2))
This really great code to get data from outlook is originally: [URL] ........
View 2 Replies
View Related
Nov 29, 2007
Right now I have 2 ranges say A and B. Range A is in a worksheet on row 7 and range B is on the same worksheet row 8. What I want to do is make it so that range B gets pasted in for range A and vice versa so that range A in now in row 8 and range B is in row 7.
View 2 Replies
View Related
Sep 24, 2012
I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".
However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"
Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?
View 5 Replies
View Related
Mar 25, 2014
I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.
I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.
Dynamic Ranges INDIRECT v2.xlsb
View 3 Replies
View Related
Dec 23, 2009
I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Denominator = 0............
View 9 Replies
View Related
Aug 1, 2009
I've a workbook in excel 2007 with 6 worksheets, 4 of witch have a named ranges, i would like to have a macro that consolidates this named ranges into one worksheet.
Example:
Sheet1 -- Skip Worksheet
Sheet2 -- Skip Worksheet
Sheet3 -- Range1
Sheet4 -- Range2
Sheet5 -- Range3
Sheet6 -- Range4
Result:
Sheet7
Range1
Range2
Range3
Range4
The Named Ranges are Excel 2007 Tables and they are positioned in the same place in Sheet3 to Sheet6.
View 4 Replies
View Related
Mar 23, 2013
What is the simplest way to do it in VBA?
View 3 Replies
View Related
Mar 24, 2009
I am using Excel 2003. I have a worksheet called "RawData" which has a continuous list of invoices, I need to separate off the first invoice from the rest of the invoices into a new worksheet and call it "sheet1". The number of rows from cell A1 at the top left corner varies and is never constant from invoice to invoice however the last row of the invoice I need to move always includes the text string "NET PAYABLE TO".
So I need to copy all rows from "A1" to the row that has the text to a new worksheet called "sheet1", I then need to delete those rows only from the original "RawData" worksheet, leaving the remaining invoices in "RawData" intact. If it is helpful to you, the first row on every invoice has the text string "TAX INVOICE".
There are no empty rows between the "NET PAYABLE TO" row at the end of an invoice and the "TAX INVOICE" row at the start of the next invoice, so it is literally a continuous list.
Can this routine then be continued on all the other invoices in "RawData", copying them to new worksheets in the same workbook "sheet2","sheet3" etc until there is no data left in RawData, bearing in mind that there may be 10 invoices or 100 invoices in the "RawData" sheet.
View 9 Replies
View Related
Jun 30, 2006
Can named ranges defined using the Application.names.add method be accessed using the worksheet(x).names(name) property?
I would like to access only the named ranges on one worksheet, but, it seems to me that the names collection of my worksheet contains no names.
If this is a result of my declaring the named ranges using the application.names.add method, is there a worksheet property that I could use to access only the named ranges on that worksheet?
View 9 Replies
View Related
Aug 22, 2006
Ok What I want to do is copy the info in Cells A1:N55 into cells A63 onwards, now this I have completed so far with the following code, but what I also want to do is copy any changes that are made in cells A1:N55 from when this copy function is run into cells A63:N118.
Does anyone know of any way I could go about doing this?
Sub CopyJob()
Dim wSheet As Worksheet, strWS As String
For Each wSheet In ActiveWorkbook.Sheets
strWS = Left(wSheet.Name, 2)
Select Case strWS
Case Is = "AJ", "CJ", "PJ" .......................
View 9 Replies
View Related
Jan 15, 2007
I am looking for a way to modify the following macro so that I can run the same code for multiple ranges that are on different worksheets.
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim TimeStr As String
On Error Goto EndMacro
If Application.Intersect(Target, Range(" schedule!H4:H35,J4:J35,K4:K35,M4:M35,N4:N35,P4:P35,Q4:Q35,S4:S35,T4:T35,V4:V35,W4:W35,Y4:Y35,Z4:Z35,AB4:AB35")) Is Nothing Then
Exit Sub ..........................
View 9 Replies
View Related
Aug 13, 2013
If I copy worksheet in Excel, the named ranges are copied too. If I copy in VBA, they are not. Is the only solution to loop thru the named ranges or is there something smarter like some "fullcopy"?
View 2 Replies
View Related
Jun 16, 2008
I was after a bit of code to delete the range names on a particular worksheet I thought it couldn't be that hard but have only been able to find this in the archives, unfortunately I get an error when trying to execute it:
Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Org Lookups"
For Each n In ThisWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n
The error I'm getting is Run-Time error 1004, Application defined or object defined error.
View 9 Replies
View Related
Sep 23, 2006
I need the correct syntax to consolidate ranges from several worksheets into a summary worksheet - all in the same workbook. I am building the Array on the fly, and I keep getting errors. (Subscript out of range being the most recent).
The code now is opening only one workbook so I can keep it simple. It iterates through the sheets collection, and builds the array.
Private Sub cbConsolidateToRollups_Click()
Dim MyArray() As Variant
Dim Source As Variant
Dim SheetNames As Variant
Dim SingleQuote, DoubleQuote
SingleQuote = Chr(39)
DoubleQuote = Chr(34)
'set the directory to Rollups
ToPath = Application.DefaultFilePath & "Cost Tracking" & "Rollups"
ChDir ToPath ....................
View 9 Replies
View Related
