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.
I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?
IF(logical_test, [value_if_true], [value_if_false])
value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)
How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...
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.
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
i want to create a lot of buttons, one in each line, that, when you click on them, open a file. The filename is in a cell in the same row as the button.
Of course every button should open a different file, and that is the problem. As far as i know (not very much) the buttons can only call other subs, and not with an argument.
What should i put into the "onAction" property of each button, so that each button opens a different file? I really dont want to create hundreds of subs just to get this done, there must be another way.
PS: By the way, how can i delete all but one buttons of a sheet with a vba script? Or shall I open another post for that?
In the code bellow I would like to automate a if function until the 1st emptycell in the row 7 of my spreadsheet.
In my IF function, I would like to refer to a fix cell ("B1") while the other argument is in the same column as the function but two rows bellow.
My problem is that I don't figure out how to refer to a fix cell with the syntax I would like to use t run the macro.
I think it's more clear when you'll see the code
Sub Face2face()
Range("B5").Activate
Do
If ActiveCell.FormulaR1C1="=If(R[2]C>=Range("B2"),Range("B2"),R[2]C)"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))
End Sub
I have recorded a simple macro ( copying a cell, and then pasting the formula into various others), and I get the following error
Compile Error: Argument Not Optional
I have highlighted where the error first happens
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/08/2007 by Michael Traynor
'
'
Range("K7:K8").Select
Selection.Copy
Range("K167:AJ168").Select
Range("AJ167").Activate
Range("K167:AJ168,K175:AJ176").Select
Range("AJ175").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184").Select
Range("AJ183").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184,K191:AJ192").Select
As I've said I didn't write this, it was recorded from Excel.
I have some code that, although works fine in Excel 2003, does not in Excel 1997. I receive this error when I try running it:
COMPILE ERROR:
NAMED ARGUMENT NOT FOUND
Sub HPVAL()
Dim r As Range, myStr As String
myStr = "HP"
Set r = Cells. Find(What:=myStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not r Is Nothing Then
r = r.Value
While Not r Is Nothing
Set r = Cells.FindNext(r)
If Not r Is Nothing Then
r = r.Value
End If
Wend
End If
End Sub
It looks like Excel is getting hung up on the "SearchFormat:=" portion of the code.
This Thread describes a function that I've found incredibly useful to reset the "last used cell" in a worksheet. I use this function a lot, and I would like to make a macro out of it. I have a menu-item added in my excel that runs the macro;
View 3 Replies View RelatedI 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 Relatedhow to create a function argument containing my own defined formula?
View 9 Replies View Relateddoes IF in excel formula take 27 Arg and ( AND, OR) in IF ststement take the same Arg
and does SumProduct Take the same Arg .
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........................
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?
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??
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).
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 Relatedi 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.
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.
=COUNTIFS('QC Extract'!$C:$C,$B34,'QC Extract'!$I:$I,F5,'QC Extract'!$H:$H," Closed",'QC Extract'!$H:$H," Cancelled")
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.
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.
I am trying to compute correlations using Correl function available in Excel. Get "Argument Not Optional" Error. Cannot understand what I am doing wrong.
Sub test()
With Worksheets("Corr")
Range("H1").Value = WorksheetFunction.Correl(Range("A1:A252, B1:B252"))
End With
End Sub
I have a 100 element header row. Each cell represents an account. I want to sum the amounts of each subsequent row if the word "saving" shows up in the header. For instance, there may be "saving 1" "Savings for Xmas" "Savings 2" in the header row, and I want those to trigger the sumif function.
Basically I want something like sumif(B2:B101,exact(left(B2:B101,6),"saving"),A2:A101)
This error occured me, and i clicked "Debug" to see where was the error and i found a strange thing that i don't know how to solve it:
The line has the following
I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be
RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)
Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3
And here is the code I have tried to produce:
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies View RelatedWith the UDF below and the portion highlighted in red, how can the red part be made part of the arguments instead of specifying it in the code?
Code:
Function Countcolor(rColor As Range, rCountRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim Result
Application.Volatile
[Code]....
I have hit the limit with IF statements in a formula I have in a questionnaire that I made in Excel 2010. How would I create this formula in VBA?
"TRUE" is already returned in those cells where a "Yes" radio button is selected.
I would like the macro to run when a button is selected, and for it to post the return (i.e., "BP Owned Circuit") in cell B6.
Here's the formula:
= IF(AND(C3=TRUE,C4=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE,C15=TRUE,C16=TRUE,
C17=TRUE,C18=TRUE,C19=TRUE),"BP Owned (Circuit)",
IF(AND(C6=TRUE,C9=TRUE,C10=TRUE,C11=TRUE,C14=TRUE,C15=TRUE,C17=TRUE,C18=TRUE,C19),"BP Owned (VPN)",
IF(AND(C6=TRUE,C7=TRUE,C8=TRUE,C9=TRUE,C10=TRUE,C18=TRUE,C19=TRUE),"Shared VPN (IPSec)",
[Code] .........
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