If Statement With A Range Selection
Aug 9, 2007
I was making a simple spreadsheet as I'm taking practice tests for some IT certifications and the spreadsheet I was making was to chart out my progress.
I had a range of scores in some cells and one of the formulas I used so that I didnt have errors in the sheet like div/0 went like the following:
=if(b5:b50="","",sum(b5:b50)) this seemed to work really well for the one section, however I tried to get a little fancy as always and I think I messed something up. Beneath that I tried to separate each practice test with the same sort of formula resulting something like the following:
=if(b30:b40="","",sum(b30:b40)) this however just returned an error #value. I dont understand why the first formula worked and the second didnt. Then again, I'm not really an excel guru, back in college I was really good with it but I dont really remember much.
View 9 Replies
ADVERTISEMENT
May 6, 2007
I have a spreadsheet using drop-down boxes with data from a data sheet.
What i need to do is, if 'A' is selected in "A3", then in "B3" i want a drop-down box to select "A only" compatible values, whereas if 'B' is selected in "A3" then i want "B only" compatible values to be used in "B3" list box/drop-down box.
E.g. if "A3" is 'select transport' then if the value in A3 chosen is "Car" then i only want "4 [x] wheels" in drop-down "B3" cell.
View 9 Replies
View Related
Jul 22, 2009
A question regarding "AboutRangeSelection" procedure on Chapter 11 in the **, Excel 2003 Power Programming with VBA by John Walkenbach.
View 3 Replies
View Related
Jan 13, 2007
This is hard for me to articulate, so hopefully someone will understand what I'm trying to do.
I'm attempting to construct a formula in Excel, that basically says: IF the value is between these two numbers, multiple them by this percentage, or if the value is between these numbers, multiple it by this percentage.
So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.
I've tried a formula similar to: IF(C1 (Rng(A3:A2), C1*B3) ---- Meaning that if the number is between 10,000,000 and 5,000,000, multiple the number by 10%.
View 12 Replies
View Related
Jan 20, 2010
I am trying to figure out how to derive a range from a selection. Here is the code i am using to select the table.
View 5 Replies
View Related
Jan 27, 2010
I am using Selection.ClearContents command to clear data from column F and G. I know the last row as 230 but the starting row is the first empty cell which I find using Do While loop in range F31 to F230. The variable that stores the first empty cell number is 'r'.
Range("F &r:G230").Select
Selection.ClearContents
View 3 Replies
View Related
Feb 8, 2010
I am filtering a list in Column H and depending on what criteria I filter on I need to be able to select only what I have actually filtered for. I am using the code below to find the lower right corner of my range that I'm trying to select and this works great.
View 3 Replies
View Related
Mar 7, 2012
VBA IF statement that references the below code. the IF statement would say if the below selection does NOT match 'putname' then
Code:
Range(Selection, Selection.End(xlDown)).Select
View 2 Replies
View Related
Apr 8, 2014
I'm looking to go to every 8th cell in a column when I hit the enter key. I was thinking of selecting a small range say from A2 which is zero to A10 which would also be zero then A18 which is zero. The reason being I have a lot of figures to put in to each zero cell then I select "Series" to fill the cells in-between with the incremental figures between the two zeros which would be the difference between the two zero figures.
View 2 Replies
View Related
Feb 27, 2009
I'm writing a couple of macros which require selection of a range. The user could either select the range before executing the macro or if range has not been pre-selected, an inputbox should pop-up through which the user can select required range.
View 9 Replies
View Related
Aug 8, 2013
I am trying to loop through a range, checking for a cell to contain one of two characters and then put a value in a cell to the side (well 3 away) of it. Im getting an error for Type mismatch on line:
VB:
If CellContent = "h" Or "v" Then
the data in the cell is an along the lines of '12.34.56.43 som-thi-vh-ng1'
VB:
Dim MyRange, MyCell As Range
Dim CellContent As String
Sheets("Sheet1").Activate
[Code]....
View 1 Replies
View Related
Jul 23, 2009
I am trying to do a calculation based on the conditions of two cells but one cell I would need the range of the report. Either way, here is my current statement.
=IF(P2:P15 = "Green Building 15",SUM(COUNTIF(C2:C15,"Over AC")+COUNTIF(C2:C15,"Top Lab AC")),0)
I get a Value# error (though it systematicaly works if you check in the funtion area), and its because of the range I am using, is there anyway to bypass thiss issue or can someone give a better calculation.
View 4 Replies
View Related
Aug 28, 2009
I have tried over and over to get a point value from the percentages posted below but my formula will not return consistent results. Am I doing something wrong.
94% - below 1 Point
95%-96% 2 Points
97% 3 points
98%-99% 4 Points
0% or 100% 5 Points
Reference Cell is AG2 and is .99 but for some reason I return a 5
=IF(AG2=0,5,IF(AND(AG2>0,AG2<=0.94),1,IF(AND(AG2>=0.95,AG2<=0.96),2,IF(AG2=0.97,3,IF(AND(AG2>=0.98,A G2<=0.99),4,5)))))
View 6 Replies
View Related
Apr 7, 2009
I dont know how to nest and if (if that is the right way to go about it). this is what I have ....
View 10 Replies
View Related
May 2, 2012
Need all numbers in between the ranges set. So if I input for example 8765 then the cell still comes up with 18.
IF Cell E25 is 0-7200, then 12, If cell E25 is 7201-14400,
then 18, if cell E25 is 14401 - 21600, then 22,
if cell E25 is 21601-28800, then 28, if cell E25 is 28801 - 36000, then 34.
View 4 Replies
View Related
Jun 21, 2012
so on sheet 1 i have a list in column b (as below) and in column G I want to put a note "not included" if the cell matches what is in sheet 2:
AAA
BBB
CCC
DDD
EEE
On sheet 2, column A I have a list of items that I want to not include which I want to be able to add/remove/adjust over time. For example:
DDD
EEE
FFF
LLL
I can get it to put in the correct text into sheet 1 with the following but I don't know how to get it to look through all values on sheet 2 and then only enter the "not included" on sheet 1 if it is listed on sheet 2.
So far I have
Sub checksheet()
For Row = 1 To 428
For col = 2 To 256
[Code]....
This only does it for what is written in cell A3 on sheet 2 and does not keep going down the list in sheet 2.
I think I need to define the range better on sheet 2 and ask it to look through that, but im not too sure.
View 4 Replies
View Related
Aug 10, 2007
I'm trying to create an if statement that have 2 criterias. Here's what I've created:
=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)
This statement is returning the #VALUE error.
I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.
View 9 Replies
View Related
May 19, 2009
I'm trying to create an IF formula that returns the month as text between a certain date range in a different column.
For example
Between the 25-APR-09 and 23-MAY-09 I want to display "May" 24-MAY-09 and 27-JUNE-09 "June"
And so on so forth, the column with the dates in it is in date format.
View 9 Replies
View Related
Mar 21, 2007
I need a formula that will return "true" only if ALL cells in the specified range are not Null (<>""). I'd like to assign a value (% complete) only after all tasks under a phase have been given a completion date.
View 7 Replies
View Related
Feb 26, 2014
I have excel file and need to create a chart with X axis to be scalable, by selecting (from drop down menu) - start and end dates.
View 3 Replies
View Related
Nov 3, 2008
I may not have the terminology right, but here's my problem:
With the Range Select Formula i.e.
View 14 Replies
View Related
Dec 20, 2008
Here is what I'm trying to do:
1) I have column labels, and row labels on one worksheet which I input into
a function.
2) These inputs should be matched with column and row labels, and then input
into the index function to get a certain cell - let's call it the event cell.
I'm ok with this part. Then:
3) I would like to collect a one column array. The number of cells in this
array should depend on an input from a cell in the worksheet, and the final
cell in the array should be the column label of the column where I am
inputing the formula.
4) The array from 3) should go into the slope function.
5) Then I want to get the second array for the slope function from a fixed
column, but the same rows as in the variable first array, and this should
input into the function.
View 13 Replies
View Related
Nov 19, 2009
I have a spreadsheet with data organized into columns in sets of two - the first column is an X value (Pixel number) and the second column is the Y value (pixel density). I'm trying to make a macro that will select the first set of columns, chart it, move the chart to the next sheet, then select the next two columns of data, chart them and move the chart to the next sheet etc. I've gotten as far as having it make the chart and move it, but I can't seem to make it select the next set of columns - I've been trying different things for a couple days now.
Code:
View 4 Replies
View Related
Jan 30, 2012
I am trying to use Range.Find in a named range that has some areas where two cells are merged using the format "Center Across Selection"
Set rngTemp = wbMaster.Range("PnLDateRow").Find(what:=dDate)
I get a run time error 438 "Object doesn't support this property or method"
Is it not possible to use this method to find the cell in this range? I tried iterating through the range but that errors also.
View 1 Replies
View Related
Aug 27, 2012
I have to run reports every week where I show the number of days items are pending some sort of action. I use an autofill action in a macro but the number of rows changes every week. Here is the current section of my macro that I use. I just input a row number that I know will not exceed the number of rows required by my data (12900). What can I replace this with (Selection.AutoFill Destination:=Range("J2:J12900") so it will only fill the number of rows current filled in in either row I or K?
Range("J2").Select ActiveCell.FormulaR1C1 = "=R1C19-RC[-1]" Selection.AutoFill Destination:=Range("J2:J12900") Range("J1").Select Selection.Copy Columns("J:J").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=FalseApplication.CutCopyMode = False
I have a date in cell s1 that I subtract from the date in row "I". I then copy the result of row J and paste it as values.
View 6 Replies
View Related
Oct 2, 2012
Any easy way of finding out if deleting a particular sheet / range or selection will mess up any references elsewhere in the workbook?
View 1 Replies
View Related
May 22, 2013
Im having some problems with range selection in macros. Basically, what the macro does is Copy / Paste as Values in differet sectors of an active worksheet, so this is the code for each range of cells that I need to copy paste:
Code:
ActiveSheet.Range("C14:E15").Select
Selection.Copy
ActiveSheet.Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
The problem with this code, is that because it specifies EXACTLY which range to select, if I add a row before that range the macro will be now selecting an incorrect range.
For example, say I have a value on cell A5, and the macro is set up to copy/paste that cell. Later on, I decide to add a row in A3, so the value I would need to copy/paste will now be in A6, but the macro will still execute on A5 (incorrect, as it should now execute in A6, and that is what I have to manually correct each time).
View 3 Replies
View Related
Oct 13, 2008
I am looking to run a marco for a selection range, and the selection range could vary in size.
Using the Macro recorded (whilst turning on the relative reference) the Macro runs for a defined number of cells. I would like to run certain Macro, for different ranges of cells.
View 9 Replies
View Related
Oct 14, 2008
Here is my
ThisWorkbook.sheets("sheet1").Range("M").Select
For Each cell In Selection
If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then
cell.EntireRow.Delete
End If
Next cell
Range("a1").Select
End Sub
I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View 9 Replies
View Related
Feb 19, 2009
How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.
I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.
Private Sub ComboBox1_Change()
Dim ColNum ' This is the variable I want to change based on which month is called
If ComboBox1.Value = "January" Then
ColNum = 1 ' Column number for Column A- Where my January column is
Elseif ComboBox1.Value = "February" Then
ColNum = 2 ' Column number for Column B- Where my February column is
' etc. etc. for each month
End If
Call January
End Sub
Private Sub January()
' Just a quick msgbox to make sure the previous macro is calling this one
MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................
View 9 Replies
View Related
