Making A Case Function
Nov 13, 2008
i am trying to get the following function to work
i want it to look at 2 cells, depending on the number in the second cell i want it to multiply the first sell by a specific number.
for exaple (Below) function in a3 looks at a2 sees it is a 2 and multiplies a1 by 1.09 shown in the code below
A1=load
A2=factor
A1 A2 A3
100 2 Function
Function Pmax(Load, Factor) As Double
Select Case (Factor)
Case "1"
Pmax = Load
Case "2"
Pmax = Load * 1.09
Case "3"
Pmax = Load * 1.12
Case "4"
View 9 Replies
ADVERTISEMENT
Dec 17, 2007
in making this macro non case-sensitive. So when a user searches a company name in the worksheet they do not require to type in the company name exactly as it appears.
For example; when searching Microsoft they can type "microsoft" and the macro would take the user to Microsoft.
I would also like to know if it is possible to add a feature that keeps the search dialogue open so the user can search the next possible match. If the user was to type in "mirco" and the search would show the user any company name with the word micro in it.
Sub Button3_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
View 9 Replies
View Related
Apr 24, 2008
I'v got a school task about making a excel worksheet.
But we have just started and i havent gotten all the functions to work yet.
The task itself is :
Make function that tests if D9 is larger then 100 and I9 is less then 50. If true "Kontroll" if not "OK" should be written.
Also, if "Kontroll" shows up it should have a dark green "OK " should just be black
Its roughly translated from norwegian. If you need some more information I will try to provide it
View 10 Replies
View Related
Jul 9, 2014
So I have a rather complicated IF, Countif, Sumproduct combo in a formula right now. As you can imagine this makes my worksheet run rather slowly. I was curious if there is any way to make this formula into a User Defined Function. The formula looks like this:
[CODE]
=IF(A15="","",IF((AND(COUNTIFS(J:J,J15,AM:AM,ABS(AM15),AD:AD,AD15)>1,
(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)
=ABS($BD15)),--($AX$17:$AX$10000=$AX15),$BD$17:$BD$10000))
[CODE]
I would not be surprised if this isn't possible but thought I would try. Another possible approach would be to make this into a macro and instead of the output being "Duplicate" or "Research" just have the "Duplicate" Cells Highlighted in Red and the "Research" Celss highlighted in yellow.
View 1 Replies
View Related
Sep 9, 2012
I am attempting to use the following code to move certain sheets to specific locations in my workbook. The case statement containing the InStr function isn't working eventhough the "Data" tab does exist and the InStr function does return 1.
Code:
ThisSheetToMove = Sheets(SCount).Name
Select Case ThisSheetToMove
Case "Schedule"
Sheets("Schedule").Move Before:=Sheets(1)
Case InStr(1, Trim(ThisSheetToMove), "Data") > 0
Sheets(ThisSheetToMove).Move After:=Sheets(SShtLast)
End Select
View 4 Replies
View Related
Oct 24, 2012
In cells A1:A45 i have all lower case names...how would i use a function to convert all to upper case
View 6 Replies
View Related
Nov 5, 2012
I have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.
Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"
[Code] ...........
View 9 Replies
View Related
Nov 2, 2006
is it possible to make a SUBSTITUTE finction non- case sensitive?
For example I want to replace all letters "e" and "E" in a cell.
View 3 Replies
View Related
Dec 10, 2013
Basically i have a list of data for example from B2 to B2000 for which i want to calc a standard deviation across however the number of data points in the standard deviation need to be dynamic.
For example in Cell a1 i can enter 150 it will then calc the standard div across the first 150 data points (points 1 to 150) then in the cell below calculate the next standard div.
Standard Div Points10
Data PointData ListStandard div
10.167442459
20.539073451
30.29740845
40.309440859
50.640742715
[code].....
View 2 Replies
View Related
Apr 16, 2002
I've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?
View 9 Replies
View Related
Feb 15, 2010
i have some numbers as data, i want to find the function that generate thiese numbers and also i want to view the chart of it, its kind of sine wave graph.
View 9 Replies
View Related
Aug 26, 2009
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
View 7 Replies
View Related
May 8, 2008
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
View 6 Replies
View Related
Jun 3, 2009
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
View 9 Replies
View Related
Apr 22, 2009
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies
View Related
Jun 20, 2008
When I use a simple formula such as:
=upper(a1)
that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.
What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet
View 9 Replies
View Related
Jul 20, 2006
For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....
View 9 Replies
View Related
May 15, 2007
I'm trying to compare two range values within a macro to see if they match...if they do/don't I capture and write some other data.
One list is lowercase, while the other list is UPPERCASE.
My current macro needs them to be in the same case because I'm using the following to compare:
If VPNID.Value = BuildHRName Then
How can I change the format of one of the lists to UPPERCASE or lowercase so that I am comparing apples to apples?
View 9 Replies
View Related
Nov 17, 2009
if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"
View 2 Replies
View Related
Feb 3, 2010
I am making a simple map in excel. I have my floor plan on sheet 1 and the location of product on the floor on sheet 2.
Currently I have some formulas on sheet 1 that link to some sales data on sheet 2, but what I would really like to be able to do is use the search function on page 1 and it give me the location of the products in a given area.
The data is broken up into group location on the second sheet.
View 11 Replies
View Related
Feb 6, 2010
I have four columns containing about 200 addresses.
A Names
B Streets
C Cities
D Phone no.
If I write a name in D1 I want to get the corresponding street, city and phone no. in E1, F1 and G1.
I also want to copy the formula downwards let's say ten times so I can make a list.
If I for example just want to make a list of three names (D1-D3) I want the rest of the fields to stay blank.
View 6 Replies
View Related
Aug 2, 2007
I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.
I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?
View 11 Replies
View Related
Feb 11, 2008
i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;
View 14 Replies
View Related
Mar 14, 2007
I have a spread sheet that tracks progress reports that are due every 90 days. I have a due date colum that will automatically get high lighted if the 90 days have passed to inform me or my staff that the report is late. I would like to have a colum with a button in each row that allows the reader of the spreadsheet to press "YES" for that accont's progress report having been turn in. When the "YES" button is pressed the date due date column advances to the next 90 day due date and the highlight would turn off.
I used the conditional format to change the due date column to Yellow if =TODAY() is less then or equal to the due date. But I can figure out a way to select something and turn the highlight off and advance the due date to the next 90 day due date.
View 9 Replies
View Related
Mar 30, 2007
I have been asked to make a formula that sums up the hours in a week and then tells me whether I am below or over the budgeted hours for that week!
I cant get it to work since I cant figure out how to have the same formula in all the cell that sums up early, mid and late shifts, ie 06-14, 14-22, 22-06.
The graveyard shift is a problem there since I get negative hours.
Further more, I need to have the formula ignore cells to be added up if it has letters in it, we write an R for Rostered day off.
View 9 Replies
View Related
Aug 20, 2008
............Beer........ Total
Stefan:....1........... 0,70€
Now i need a button that when i click on the name the amount 1 gets 2,3,4,5,6, ect and that it calculates the price up as a total
View 9 Replies
View Related
Jun 6, 2006
1- I 'm working with a table wit many Sheets.
I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)
2- then how to Zip this worksheet to zip file ( to save it)
3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?
View 6 Replies
View Related
Jun 15, 2006
I would like to:
1-Make Rows & columns 1/4" X 1/4"
2-Be able to enter dimensions into an input box that would then draw a square(or rectagle) with the inputed dimensions in INCHES.
View 3 Replies
View Related
Aug 29, 2009
I have 14 Case instead of multiple IF's
one by one 52 cells will be selected and will act on the 14 Case
Can I name the 14 Case as one and not have to end up with a mile long code?
Example:
Select Case Grade
Case Is >= 90
LetterGrade = "A"
Case Is >= 80
LetterGrade = "B"
Case Is >= 70
LetterGrade = "C"
Case Is >= 60
LetterGrade = "D"
etc...etc...
Case Else
LetterGrade = "Sorry"
End Select
would become:
Select Case test
End Select
View 9 Replies
View Related
Aug 21, 2012
In Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"
Is it possible to make Column C a required field if "No" is selected in column B.
So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.
Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.
View 1 Replies
View Related