Add Two If Operations To VBA Function
Nov 13, 2012
I'm trying to take a vba function and add a second if clause. Here's the function:
VB:
Function getdes(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRn Then
holder = holder & ce.Offset(0, 2).Value & vbLf & ""
End If
Next ce
getdes = Left(holder, Len(holder) - 2)
End Function
I want to do something like:
VB:
Function getdes(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRn If ce.Offset (0,-02).Value = "y" Then
holder = holder & ce.Offset(0, 2).Value & vbLf & ""
End If
Next ce
getdes = Left(holder, Len(holder) - 2)
End Function
View 3 Replies
ADVERTISEMENT
Jun 17, 2006
Does excel contain a format of : "number of months"?
Example : I have 3 cells
A1 : should contain the number of months (3 months)
A2 : should contain a date with this format (dd/mm/aaaa)
A3 : should contain A1+A2
View 4 Replies
View Related
Aug 16, 2008
I have "bashed into shape" a formula that involves 2 sets of parenthesis. After progressing beyond "#REF! or #NAME errors, Excel's automated Help suggested to add a comma, and the formula functions as wanted, I thought I was "Smart".
Later, when saving the formula to my `Formula Workbook', "Order of Operations" crossed my mind. - I think this formula is backwards, but `Excel Help' only suggested the addition of a comma? Maybe the all inclusive parenthes allowed the order to be understood?
The Formula: =AVERAGE((E56:AH56),COUNTIF(E56:AH56,">0"))
Q: Shouldn't the COUNTIF occur before the AVERAGE? Did the extra Parenthis automate the order of Operation within the Parenthis?
View 9 Replies
View Related
Jun 2, 2006
I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator.
In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!
'Initializing tagrow so we can test what train we are trending'
tagrow = Cells(12, 2).Value
'Only Propylene flows need adjustment in A and B trains, calculate everything'
If tagrow = 7 Or tagrow = 8 Then
Sheet2.Calculate
Range("D15:D115").Value = Range("C15:C115").Value / 1000
End If
View 3 Replies
View Related
Mar 7, 2007
I need to have the contents of cell A1 (a 3 component list) control a formula in cell E1. If A1 is "add", then E1 should be B1 x D1. If A1 is "delete", then E1 should be B1 x D1 x .6, and if A1 is "delete ilo", then E1 should be B1 x D1 x (-1).
A1 B1 C1 D1 E1
list qty. list price of C1 item formula needed
( lookup)
View 5 Replies
View Related
Mar 5, 2014
I have a formula that looks like this:
=12*NETWORKDAYS(C2,D2)-12+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)
This formula calculated the time between two dates (Start date in C2, End Date in C3) between the hours of 06:00 and 18:00 on weekdays. This is so I can work out how long an incident has spend within the group at the time when our service should be operating.
I was given this formula by someone on this forum a while back and i was new to excel and formulas so I just took it at face value since it worked. Now I want to disect it so I can use it in other situations where similar calculations are needed. I want to try to break it up into easier to understand chunks to try to understand what it is I need to change in order to do these related calculations.
NETWORKDAYS(D2,D2)
Is this trying to distinguish if D2 is a weekday?
MOD(D2,1)*24 Is this not the same as (1-D2)*24
NETWORKDAYS(C2,C2)*MOD(C2,1) Is this not the same as: IF(weekday(C2)>5,(C2-1)*7,0)
IF(NETWORKDAYS(D2,D2),....,....) What is the comparison here? if NETWORKDAYS(D2,D2) = 0 or 1?
Is this equivalent?
=12*(
NETWORKDAYS(C2,D2)-12
+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)
-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)
)
View 1 Replies
View Related
Jun 22, 2009
I'm try to put together a set of macros that perform certain operations within a file. However I've got a bit stuck on the following, and I can't see where I've gone wrong:
View 3 Replies
View Related
Jul 28, 2007
I want to copy several separate sections on a worksheet and place these sequentially in an array, then paste these in order into another worksheet.
I have written a macro to copy and paste between the two spreadsheets, however, this is rather basic as it involves straddling between the two and performing multiple alternate copy and paste operations, as only one section can be copied at any one time.
View 9 Replies
View Related
Jan 16, 2009
I am trying to write a formula in 2003. The basic premise is below and I'm having problems getting all the senarios to work out.
Cells:
E20 = Calc'ed amount
E22 = Min amount (Sometimes equals zero - No Min)
E23 = Max amount (Sometimes equals zero - No Max)
E24 = Additional amount to add
=IF(AND(E22=0,E23=0),SUM(E20,E24),IF(AND(E220,E230),IF(AND(E20>E22,E20E23,SUM(E23,E24),IF(AND(E20E22),SUM(E20,E24),IF(AND(E20
View 9 Replies
View Related
Oct 17, 2008
I am trying to make things easier for the next user by having one command button run the macros for the other worksheets (so they don't have to switch between worksheet)
However,
I get "Runtime error '1004' Application-defined or object-defined error"
View 8 Replies
View Related
Mar 30, 2007
I need a macro that will perform a set of oprations on all worksheets in a workbook. The names and number of worksheets will not always be the same. I have found several macros that look like they will do this, but when I try to add my code they don't seem to work.
View 9 Replies
View Related
Jul 18, 2007
I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?
View 9 Replies
View Related
Aug 8, 2007
To speed up copy and paste operations in a macro, I've heard that one can replace the following:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
with
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
The second code should run faster as the clipboard is not used to store the copied info.
When I tried this, though, I didn't notice any improvement in macro running speed.
View 9 Replies
View Related
Feb 2, 2007
I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.
Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.
View 2 Replies
View Related
Dec 30, 2008
when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.
View 2 Replies
View Related
May 22, 2006
Does excel contain a format of just : "number of days"??
For example : I have 3 cells
A1 : should contain the number 30 (30 days)
A2 : should contain a date with this format (jj/mm/aaaa hh:mm)
A3 : should contain A1+A2
View 3 Replies
View Related
May 24, 2006
(Date1-Date2) : what's the format I should use to obtain the result in number of days.
For example :
(23/12/2006 16:30:00) - (18/11/2006 15:30:00) : It should give 35 days 1 Hour (35:01:00:00 - dd:hh:mm:ss)
How can I have this format (dd:hh:mm:ss)
View 9 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
May 14, 2013
Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
View 9 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Dec 3, 2013
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
View 2 Replies
View Related
Jul 17, 2008
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
View 13 Replies
View Related
Nov 25, 2008
I keep getting the "You've entered too many arguments for this function" error.
Here is the formula:
=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))
View 6 Replies
View Related
May 13, 2013
I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)
right now it looks like:
If (C10
View 1 Replies
View Related
Mar 2, 2008
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).
My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.
My Script:
Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult
Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select
If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If
End Function
So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).
My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View 9 Replies
View Related
Mar 26, 2009
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
View 2 Replies
View Related
Nov 19, 2009
I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.
The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.
I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).
View 5 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