Creating Looped If Statement
Nov 22, 2012
I am working on creating a looped if statement but can not get it working.
I have 3 columns:
D3:D50 = Application ID
G3:G50 = Sorted
H3:H50 = PasteCreate
Column D contains unique records, however the first 6 characters could be the same so I have used the Left statement (=LEFT(D3,6)) to sort by this in column G.
Application ID
Sorted
PasteCreate
ES0920.21
ES0920
[Code] .........
What I want it to do is to go through the list and Match the first 6 characters of cells in coloumn D:D create a new worksheet and name it the first 6 characters then copy (in the case above it would create 2 new worksheets that would be called ES0920 and ES1341.
ES0920 would have all the ES0920... names pasted into cells A2:R2 and ES1341 would have the 2 ES1341... names pasted into A2 and B2
The list of EStext will continually change. but will always contain ES followed by 6 or more numbers.
View 3 Replies
ADVERTISEMENT
Mar 2, 2007
I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.
Sub HTSoutput1()
'
' HTSoutput1 Macro
' Macro recorded 3/2/2007 by Jack Rosa
'
' Keyboard Shortcut: Ctrl+q
'
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"
This would have to be repeated for a column that contains 312 entries so that I end up with each destination worksheet containing a 13row x 8col table (or should it be called an array?).
View 9 Replies
View Related
Aug 9, 2012
HOW I CAN USE IN EXCEL VALIDATION DROP DOWN BELOW CONDITION
=IF(B1="Assets",[Mapping.xls]Assets!$A:$A,IF(B1="Liability",[Mapping.xls]Liability!$A:$A,""))
View 7 Replies
View Related
May 22, 2014
I am trying to generate a RAG status on an action log. Column K is the start date, Column L is the due/delivery date. I need a formula to bring through the following (i'll use conditional formatting to change "G" to green etc) -
show "R" if past due/delivery date
show "A" if past start date but before delivery date
show "G" if not past start date yet
This is probably really simple, but I've been trying various IF/AND statements and can get the "G" to work but not the rest for some reason. I think i may be using 'TODAY' incorrectly in my formula.
View 4 Replies
View Related
Nov 18, 2009
I receive spreadsheets each month in the same format. It is effectively 6 rows of titles, 7 rows of weekly data, 2 rows of weekly summary, 3 blank rows then it repeats itself with the 6 rows of titles etc... down the page for 8 or so weeks.
I wish to create a (looped) macro that will leave the top set of titles and the 7 rows of weekly data (Mon to Sun) then delete the next 11 rows and so on and so forth to the end. I will then be left with the intro to the dataset followed by a whole months worth of data in continuous format as opposed to weekly summarised sections. But I am stuck as my macro knowledge is basic and I can only record macro's not program VBA.
I can create a macro and manually delete all entries for one spreadsheet but would rather have a code to do this than can run (x) amount of times in case the size of data changes. When it deletes section by section, the cells move up and the row numbers change.
View 3 Replies
View Related
Feb 18, 2014
The code below works correctly on certain sheets. The code is supposed to loop through worksheets in an array, calculate the percent change from 1990 to 2012 and from 2005 to 2012, and put the calculations on the 4th and 5th row from the last non-empty row, respectively. All the sheets are identical except for 3. The sheets that are different only have a different number of years of data. For some reason this causes the macro to put the percent change calculations in random rows below the correct location. Also, the macro doesn't work correctly on one of the identical sheets.
[Code] ......
Example of how the macro runs correctly on an identical worksheet : correct.gif
Example of how the macro runs incorrectly on one of the 3 sheets that are not identical : incorrect.gif
View 6 Replies
View Related
Jul 2, 2006
the if stattement works perfectly and does exactly what i want except when it comes to the else part. if there is no error the statements are run perfectly but if there is an error (in this case the error is generated when a match cannot be found in the spreadsheet) the else statement doesnt kick in and post the msgbox.
the code just crashes. and returns an error 1004 on the line i have highlighted in yellow
res = WorksheetFunction.Match(invvar, Columns(1), 0)
If Not IsError(res) Then
Me.txtClientID.Value = ws13. Cells(res, 7)
Me.txtNumber.Value = ws13.Cells(res, 7)
Me.txtDate.Value = ws13.Cells(res, 8)
'save client id as a variable
'Print to invoice------------------------------------------------------------------.....................
View 6 Replies
View Related
Jul 28, 2009
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?
View 5 Replies
View Related
Oct 1, 2008
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 Related
Feb 14, 2012
I 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
View 9 Replies
View Related
May 5, 2014
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.
View 5 Replies
View Related
Jan 6, 2009
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.
View 9 Replies
View Related
Mar 18, 2014
I am looking to have one formula containing two If Statements that minus.
=IF(B6="investment",C6,0)-IF(B7="gross",C6,0)
So the result of If Statement 1 minus the result of If Statement 2.
What I am seeing at the moment in the cell is FALSE and what I want to see is the sum.
View 2 Replies
View Related
Jun 13, 2009
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
View 3 Replies
View Related
Apr 25, 2007
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
i.e.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
View 9 Replies
View Related
Jul 3, 2007
I have a problem with a formula inserted with control+shift+enter
The formula is
MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999))
In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.
Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.
The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.
I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
View 7 Replies
View Related
Dec 22, 2008
I am trying to build a selection for a procedure to use.
View 2 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Jul 27, 2014
What I'm trying to have happen is when I enter a cost ($50,000-$240,000) in Sheet 1, I want the fees (cell B2) to correspond to what they are on sheet 2. How should I set up an if statement for this?
View 3 Replies
View Related
Sep 1, 2008
How do I write an if formula for the below example. X = A Week Number. If(X is between 1 and 4,"Y","N").
View 2 Replies
View Related
Oct 13, 2008
Here is what I have right now, =if(i3:i133="MLB"&if(b3:b133="W",c3:c133))
Basically the statement needs to contain two "ifs". I have a column that has MLB, CFB, NFL, etc and a column that designates wins and losses. So, what I am trying to make it do is say if i3:i133="sport" AND if b3:b133="W" then add those up.
View 3 Replies
View Related
Dec 11, 2008
I have a sheet where A2 is machine name A3 needs to put a -1 if A2 is either Machine 1 or Machine 2. I looked and cant find what I need. I know this should be a no brainer but having problems. here is what I have
=IF(A2="Machine 1",-1,"") or IF(A2="Machine 2",-1,"") this doesnt work
I have also tried something I found
=IF(OR(A2="Machine 1",A2="Machine 2"),-1,"")
View 3 Replies
View Related
Jun 11, 2009
How do i say if x is not equal to #value! in a formula? In the context of...@search() returns #VALUE! when the search string is not found. So: =OR(SEARCH("price",$A4) **NOTEQUALTO#VALUE!** ,SEARCH("pricing",$A4) **NOTEQUALTO#VALUE!**)
View 5 Replies
View Related
Sep 8, 2009
=IF(ISBLANK(G9) or ISBLANK(H9),"",(G9+H9)/F9)). How can I get the above if statement to work?
View 4 Replies
View Related
Jan 5, 2010
If d6 and d7=0 then put 0 but if one of them contain any other value then roundup as below based upon the cell with the different value. I current have this formulae:
=IF(Parameters!D6=0,0,ROUNDUP((Parameters!D6*1000)/Parameters!D11,0))
View 3 Replies
View Related
Aug 19, 2008
I am trying to do and IF within an IF like i could do in java. Although i unsure now to whether this can be done within VBA and whether the syntax i am using is correct.
The situation is that i have to two cells with a list of information within them both.
For example cell (2, B) has options 1, 2 and 3 & cell (3, B) has options a, b and c.
If option "1" is selected then either option "a", "b" or "c" can be selected that will therefore show different information in another cell within the worksheet.
View 13 Replies
View Related
Nov 30, 2008
I want to create code that will check if a number of cells is empty and if they are to call a sub. If any of the cells are not empty then call another sub.
View 11 Replies
View Related
Mar 18, 2009
I am looking to get my vb code to do a simple thing, i need it to look at a date on my excel doc and to flag a messagebox that it cannot run due to date passed only if the date is exceeded and to stop at that point.
View 4 Replies
View Related
Aug 12, 2009
I have a bit of code in which I am trying to use the or statement. I've tried a couple of methods but neither seem to work. Is "or" a valid command in VBA?
View 3 Replies
View Related
Sep 15, 2008
In column "AN" I would like to put an If statement together something like
this: =IF(AK4="NO",AA4,AA4-AA4). The problem is that I get an "#VALUE" error if there is no data in column "AK" for which this formula is based. Ideally I need the "#VALUE" to go away even if the specific cell in column AK is blank, due to the fact that I need to eventually sum the totals in column "AN4:AN150", which the "#VALUE" error prevents me from doing.
View 4 Replies
View Related
