I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!
1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under
blanks or zeros = unknown
I'm having an issue trying to locate a certain value in a cell.
If it is a number, say 50, then my code works, if its a word though
then it doesnt work. How can I get this to search with a list of words rather then numbers?
Sub alpha()
Dim cell As Range
For Each cell In Range("A2", ["N27"])
If cell.Value = Beff Then cell.Interior.ColorIndex = 6 'doesnt work
If cell.Value = 50 Then cell.Interior.ColorIndex = 6 'does work
Next cell
Range("A2", ["N27"]).Font.Bold = True
Range("A2").CurrentRegion.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub
It's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.
I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.
Code:
Option Base 1
Dim c(6)
Sub calc_averages()
[Code]....
I'm having some issues getting a tool I'm working on to function correctly. I have some cells, the number of which can vary based upon user requirement, that contain cell addresses. What I need from VBA is to translate these addresses into usable row, column values for the user defined range. Example:
Cell D4 contains F14, which was input by the user.
What is the best way to pass this data through VBA. Right now I am currently using:
Code:
Dim strRange as String 'left out loop parameters
dim TempRange as Range
strRange(i) = Cells(4, 4) 'using TypeName on StrRange at this point yields a string result as expected
set TempRange = Range(strRange(i))
From my understanding that should work but I am getting type mismatch.
I do not have any reason for actually using a string, but would like the user input to only be 1 cell. If there are cleaner ways around this, I am open to them.
Then from the range, I would have two other variables as type long for storing the row and column data using:
Code:
Pathing_Rows = TempRange.Row
Pathing_Cols = TempRange.Column
Using the SpecialCells method with a Type = "xlCellTypeFormulas", I can identify the range contains cells with formulas.
But when there isn't any of these cells in the range you've applied the SpecialCells method to, you get a VBA Error 1004. (Doing this outside VBA gives you a "No cells found" box.) How can I skip over this error?
I'm looping through each sheet in the workbook, and defining the UsedRange. I'm doing another loop that uses the HasFormula property on each cell and then performing an action if HasFormula = True, but this results in a lot of extra work processing cells that don't have formulas.
I'd like to further refine the process by defining the range of cells on each sheet that have formulas. But if a sheet contains no cells with formulas, I'd like to skip past it and move on.
I think this is just a simple syntax / code construction question.
I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.
View 3 Replies View RelatedI am trying to define a dynamic range based upon error criteria. After the first error, all the subsequent cells are filled with the error and I would like to limit the named range to the rows with no errors (one column wide). I am thinking something similar to (realizing this is probably very wrong):
=OFFSET( 'Basin Routing'!$X$5,0,0, COUNTIF('Basin Routing'!$X:$X,AND(NOT(ISBLANK()),NOT(ISERROR()))),1)
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)
)
I have 2 colums that represent the height and width of a paper edges. And now I would like to find a formula (somekind of an IF?) which would calculate automatically (taking account the values in these colums) which of these 2 measures make together ISO paper sizes A0 (84,1x118,9), A1 (59,4x84,1), A2 (42,0x59,4), A3 (29,7x42).
View 2 Replies View RelatedI sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.
The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.
VB:
Private Sub ArttotKnop_Click()
Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant
iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....
Since my table grows larger every day I want to use variables.
Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable
Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))
They don't work.
I'm trying to make a chart viewer on the first sheet of my spreadsheet and am using this website as a guideline, [URL] However instead of the 3 charts in the example, I have 12 which makes my formula for defining a name substantially longer.
By moving my charts to the same sheet and getting rid of the INDIRECT formulas, I shortened my formula to 291 characters which is too long as the limit is 255.
=IF($G$9="Sodium",$N$34,IF($G$9="Manganese",$N$35,IF($G$9="Iron",$N$36,IF($G$9="Nitrate Nitrite",$N$37,IF($G$9="Nitrite",$N$38,IF($G$9="Nitrate",$N$39,IF($G$9="PH",$N$40,
IF($G$9="Conductivity",$N$41,IF($G$9="Turbidity",$N$42,IF($G$9="Fluoride",$N$43,IF($G$9="Selenium",$N$44,$N$45)))))))))))
Is there any way to make my formula shorter? Or to reference a cell where I place the formula?
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.
=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)
=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)
Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?
View 12 Replies View RelatedI have a sheet containing several charts. I have a script which I found on this site that helps me create dynamic min and max values for the axis.
Since I have several charts it seems that I need to define which chart that I want to change the axis.
This causes an error.
I have a form in excel that people are going to populate, then a button which runs a macro which copies all the cells out into a new line in a different worksheet inside the spreadsheet. im going to use that sheet to formulate graphs etc, the macro works however it overwrites previous data, im assuming i need some function to calculate which is the next empty cell then use this to to copy the data to
I am starting to write a macro and first thing i Need to define is all the columns that I need in my report. I started out by defining an array and this is what I have so far.
I need that every time I run this macro it should vlookup the data from cleantxt files that are saved in my c:autofile ReportsFC Banded Summary and essentially create separate excel files for different buyer names and save those sheets to my C:Banded Summaries with the name as Bandedsummary(todays Date)_BuyerName
This is going to be the bones of it then I have to figure out how to vlookup data for all the columns and do sumifs and format it.
This is what I have so far:
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
[Code]....
Defining Cell Name when Dynamic?. Here is whare I am.
View 3 Replies View RelatedI have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.
I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.
I tried to run this code and encountered an error telling me that N should be an object.
N has to be a variable number representing the amount of sheets in the workbook -2.
Does anyone know how this is written?
I've got a spreadsheet that could have 1 of 3 possible print ranges:
PR1 = B2:K67
PR2 = B2:K132
PR3 = B2:K197
If there is a value greater than "0" in Cell B68, the print area would be PR2, but if there is a value greater than "0" in Cell B133, the print area would be PR3, otherwise it would be PR1.
These print ranges are such that the page break would would be below B67 and B132, but I'm concerned that I may need to set these page breaks in the Macro.
I'm not sure how to use the IF, THEN, ELSE, in the Macro to test the value of the cell and then set the print area.
I'm trying to make ranges in a SumProduct formula dynamic, but getting errors #N/A. I think this is because the top two rows are headers, throwing off the range count.
Q. How do I adjust the range definition to compensate., e.g.
Range =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) where column D is numeric, and
Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1),1) where column T is text?
I had assumed that the offset value 3 would do this, but I suspect the functions count and match are not doing so.
once bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:
Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date
(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')
Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate
If I have a column of data with a defined name is there an easy way to update this as data is added? Can this be done when you open the workbook and without? I'd assume an open event could do it but I'm having problems finding a simle solution.
I have to enter a 2D array by hand into code because I am doing it in PowerPoint VBA, but I can't find out how to enter a giant array manually.
I know I could do it like this:
MyArray(0,0) = 1
MyArray(1,0) = 2
MyArray(2,0) = 3
...
MyArray(99,99) = 10000
But obviously that would be a giant mess.
Couldn't I just define it like:
{0,1,2,3,4,5,6,7,8,9,...,99,100;
101,102,103,...,199,200;
...10000}
What the code does is that it runs in a Master workbook, and searches for XLS files in a defined folder. It opens a file, count, closes the workbook and takes the tallied number of sheets and places it in the master file, column A being File Names and column B as Number of sheets. What I need is for the code to take the file name from column A, run it and then place sheet counts in Column B and then move down the columns until all the counting is done. Currently I am only able to define one row using the range function and i'm at a loss.
'Get file names and enter into Master
Public Sub ShowFolderList()
Dim wb As Workbook
Dim ws As Worksheet
ChDrive "C"
ChDir "C:Documents and SettingsTest Folder"
outrow = 2
Filess = Dir("*.xls")
While Not Filess = ""
Cells(outrow, 1).Value = Filess
outrow = outrow + 1
Filess = Dir()
Wend.......................
I have created some code of which this is an extract
Dim i As Variant
i = Array(37, 38, 41, 42)
For Each i In i
'Some Code
Next i
the routine works fine when the i variable is hard coded, but once the above is included it fails (Error 10 This array is fixed or temporarily locked).
It extracts Employee ID's and there Names from our database and lists them in columns A:C
What I want to do is list the ID's manually and get it to extract only the Names for the ID's I listed in Column A...
I am not sure how to amend this code to look at ID's listed in column A instead of extracting the whole database.
I know I need to add a "WHERE ID =" clause in the SQL but not sure how to say WHERE ID = A1, A2, etc.
I'm trying to define some ranges with a loop but being a newbie at VBA I'm finding it difficult to solve the error in the code.
First, as the data area is uncertain (variation in the number of rows and columns) i look to the end of the entire data area. Then i want to define intervals of 250 rows with a 30 row difference between each of them. Here is my
I am trying to perform certain actions on just some worksheets in a workbook. I realize this is probably a simple thing, but I've been unsuccessful in my search of the web so far.
What I'd like to do is define the worksheets (i.e. "in worksheets" below) in the below code to only include a list of worksheet names (which I can provide - ideally via a list in the worksheet in Excel, say in cells A1:A15 of a tab called "Tab List".).