With Excel you can do If(array, array2, false) to poplulate another array. Is there a way to replication this without looping. For instance can the IIf hold an array as its expression or is there any other way of replicating this If statement without looping?
View 4 RepliesI have a list of data for each year dating back 10 years and some of lists had entries that weren't available so the cell showed up #N/A. I searched and replaced them with 0 but when I graph the data it skews my graph. I was wondering if it was possible to take a logic (IF) statement that says if the cell = 0 then take the average of the next and previous data points so the graph looks smooth. i.e. =IF(G444=0,(G443+G445)/2,G444) This gives me the correct number i want, but i want to apply it to all my lists like a format if possible.
View 1 Replies View RelatedTrying to write function statement that does the following:
IF cells C9:E14 contain any of the following: "Termination" or "Forms" or "PWE" or "TSA" or "3rd Party", then cell would show "Provided Date (If Applicable):",""
This is my best shot at trying to get it to work, but it wont, no matter what iteration I try.
=COUNT(IF(C9:E14="TERMINATION",IF(C9:E14="FORMS",IF(C9:E14="PWE",IF(C9:E14="TSA",IF(C9:E14="3RD PARTY")))))>0,"PROVIDE DATE (IF APPLICABLE):","")
I want to count the number of cells in a row containing a value greater than a fixed number but less than a number entered in another cell.
For example, in the cells A1..K1 I want to know the number of cells which have an entry greater than 7 but less than the value I have entered in cell M3. I have used the COUNTIF in other circumstances but I cannot find out how to use it with this "greater than and less than" condition?
I'm having trouble with the Rmdir statement.
I have tried ...
I'm trying to make an expression to calculate some fields in Excel 2007.
Trying to check fields up to a given number and give them a high multiplier, and add the rest of the numbers with a lower multiplier.
I have multiple fields with numbers in hundreds. I want to multiply the sum of this fields (up to 500) with 4. But the expression must also start the count from the first field, and when im hitting 500 as a sum I want the rest to be added with 2 as multiplier (the reason for the prioritizing of the fields is that they also have their own values/multipliers, but I'll skip that to avoid to much confusion here).
It's probably a lousy description. I'll give an example:
Field 1__Field 2__Field 3____Sum
100____300_____500______2800
How in earths name did I get that Sum you might ask.
((100 + 300 + 100) * 4) + (400 * 2) = 2800
400 is the number left from the third field wich I only want to multiply with 2 instead of 4.
Another example:
Field 1__Field 2__Field 3____Sum
300____600_____500______2000
((300 + 200 + 0) * 4) + (900 * 2) = 3800
900 is taken from the rest value of field 2 and 3.
If there any way of doing this "sum check and multiply, and then add the rest with a different multiplier"?
I want to apply regular expression for password
a) The first letter cannot be number
b) the password should have a combo of numbers letters and one special character
c) The password should begin with a big case letter
Here's what I need to do. I have a list of row numbers in AA2:AA6, so for i =1 to 5
I want to insert a page break before cell (column AA and Row(#in cell AA2-5))
what the long expression for the range being summed below.
Reason: I need to exclude some cell values.
I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
I am currently using a SUMIF statement as follows: =SUMIF(W1:W1500, "Charnwood" & "*", S1:S1500). The problem i am having is the fact that SUMIF doesn't work with a reference to a closed worksheet. I tried using SUMPRODUCT but it did not work. The reason it didn't work is because I need to find in column W anything with Charnwood and anything else past it, auch as Charnwood Estates. This is what I tried: =SUMPRODUCT((W1:W1500="Charnwood")*(W1:W1500="*")*(E1:E1500="S"),S1:S1500)
which of course is a syntax error.
Using Excel 2007 the below code causes Run Time Error '16', Expression too complex.
It works fine in Excel 97, 2000 and 2003.
why this would now cause an error?
My search on google only refers to this error in relation to charts.
I am getting 'Constant Expression Required' and this line of code highlighted.
Code:
Const sFile As String = ThisWorkbook.Path & "Survey.xlsm"
I need a nudge in the right direction here. I have found several examples on the board that have spawned some ideas but need a little more help.
I am looking to enter variables into 2 columns A and B and then run these variables through an expression.
I have already done basic cell math and want it to be cleaner by using VBA to run the calcs and then spit the answers back into the spreadsheet.
I think I have an idea as to how it need to function but do not know the exact commands to make it efficient.
I have been reading the different post here on the forum.
I have found a snippet of code that will open a pop-up message from a value of a cell. I would llike to do this on one of my reports. Here is the scenario.
Range (R5:S5) 0.15 them MsgBox "Note Must Be Entered For +/- 15% change"
But There are 14 different cells that I want this to work for. The value differs from day to day, and this should pop-up happen without a user having any actions other then the calculated value. But ones the note is entered in the "Notes: cell the message will not return.
Hope I explained it correctly.
The value of the cells in question are calculated in each cell with a formula example "=IF(ISERROR(Z20/H5),0,IF(ISERROR(Z20/H23),0,(Z20/H23)))"
I have a formula that has been working and it looks like this
=VLOOKUP(A13;'IFS export'!$A$1:$F$19000;4;FALSE)
Now I want to use the same formula in an other workbook and it gives an error
=VLOOKUP(A2;DRAWING!$A$2:$C$9168;3;FALSE)
When I type this formula I get the window that says
"This formula contains an error.
*For information about fixing common formula problems, press help.
*...........
*..........."
And if I go and try to change the first formula that has been work it gives me the same error.
In excel 2007 i need to do a formula...
I have the number of hours and de couste...in an other cell i have the expression that says that is paid or not....
I need only sum the cells that have the expression paid ...how do i can do this?...
I have this COUNTIFS expression buried in a larger formula:
COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1")
and it always returns a value of zero or some positive integer.
I'm now looking for a little "function" that I can wrap around this expression that will:
A) indicate any non-zero result as 1 or TRUE
and
B) indicate any zero result as 0 or FALSE
Something like...
AREYOUNONZERO( (COUNTIFS(Table[C7],Table[C7]&"",Table[C21],">=1") )
I'm sure there's a fairly efficient way to do this... but I'm totally stumped at the moment!
I have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows)
To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression.
I effectively have two types of conditional formatting that are very similar. I have the standard Yes, No & A/L which I wand to shade accordingly. I then have alternating columns of Yes , No , A/L . I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases.
I have built the following expression which does not seem to work.
=ISNUMBER(SEARCH("Yes",(ADDRESS(ROW(),COLUMN()))))
The two components of the expression work separately:
=ISNUMBER(SEARCH("Yes",P3))
=ADDRESS(ROW()-1,COLUMN())
Our company initial is TP, but sometimes it might be combined with a reference number, e.g TP5659
I would like the IF formula to stick pick this up..
I know if it was just the phrase 'TP' I would use =If(a1="tp"...
How do I express that I would like my If formula to work when something 'contains', not an 'exact match'
I'm trying to use the macro below (twice but with different variables) but everytime I get the error "Compile error: Constant expression required".
[Code]....
(Excel 2007) The issue I'm having is that a loop statement of mine is failing to recognize that a couple values are equal. It works for the first 3 iterations of the loop, then stops recognizing. I tried copying another set of numbers in the failing one's place, and that actually worked. I can't attach anything, so I'm including a link to a detailed screen shot with some notes on it and code that I'll host on my site. Not sure how else to explain the issue, but if you take a look at the screen shot you can see the excel sheet being worked on and the vba code running. I will also include a text file of the code
Here are the links to the screen shot and text file of my code
Text file
Screen Shot
I am using the following to check if a string has a sequence of 6 decimal digits in it. But am getting an error. If(str Like *######*). I have to check if str has values like 123456USA ; ABC725439 ; jh658478hd. I thought # represents a single digit and * represents any no of characters
View 2 Replies View RelatedReplace filename with variable in "Activecell.FormulaR1C1=" expression
I have recorded a macro which contains several lines of code like this one:
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I've some code that I use to scan HTML to find images. The basic part string I use is:
WildSought = "**"
The problem is that there are several ways to close an image tag. The above closes using /> but you could also use "> for example. Is there a way in my regular expression to say "this OR that". Maybe it would look something like:
WildSought = "*
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies View RelatedI am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.