Create User Input For LARGE Function

May 18, 2009

I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.

=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))

I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.

View 2 Replies


ADVERTISEMENT

How To Create A User Input Dialog Box

Nov 13, 2008

I have a macro that's running quite well and is cross-posted here:

[url]

What I need to do is allow for user input. I want to create a dialog box that pops up and asks for an input range, when the user runs the macro. Values can only be from 1 - 50. And the user won't be allowed to select broken sequences, like 6, 9, 12-15.

They would have to be continuous, i.e. 6 - 15, etc. I'm still very new to VBA. I can play with the control toolbox, but I have very little understanding of how to interface with the components that I'm adding/creating. Once I have that range, I want to use the inputs as the 'start' and 'stop' points for my loop. So I need to capture the values and pass them as input parameters, I'm guessing.

I can modify the loop to accept the inputs, I'm just not sure how to capture them from a user input dialog box.

View 14 Replies View Related

Create A Cell That Allows The User To Input A Number

Jul 30, 2009

I am working on a spreadsheet and would like to create a cell that allows the user to input a number (1 to 5) which will then equate to a percentage(%). I would then like this to deduct that percentage from a total number...

To try and explain this.. Should the user input 1 into the cell, this would then equate to 3% - this would then deduct from a total that percentage. Input 2 = 5% etc..

View 12 Replies View Related

Create Formula That Links To Another Sheet Based On User Input

Jan 8, 2009

I'm trying to create a formula similar to this:

=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)

The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.

View 6 Replies View Related

Create A Dynamic Array So That When A User Enters The First Sheet Name Via An Input Box

Nov 20, 2008

I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.

Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.

How do I make it so that it will select sheets if there are less than 4 sheets present?

I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?

View 5 Replies View Related

Index - Match Function To Output Data Based On User Input

Apr 30, 2009

I have a list of data that I'm using the index - match function to output data based on user input.

For example, I have:

1 10
2 15
3 20

So when '1' is selected another cell outputs '10'.

However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.

View 5 Replies View Related

Create User Defined Function With IF And AND

Jul 14, 2014

I am trying to create a function using the functions of IF and "And", but for any reason I don't know how to add the Add function

My function written in excel will looks like =IF(AND(A80="00",C80<>"8300"),"yes","no")

LOB Cost Center
00 1000
00 2000
00 8300
01 5000
02 8300

Function Allocation(LOB As Integer, CostCenter as Integer) As String
If LOB = 00 And CostCenter <> 8300 Then
Allocation = "Yes"
ElseIf LOB = 1, 2, 3, 4, 5 Then
Allocation = "No"

End If

End Function

View 3 Replies View Related

Create A User Defined Function To Search A Column Of Data For A Part Number

Mar 25, 2008

I want to create a user defined function to search a column of data for a part number.

If it exists I want to have a the UDf returna "fail" otherwise "pass"

Here is the code I was trying to use

Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
Else
firstpass = "Fail"
End If

End Function

This function only returns a "#value" and I don't quite know how to troubleshoot it.

View 9 Replies View Related

Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank

Feb 9, 2010

I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.

What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)

View 2 Replies View Related

Check A User Input To See If The User Has Entered The Date Correctly

Feb 17, 2010

I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.

View 2 Replies View Related

Input Box To Create And Input Info Into A Formula Which Then Populates A Cell With A Value

Jul 24, 2014

I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:

Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)

Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:

=(NUM1*(NUM2*$H10))/$H10

So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.

View 9 Replies View Related

How To Input Formula Down Large S/s??

Nov 25, 2008

I'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.
The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.
I attach short s/s to illustrate my need.

View 2 Replies View Related

Large File Taking Long Time To Close - No Interaction From User

Apr 18, 2012

I have a large file that takes a long time to close.

I would like to display a message that says " SAVING - PLEASE WAIT".

But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.

How can I do this?

View 7 Replies View Related

Create A List From A Large Table

Nov 21, 2009

I am trying to do is create a list from a large table (see attached). I have 2 columns (A and B) listing 'Asset' and 'Area' and then 2 rows (1 and 2) listing 'type of task' and 'task'. This forms a table with entries in the cells (from C3 onwards) denoting frequency of each task at each location, i.e. daily, monthly, 6 monthly and annual. This gives me quite a large table which is quite confusing. Can I create a formula to sort all the daily activities which will give me a list of the 4 corresponding properties 'country', 'location' (in columns A and B) and 'type of task', 'task' (in Rows 1 and 2)? Then do the same for the different frequencies populated in the table, 'monthly', '6 monthly' and 'annual' ???

View 7 Replies View Related

Wait For User Input

Jan 24, 2014

How do I pause my macro until the user selects a chart in the worksheet? Or Keep prompting a message to the user till he selects the chart and when a chart is selected run the macro.

Step 1: Check if a chart is Selected
Step 2: If yes, run the macro
Step 3: If no, prompt the user with a msgbox of Retry/Cancel.
Step 4: If the user selects Retry, wait till he makes a selection.
Step 5: If the selection is a chart, run the macro. If the selection is not a chart prompt the same msgbox again and keep looping it till he selects a chart.

Sub test5()
Dim chtSelected As Chart
Dim UserResponse As Integer

On Error Resume Next
Set chtSelected = ActiveChart

[Code] .....

View 2 Replies View Related

Macro User Input

Mar 28, 2009

I would like to do a Macro which requires user to go to a specific line which is variable in each work sheet. I would like to be able to enter the number and then delete all lines below that point and then continue with the balance of the macro.

Is there an easy way to pause the Macro and then restart it? Or is there a code that I can insert in the Macro to locate a specific Cell and then delete all lines below the next line?

View 4 Replies View Related

User Input To Multiply

Apr 15, 2009

In my macro, I'm trying to use an input box to determine the factor to multiply another cell by. I also want to only allow entry of 6.0 or less, and only allow, (but not require), a single decimal place. Here's what I've got so far, but it's not working:

View 2 Replies View Related

How To Autofill With User Input

Mar 2, 2012

I would like to make a button on my toolbar that will let me autofill whatever cell I've selected down to whatever row I designate. I would like to have a user input where you put in the row number and it autofills whatever formula down to that row in the column selected.

I know how to do a normal autofill, but I don't know how to do a user input or have it input into the autofill process.

View 5 Replies View Related

User Input Into A Range

Jul 14, 2006

This code works fine if i use static ranges, but i would like the user to input the column in which the data is stored.

'Get data for the locations of the gaming license numbers needed for the comparison
varPlayerHost = InputBox("Please enter a single letter for the" + vbCrLf + _
"Column that the Player Host License" + vbCrLf + _
"numbers are in.", "Player Host Number Location", "H")
varHostLicense = InputBox("Now enter the column letter for the copied employee" + vbCrLf + _
"license numbers", "Employee License Number Location", "U")

'Set the ranges for the data to be compared
'This is where I am getting the error???
Set rRangeA = Range([varPlayerHost,1], Range(varPlayerHost, 65536).End(xlUp))
Set rRangeB = Range([varHostLicense,1], Range(varHostLicense, 65536).End(xlUp))

'The actual comparison and deletion of record that match the license numbers copied.
'When this function finishes you will be left with a spreadsheet that only has patron
'information left for the patrons that have an invalid host number.
For Each rCell In rRangeA
If WorksheetFunction. CountIf(rRangeB, rCell) > 0 Then
rCell.EntireRow.Delete
End If
Next rCell

This exact code is kicking an error
"Run Time error 1004"
"Method 'Range' of Object '_Global' failed"
Not quite sure where i went wrong.

View 9 Replies View Related

Validate User Input

Aug 10, 2006

If anything else is entered into the input box a message box (MSGbox) with a vbCritical button will appear with the following prompt "You entry (insert entry data here) is inccorect." I don't know the code for "insert entry data here".

View 4 Replies View Related

How To Create Expandable Cells For Large Blocks Of Text

Apr 25, 2014

create cells which could be expanded/shrunk, to either show the first sentence or so of the essay, or the full essay. I'd need to be able to expand/collapse each cell individually, not by row or column as the tutorials I've found have shown. I'm pretty sure I've seen this done with a little +/- sign in the corner of the cell but I can't find the option. It would be a bit like on a website that have show more, show less buttons.

View 2 Replies View Related

Large Function And Corresponding Name?

Jul 14, 2009

I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.

I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.

I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.

View 2 Replies View Related

Need User Input Value Or Formula Value In Same Cell

Sep 15, 2014

I would like a cell to have user input. If there is no input then the cell should reference a value in another cell. My strategy was to use a formula in another cell to check to see if the user input cell was empty. If it was empty then the formula propagated the cell. If it had user inputted data then it stayed like it was.

Here is an example:
Begin Invent
1000
Gallons

End Invent
User input or
formula here
Gallons

Received

Gallons

Used

Gallons

I want the user to be able to input a value for the End Invent cell right below 1000. If there is no value I want that same cell to mirror the cell above it with the 1000 in it.

View 5 Replies View Related

How To Protect Cells After One User Input

May 2, 2007

I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.

View 14 Replies View Related

User Input And Cell Formatting

Dec 1, 2009

I have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?

View 4 Replies View Related

Default Cell Value Before User Input

Aug 14, 2013

I need a cell to display a default value (something like "Input Here") which a user can overwrite with a value. When the value is erased, the cell should return to the default. Preferably, it'd be great if the cell reset to the default value whenever the workbook is opened as well (i.e. won't save the user's input value).

View 2 Replies View Related

Log User Input In Successive Rows?

Feb 4, 2014

I am trying to build a user-friendly Excel database that takes the user's input from various cells and logs that data input by the user in successive rows to the side. Please see the screenshots of my current spreadsheet below:

Compressor Database.jpg

From the screenshot above, I would like for the user to input their data for the Date of Last Inspection, Current Hours, and check the boxes for any services they performed in the orange/pink "Input" box on the left. Currently I have the spreadsheet set up so that the orange/pink "input" areas recognize data put in to the yellow "Data Log" cells to the right. I am trying to get the yellow "Data Log" cell area to store the Date and Hours data put in by the user in successive rows like a database, but I am not sure how to do this. I was thinking of putting a button in the says "Log Data" which would, when clicked, store the data in the "Data Log" cells. I am unsure if I need to use a Macro or whether Excel has a simple, built-in function that can be used. Eventually I have plans for the "Date of Last _______ Change" Gray boxes, but would like to take this one step at a time.

I am not well versed in VBA and Excel Macros, and I am currently using Microsoft Excel 2007 (yes, I know it is old). I do have C# and Matlab coding experience, however I would like to do this in Excel to make it easier for the User as it is a well known program.

View 14 Replies View Related

User Input From A Supplied List

Jun 18, 2007

I want to present a user with a list that they can make a selection from and enter their selection in a cell. Sounds easy but.........

View 9 Replies View Related

Repeat Macro From User Input

Jun 14, 2008

I have a very simple macro for recalculating formulas and printing the page.

I need a way to show an input box where the user can say how many times to repeat the process i.e. 2x = recalculate, print, recalculate, print.

View 11 Replies View Related

User Input For Current Path?

Oct 31, 2008

I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.

I did a number of searches and I can't find exactly what I'm looking for.

I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved