UDF Range Argument Into Array Assignment
May 2, 2008
I having trouble with an intermediate step filling of an array based on the input range data and polynomial degree. The values calculated (xsum and xysum) for the array (F in the code) are accurate, but the function returns #VALUE for all elements.
Function poly_fit(ByVal Xdata As Range, Ydata As Range) As Variant
Dim a As Integer, b As Integer, c As Integer
Dim deg As Integer, num_pt As Integer
Dim Xs() As Variant, Ys As Variant
Dim F() As Variant, XY() As Variant, coef() As Variant
Dim xsum As Variant, xysum As Variant
Xs = WorksheetFunction.Transpose(Xdata)
Ys = WorksheetFunction.Transpose(Ydata)
If UBound(Xs) UBound(Ys) Then
MsgBox "You don't have the same number of X's and Y's"
End
View 9 Replies
ADVERTISEMENT
Jul 16, 2009
The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.
View 3 Replies
View Related
Dec 28, 2013
Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below
=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)
View 9 Replies
View Related
May 6, 2008
I have below code apearing couple times in my macro and I'd like to make a variable.
Plese see belowe
Workbooks.OpenText Filename:= _
"S:ReportsMy ReportsGuidefilename20080430", Origin:=xlMSDOS, _ ' filename should be another variable as it referes to different files
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array( _
23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1)), _
TrailingMinusNumbers:=True
View 3 Replies
View Related
Nov 3, 2009
I'm fairly new to VBA (exactly 8 hours of paid experience ) and I've stumbled upon the following.
Say I wanted to select a certain range of cells (which later have to be merged, don't ask why, they just have to. It's in the job description and it's not in my power to change things around here).
View 14 Replies
View Related
Oct 20, 2009
I have the following formula which works fine in this form:
View 4 Replies
View Related
Sep 8, 2005
I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.
To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.
If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.
Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer
Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i
Mean = Sum / UBound(Arr)
End Function
View 14 Replies
View Related
Nov 5, 2013
I have 3 columns in an excel sheet (ID, Signed by & To be checked), and in another sheet, I have names under a column "Checkers".
I need a macro where in if I run it "To be checked" column in sheet 1 should be equally assigned to the "Checkers" in sheet 2 and the rest should be blanks.
find the below sample:
Before Macro:
ID
Signed by
To be Checked
121
AAA
123
BBB
[code]....
View 9 Replies
View Related
Feb 24, 2014
I need to assign van seating assignments to a client room census. I have a workbook attached. I am hoping a formula can get me there. The only variables are if the cell in column E has TO BE CLEANED or READY in it, then I want the formula to overlook that particular cell. I don't want to assign a seat to an empty room! My workbook should make what I am attempting to accomplish much more clear.
Also, there are either 12 or 15 seats available in each van, but the workbook reflects that.
View 9 Replies
View Related
Feb 27, 2009
On Sheet1 I have a small list of names in column A and a number next to the name in column B. This number may vary.
What I want to do is on Sheet2 in column A repeat the name based on the number from sheet1 in column B.
Now the sheet is shared among all of us and macro are out. Is this something that is possible with a function?
View 9 Replies
View Related
Jan 28, 2008
i was asked to make a fair rota and i found this 1 on this website its useful but i need to add to it. if you download this work rota you will understand what i mean
http://www.ozgrid.com/forum/attachme...0&d=1154640274. i need to find out what the formula/macro is used in this work rota ...its exactly what i need but i need to add saturday and sunday to this rota and more duty columns as their is more employees and when i click the button it randomply places employees in different positions.
View 2 Replies
View Related
Jul 25, 2012
I want to know how to copy a range into an array then an array back into an range.
Code:
Dim a(3,3) As Double
a(3,3) = Range("C3:E5")
Range("C10:E12") = a(3,3)
View 1 Replies
View Related
Jul 6, 2012
I have two columns one with the variables names and the other with the values now I have to assign vales to teh variables dynamically using the macros and vba code.
View 7 Replies
View Related
Nov 17, 2007
When I use customize and assign a macro (to a button) I set the "Marco In" option to "This Workbook" then select a macro from the available list. If you close the Assign Macro dialog and open it again, notice how it appends the name of the file to the macro name. Later, in an automated process the xls filename is appended with a value and I think this is causing the problem because the appended data is static and thus no longer is in sync with the new (latest) filename. How do I assign macro's to either custom toolbar's or autoshapes so that the assignment is tolerant of file name changes? I tried to delete the appended data but it is appended automatically.
I am also curious why some of the macro's listed in the dialog include a filename and macro name delimited with an exclaimation mark and some are macro name only (with no delimiter).
View 9 Replies
View Related
Jul 28, 2009
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment.
Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes.
I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View 3 Replies
View Related
Jan 26, 2010
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
View 4 Replies
View Related
Nov 4, 2009
im having trouble with an IF argument as you can see from example the data sheet is where i need the result,the first formula i have done in B24 but cant understand why the 2nd argument will not go in and i keep getting a value error as shown in C24.
i need say If in the sunday tab G8="A1"then insert G32.Then do it for "A2"=G32 and so on.all in Data B24
Is there a shorter way as i have to go up to A9 B9 C9 D9.
View 2 Replies
View Related
Nov 11, 2009
I have a sheet that has 2 fields that i need a yes/no answer from. Basically if b = "P1" then a should be less than or equal to 4 to reply good otherwise it should reply no. but if b isnt "P1" but instead is P2 P3 or P4 then it should => 10, 30, 40 respectively.
View 8 Replies
View Related
Sep 25, 2008
how to create a function argument containing my own defined formula?
View 9 Replies
View Related
Mar 15, 2009
does IF in excel formula take 27 Arg and ( AND, OR) in IF ststement take the same Arg
and does SumProduct Take the same Arg .
View 9 Replies
View Related
Jul 19, 2007
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.
In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?
Sub updateAll()
includeHeader = False
Application.Run "SelectColumn"
' need something like:
' Application.Run "SelectColumn(includeHeader)"
' where subroutine below has something like:
' Sub SelectColumn(includeFirst)
End Sub
Sub SelectColumn()
'Select a Column or Row of Cells in a Used Range Quickly
'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx
includeFirst = False
'includeFirst = True
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell........................
View 3 Replies
View Related
Jan 8, 2014
Is it possible to create an IF argument based on the filename?
Example - A2=IF(Filename="template",A1,Filename)
Reason - A1 is the result of a MAX function on the template WB but once template is saved as a different file name I want A2 to = the new filename?
View 10 Replies
View Related
May 23, 2008
formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
View 10 Replies
View Related
Jan 23, 2008
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View 9 Replies
View Related
Dec 22, 2009
i have a calendar with the dates in a row
I want to count instances of an entry under the dates using 'countif' from 01JAN until today.
My problem is that today changes value [up by one] each day so the 'countif' function will need to extend its range to the right by one cell each day.
View 11 Replies
View Related
Nov 7, 2011
I am trying to write a macro that has multiple if arguments.
An example is as follows:
If cell b11 had fill colour red, then colour fill cell B8 green,
If cell b11 had fill colour green, then colour fill cell B8 red,
If cell b11 had fill colour yellow, then colour fill cell B8 blue,
If cell b11 had fill colour blue, then colour fill cell B8 yellow,
end if.
View 5 Replies
View Related
Sep 24, 2012
There are 100 loans, each one of them need to be tested on 11 different tests, the results are either "pass" or "fail". All the test results are stored in the Boolean array. g_sel_tag(). For instance, if g_sel_tag(2,7) = true that means the 2nd loan has failed on the 7th test.
But I got stock with the following code, I think there should be a loop for the public variable rep_num in order to loop through the 11 test results for each loan. I have searched the entire project but did not find any value was assigned to the variable rep_num. The following code is part of a huge project, I can't test run it to see if the following code is valid at this point.
I am new to VBA therefore wondering if it is valid with no specific value assigned to rep_num. (I think there should be something like "for rep_num = 1 to 11" in order to get it running).
There are several codes in the project with similar structure where they just use rep_num without assigning any specific values.
Code:
Dim rep_num As Interger
Sub test_results_reporter (rep_num As Interger)
Dim i As Interger
Dim g_sel_tag(1 to 100, 1 to 11) as Boolean
For i = 1 to 100
If g_sel_tag(i,rep_num) Then Msgbox "Loan" & i & "has failed on test" & rep_num
Next
End sub
View 2 Replies
View Related
Mar 14, 2013
In the Formula below I am attempting count records that are not "Closed" or "Cancelled"
But records with those two status values are still being counted I have a feeling that my attempt to create a "not equal to" condition may be the problem, even though Excel did not error when I put in this formula. I am certain that the worksheet and columns that these conditions are pointing to are correct. Note that the first 2 conditions are working correctly.
=COUNTIFS('QC Extract'!$C:$C,$B34,'QC Extract'!$I:$I,F5,'QC Extract'!$H:$H," Closed",'QC Extract'!$H:$H," Cancelled")
View 2 Replies
View Related
Jun 4, 2009
I need a simple IF AND formula. I just can never manage to get them right. I need a formula (in Column M) to evaluate a date count in Column L (which is a count of business days since another date, fyi). It needs to check to see if the number of days since the date in Column L is greater than 10 AND if the value of Column I is one or zero. If the argument returns true (more than ten days and a zero in Column I) it should return a vaue of "Yes" and "No" if false.
View 9 Replies
View Related
Dec 11, 2006
I want to be able to call a procedure and pas 2 variables that it will need to it.
The code looks like this:
Private Sub UpdateScreen(StartLine As Byte, EndLine As Byte)
End Sub
UpdateScreen(104 , 140)
For some odd reason I get an "Expected: = "error when I enter the last line. I works if I send only one variable, but not when I send 2.
View 2 Replies
View Related