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