IF Function Only Works With 7 Different Selections
Aug 26, 2002
I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.
Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)
View 9 Replies
ADVERTISEMENT
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Dec 1, 2008
I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).
I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".
I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.
I have attached a sample of the offending spreadsheet. The rounding function is in column "B"
View 10 Replies
View Related
Dec 28, 2006
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.
And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.
So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1
But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6
View 9 Replies
View Related
Aug 15, 2006
If you run the Int function on a product of two variables, it will return a different (wrong) result if the variables are both defined as single:
Sub roundingtest()
Dim a As single
Dim b As single
Dim cases As Integer
a = 18200
b = 0.01
cases = Int(a * b)
Debug.Print cases;
End Sub
this returns 181 instead of 182. If you define one of the variables as double, then it works fine. Is it just me?
Anyway, I found it to be quite useful, as i was trying to get a function to round to the first higher integer - as opposed to the first lower. so
182.1 -> 183
182.9 ->183
182 ->182
So weirdly enough, int(a*b)+1 does the trick! Of course if a or b is defined as a double, then it all goes to 182. So without any IFs, this works real nice!
View 4 Replies
View Related
Aug 24, 2009
I have a column, where i want to calculate the difference between today and another date/or viceversa. The problem is, that in the first two cells, it calculates it well, but then, it shows #VALUE and when I press to see the "calculations steps" , it shows "29/09/2009"-40049. It turns the today function into a number. Why?
View 11 Replies
View Related
May 20, 2014
This is a function to add the ascii values of a string to give a single value. Initially I wrote this as a Sub routine and it worked fine, but when making it into a function, I get this error. From a little research it appears that you can not use a string as an argument for a function call, yet that defeats the object of this particular function.
The intention of this function is that it works on a name in a given cell and the value (an integer) that is produced is then displayed in another cell, or if using it as a formula, the cell the formula is in.
I simple wrote the work "Hello" in cell A1 the ascii values of these added ignoring any spaces give 500 which is correct.
View 3 Replies
View Related
Mar 25, 2013
I've been working on a payroll program for my small biz. I'm close to done BUT...
When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.
Columns:
Gross Fed FICA Medicare State Net
F G H I J K
Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.
Code:
=INDEX(SingleWH!C$6:M$140,MATCH(G7,SingleWH!A$6:A$140,1),MATCH(E$1,SingleWH!C$4:M$4,0))
This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").
My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
View 8 Replies
View Related
Jun 21, 2008
i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error
The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.
I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.
PHP
Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double Dim I As Integer, Slope As Double, NData As Integer NData = UBound(X) For I = 1 To UBound(X) - 1 If (X(I) = X0) Then Interpolate = Y(I) Exit Function ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1)) Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1)) Exit Function End If Next I End FunctionPublic Function ListMax(ParamArray ListItems() As Variant) Dim I As Integer ListMax = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) > ListMax Then ListMax = ListItems(I) Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant) Dim I As Integer ListMin = ListItems(0) For I = 0 To UBound(ListItems()) If ListItems(I) < ListMin Then ListMin = ListItems(I) Next IEnd Function
View 10 Replies
View Related
Jan 27, 2009
I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.
Sub B_Test()
Dim myRange
Dim Results
Dim Run As Long
myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown))
Range("M3") = Application.WorksheetFunction.Sum(myRange)
myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown))
Range("D3") = Application.WorksheetFunction.CountA(myRange)
End Sub
I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.
View 2 Replies
View Related
Jul 7, 2013
I am trying to take two random Mouse click selections and swap a set of ranges associated with the cells that are selected. For example if the user clicks on A1 it will resize(3,22) and store the selection as a variable to be swapped with another selection. Here is what I have so far but I keep getting a object required error on rngEmp1.
VB:
Sub SwapGroup()
If Selection.Cells.Count < 8 Then
MsgBox "Please Select two Groups to swap. Press and hold 'Ctrl' in between your selections", , "Swap Groups"
[Code] .....
View 4 Replies
View Related
Jan 25, 2008
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name
Cell B - Monday
Cell C - Tuesday
Cell D - Wednesday
etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
View 5 Replies
View Related
Aug 12, 2014
I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
View 5 Replies
View Related
May 19, 2014
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
View 9 Replies
View Related
May 8, 2008
I'm trying to use combobox1, say, department, to filter items listed in combobox2, say, employees. I don't want all employees from different departments listed in the combobox2 so as to make the user spend time looking, but only names of a particular department once that department is selected in combobox1.
View 9 Replies
View Related
Apr 19, 2006
I have a userform with multiple buttons and a listbox. When any button is clicked on, a listbox is created. The user selects various items on the listbox. Then the user clicks another button and selects other items based on a new list. If user goes back to click the original button, the original list shows up, but the selected items are not highlighted. Is there any way to keep those original selections highlighted (selected)?
View 9 Replies
View Related
Jun 13, 2013
What I'm trying to do is to select multiple groups of cells (say 5x4) which are below each other, but then paste them next to each other
For example, copy cells A1:E4, A8:E11, A15:E18 and paste them to A20:E23, A23:E26, A27:E26
View 2 Replies
View Related
Dec 15, 2008
On one sheet I have a long list of items which I need my users to scroll down and, when needed, fill in a value next to the appropriate item in the list. Most of the list items will not need anything inputting against them. Then on a different sheet I need a nice tidy summary list which only shows those list items which have an input against them.
The bit I can’t do is getting these few items to display (on a separate sheet) in a nice neat all together, contiguous way - ie with no gaps. Also I need to populate this summary list automatically. ie after each entry.
View 2 Replies
View Related
Mar 26, 2009
how to track selections from a dropdown menu, I have 8 selections under the dropdown that are being selected from a list. Next to the list I want to have the total of the number of times it was selected on the dropdown. I've tried the data>subtotals, but It only seems to create 0's under the dropdown box.
I dont want the totals in the dropdown box I want it next to the list of 8.
View 3 Replies
View Related
Sep 18, 2009
I have not used Inputboxes or Userforms much. I have read through some of the messages here, but I need to learn more about them. I need to create some type of user interface, where the user would be asked to select between two choices. Based on the choice made, a macro would be executed. The two choices run different macros.
I need direction or an example of an Inputbox that shows two choices instead of a blank input space.
View 3 Replies
View Related
Oct 13, 2009
This should be very easy, but I am not getting it to work. I have a userform with a combo box and I just want to hard code the values to it in VBA. I want the value in the combo box to be 1 - 10. Here is what I have so far.
View 3 Replies
View Related
Nov 15, 2011
Any way to sort by more then 3 selections.
I want to sort by Date, Team, User, Item, then Sub iTEM.
View 9 Replies
View Related
Jul 10, 2012
We currently have a macro that has a limitation that it only works on 8 sizes, and only upto 12 Colours. We want to change this to make the number of sizes and colours totally variable. how to change this bit of code below so that the cells selected are not fixed but are variable depending on the number of sizes and colours as entered in sheet 2.
Current Code for When 8 Sizes and 12 Colours
Code:
Sheets("Sheet2").Select
Range("B30").Select ' This is Fixed
Selection.Copy
Sheets("Sheet3").Select
Range("G2:G109").Select ' Want this to be variable based on the number of rows needed starting at G2, this can be found out from Sheet2, Cell G28
ActiveSheet.Paste
[code]...
On Sheet 2 Cells G23 is used to enter number of Sizes, and G24 is used to enter number of colours. Cells G25, G27 and G28 then have formulas to work out other values that will be needed in the macro.
View 1 Replies
View Related
Jul 19, 2013
I am working on a macro, which should cycle through a column and find all rows with a given value. Then for these rows it should paste values from other columns into another worksheet.
Example:
1 2 3 4 5 6
Find all cells in row 6 with "yes" and paste the value from row 3 into another workbook.
Issue:
I cant seem to get the the value from row 3.
ActiveWorkbook.Worksheets(datasheet).Cells(rowtocopy, 6).Value.Copy has an error. Currently "Object required". Ive tried: .cells().value.copy that gives me a different error.
Script is started in the workbook where the extracted values should go.
Below its called "Template (2)".
The workbook with the rows is called "Datasheet" (which may be "Volume" or "SNiC data" depending on what is written in Worksheets("Template (2)").Range("B4").Value
Code:
Sub copychanges()
'################ EXTRACT ORIGINAL PAYMENT DATA
originaldata:
Dim currentwb As String, SuppXls As Excel.Workbook, datasheet As String, i As Long, targetcolumn As Range, modcorrect As String, fromcolumn As Range, rowtocopy As String
currentwb = ActiveWorkbook.Name
SuppPath = ActiveWorkbook.path & "" 'file path (assuming all file are in the same folder
SuppName = Worksheets("Template (2)").Range("D4").Value 'filename of corrected file
[Code]....
View 2 Replies
View Related
Jul 25, 2007
Sheets("Data").Select
Range("F5").Select
mycheck = ActiveCell
Sheets("Data in").Select
Selection.AutoFilter Field:=10, Criteria1:=mycheck, Operator:=xlcontains
xlAnd
The problem is that I have a value that is collected and put in to MyCheck. This value is only contains a few letters that I want the autofilter to filter.
e.i: The value i want to filter contains following.
01jan2007
What the autofilter is supposed to do is to show all cells that contains the value 01jan2007.
The cells that are supposed to be autofiltered looks like this.
2143 01jan2007 and so on...
I can not use "=" course the value is not the same as the others.
View 9 Replies
View Related
Dec 22, 2008
I execute custom Insert and Delete macros whenever when the user has selected a single contiguous range of rows to insert or delete.
But I don't know how to detect if the user has selected multiple, non-contiguous rows. Is there a way to detect this situation, and if so, is there a way to "loop" through the individual selections?
View 9 Replies
View Related
May 2, 2006
On a multiselect listbox, is there any way the user can select a "block" of selections, instead of having to select them one by one? The list box I have has about 100 choices and it would be possible to select blocks of choices at a time. This is similiar to MS Outlook email where you can push the shift key to isolate a block of emails to move to a different folder.
View 2 Replies
View Related
Jul 31, 2006
I have a workbook with 3 listboxes: Listbox1, Listbox2 and Listbox3. Selecting an item in Listbox1 will query a database which populates a hidden worksheet. Listbox2 then populates itself by drawing on entries in the hidden worksheet. Selecting an item in Listbox2 populates Listbox3 in the same way.
At the moment I have the spreadsheet so that nothing is cleared when I close or open the workbook, allowing the listboxes to remember what they were populated with when I closed the workbook. However, the listboxes will not remember what was selected when I closed it. Is this something which I can easily fix with a listbox property? If not, what is the easiest way for each listbox to remember what was selected when I closed it?
View 6 Replies
View Related
Sep 25, 2006
updating validation selections. I have attached an example of a bigger worksheet I use.
Sheet data: this sheet contains the raw- date. Data will be added and changed continiously.
Sheet pivottable: this sheet is used to sort the drowdown list and filter out double entries. A Dynamic named range is set to the pivottable
Sheet form: in this we will choose a code by a dropdown list and the column data 4 is filled by a vlookup formula.
If the data is changed in the sheet data and we’ve refreshed the pivottable, the code on sheet form contains previous data, so the vlookup formula can’t find the data (resulting in n#a depending on using a ISERROR formula)
The following solutions I’ve already tried:
- A userform with a lisbox/combobox in combination with listindex and an index formula. The problem is that when I sort the pivottable the index and data do not match.
- I also used the information in this thread but this didn’t work.
Maybe I have to change the construction of my worksheet, but it is important that it works.
View 8 Replies
View Related
Oct 9, 2006
I'm trying to see if it's possible to create a list box where I can select multiple values. For example, if a create a list box with the alues, "ant", "dog", and "cat". Is it possible for me to give the user the option to choose, "ant" and "cat"?
View 2 Replies
View Related
