Incremental Numbering Based Upon User Input
I have created a macro that processes through information for the creation of checks.
I would like to have a way for the user to input a check number, and Excel to take that number, drop it into cell A2, then increment it and drop the next number in A3, and so on until all of the checks have been numbered. The process should stop at such a point as there is no more text in column B.
Here is an example. Let's say I have 35 checks to write. The first check number is 200. I would want a user box to pop up which asks me for the first check number. I would enter 200. Then the process would place the number 200 in cell A2 (A1 is part of the header information for the checks.) In cell A3, the number 201 would be placed. The final check number would drop into cell A36. No check numbers would appear in cells A37 and greater because cell B37 is empty, signifying that there is no further check data.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Delete Rows Based On User Input
1) Allow user input of a number between 1 and 999 2) Search column three and delete any rows that don't match this number. I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.
View Replies!
View Related
Updating Value In Table Based On User Input
I have one sheet to act as a splash page for user input. The second sheet stores all relevant data. My goal is for the user to input a numerical value in a cell (or input box) and then click a submit button. The code for the macro should look at a cell on sheet 1 which displays the primary key of the row in the table on sheet 2. Based on that primary key, it should look to sheet 2 and then insert the value into the table in the correct row and column. The column headings are the days of the week, and the specific day the user is concerned with is also displayed on sheet 1. Sheet 1: A1 = Day of the week A2 = Unique primary key A3 = user input value Sheet 2: table Rows=primary key (001 - 999) Columns = days of the week (Monday - Friday) example: user inputs '5' on sheet 1 with 'Tuesday' and '007' selected in their respective cells. I would then like 5 to be copied to B7 (row 7 for 007 and column B for Tuesday).
View Replies!
View Related
Automate Calculation Of Output Based On User Input
What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required) I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
View Replies!
View Related
Auto-Fill Data Based On User Input
Excel 2003, I have a list of products with corresponding prices located on a hidden worksheet. I have a spot on another worksheet where users can select a certain product and next to that cell there is a column for price. What I want excel to do is auto-populate the price cell based on what product the user selects. For instance, if the user selects OD2000N, then I want the price to auto-populate with the corresponding price that is found on the hidden sheet. Like I said before, I know this has probably been answered, maybe even mulitple times but I can't seem to find the appropriate thread.
View Replies!
View Related
Code To Delete Rows Based On User Input
I am using the following piece of code to delete unwanted rows from a worksheet: Sub DeleteDates() FinalRow = Cells(65536, 3).End(xlUp).Row For i = FinalRow To 1 Step -1 If Cells(i, 3).Value Like "*2007*" Cells(i, 1).EntireRow.Delete End If Next i End Sub What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted. However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007. My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range? Some important info: All of the dates are in column C, in the format mmm yyyy I would only need to narrow down to a month - so for example mar 2008 - jun 2008
View Replies!
View Related
Archive Data Based On User's Input
I am trying to move (archive) data from one sheet to another sheet based on a date range provided by the user’s input. If the date on sheet “list” in the cells in range R9:R10000 fall within that date range the following takes place. The rows that meet the date range per the user’s input in the range of R9:R1000 are copied and just the values are pasted into the next empty row starting with row 7 on the “report” worksheet. The cells that are copied on the “List” worksheet are NOT deleted but the contents are cleared. The reason for this is because there are formulas/links in those cells. I have attached a worksheet example and have some VBA code but it does not work.
View Replies!
View Related
Sum A Range Of Cells Based On User Input
I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
View Replies!
View Related
Creating Summation Formula Based On User Input.
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View Replies!
View Related
Copy Formula Across Based On User-defined Input Range
I have a cell B10 which contains a formula. I would like a macro which copies this formula across the same row. However, the macro should prompt the user to select a range. Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
View Replies!
View Related
Increasing Value Of One Cell Based On Another Incremental Value
I have cell F15 which is blank by default, and cell D14 which pulls a value from another sheet (D14's value is =Info!X20). For D14's properties I have it set to show thirds (Custom Property "# ?/3"). I want to make D14 increase by 1/3 for every increment of 60 that F15 contains. For example, let's say D14 is 12. If F15 is 59, it won't change. If it's 60, D14 will be 12 1/3, and if its 180, it'll be 13. I think I'm close, but just can't quite get it.
View Replies!
View Related
Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank
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 Replies!
View Related
Automatic Numbering Based On Last Number
I have got 2 sheets: - list (list of transactions) - accepted (accepted transactions). I want to make macro, which will: 1. select max value from sheet.accepted "B:B" 2. enter max value +1 in sheet.list "B8" 3. automatic fill down from B9 to the end of list with number increasing by 1 (in B9=B8+1, in B10=B9+1 etc.) I tried with this:
View Replies!
View Related
User Input Into A Range
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 Replies!
View Related
User Input To Multiply
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 Replies!
View Related
Macro User Input
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 Replies!
View Related
Validate User Input
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 Replies!
View Related
Time Fractions And User Input
What I am doing is creating a spreadsheet solution to tack race times. These show up in past performances in the following formats in paper copies: 45 2/5 seconds. The end user would have to put in maybe 30 of these times based on the number of contestants. Keeping in mind that the 45 2/5 number will be used in some calculations, I want to give the user the easiest input interface. Meaning instead of inputting like this; 0:45.4 (mm:ss/00 format), I would like them to just enter 45.4. the spreadsheet would input format this cell into a time format that would be able to have calculations done to it. Another example might be 1:35.60 (mm:ss.00 format) which is 1 minute 35 and 3/5 seconds. This would be entered as 135.6. A few questions first: 1) Would it be easier to leave the number inputed alone until the calculations ar finished then translate into a time factor? or 2) would it be necessary to convert into a time factor as inputted be cause of the 60 factor involved with the time factor? Having asked those questions, setting this all up with the exact scope I am working towards, any help or sugesstions as to the best way to proceed would be fantastic!
View Replies!
View Related
Sort And Print With User Input
I work in the Quality dept and I have came up with a gauge calibration workbook. I am in need of a command button to get an user input "date" and sort the data and print by that date. What I have now is you have to manually sort by the date needed and copy and paste to a worksheet.
View Replies!
View Related
Macros Waiting For User Input
I am trying to put together a macro that waits for the user to input data and then continue running the macro. All user input is for one cell and I am looking at more of an entire sheet worth of data. Here is the scenerio. I have one tab that pulls data out of a sharepoint list. I then have another tab that I dump data from another database into and then use vlookups to compare the data. The problem is I need it update the sharepoint list. Then wait for the user to dump in the new data from the external database before continuing the macro and working with the vlookups. Currently I am working this by running a few different macros to do it all, but I would like to condense it down to running one macro.
View Replies!
View Related
Prompting User For File Input
I want to do is prompt the user for an Excel file and then insert it as a new worksheet as sheet 2. Also, if possible I would like the imported file to insert to the right of sheet 1. Anyone know how to do this or at least know some parts of this problem.
View Replies!
View Related
Color Range With User Input
I have searched for code in the forum to help me here but could not get any of the possible choices to work for me. I have a spreadsheet that is password protected but would like the user to be able to change the color of the fonts based on the user's choice. The protected page (one of many in the spreadsheet) has various cells that are unlocked so that the user can enter data. The rest of the page is locked. I have allowed all users of the worksheet to Select Unlocked Cells only. I have attempted to allow them to Format cells as well, but each time the program is re opened this feature is no longer working. (The program has a macro that ensures it opens in protected mode each time.) So I would like to set up a Macro that allows the users to set the color of 6 rows and 5 columns based on their choice. An example of the configuration is: 1234Color Choice A11016221 B21117232 C31218243 D41319254 E51420265 F61521276 Using the above format, I am imagining the I would have a Key with color codes (using the ColorIndex Properties). The user would type in the appropriate number for each row and click a button and the rows (5 cells each) would change to the selected color.
View Replies!
View Related
Repeat Macro From User Input
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 Replies!
View Related
User Input For Current Path?
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 Replies!
View Related
Browse For File User Input
Does VBA support any type of "browse for file" interface? I'm thinking something similar to an inputbox, but instead it gives you the standard looking windows explorer window to find a file (think adding an attachment in outlook). The function would return a string.
View Replies!
View Related
How Do I Allow User Input In A Cell With A Formula
I have a table with 4 radio buttons that assume certain data in three cells when they are selected. I have used the IF command to getthe information to appear in these boxes when the cooresponding radio button is selected. I want the user to be able to either leave the selection as it is presented to them, have the option to change the data if it doesn't fit their scenerio. Example: button 1 "Electric" makes the data "$0.12", "$per kWh", & "100%" appear in the three cells. Each button produces different data in these boxes, however if when button 1 is selected, the user's electric cost per kWh is $0.10, I want them to be able to type that in without changing my preset default.
View Replies!
View Related
User Input And Cell Formatting
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 Replies!
View Related
Getting User Input For Find Macro
Due to the economy we are only scheduling days to work one week ahead which leads to alot of phone calls. The gaurds who get the calls then search thru a printed spreadsheet looking for a clock number or last name. I realized right away they should just look at the file on the computer and due edit - find. The problem is they due not use computers unless printing and they looked at me like I was talking in a different language. So I want to edit this macro below so it will allow user to type in search criteria instead of using same search criteria every time. Here it is 70039. I also need to place a shortcut or button to this macro on the toolbar or sheet and do not know how. How do I stop "remove macro or enable when file is loaded"?
View Replies!
View Related
How To Create A User Input Dialog Box
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 Replies!
View Related
Create A Cell That Allows The User To Input A Number
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 Replies!
View Related
Validating User Input Via A Message Box
I want to inform my users via a message box if they have not entered the previous month's information. The months are populated via a User form using a combo box selection. The months start from April through to March and are entered into the worksheet range ("aa3"). Data is entered monthly by the team. I don't know how to begin with this. I've managed to inform them when they've already entered that months information, but I don't know where to begin with this.
View Replies!
View Related
Copy Data From User Input Location
I'm using a worksheet as a surveying program. I need to be able to enter a cell reference in my "input cell", ie "=A11". Then in cell the the right of the input cell I need "=B11" to be entered. And in the cell to the right of that, I need "=C11", and to the right of the I need "=I11". I can get so far working with one cell at a time. If I enter "=A11" in T3, in T4 I can enter: =IF(T3=A11,B11,IF(T3=A12,B12,IF(T3=A13,B13..and so on. But 8 conditions is all I can enter before I get a message saying that the formula uses more levels of nesting than are allowed in the current file format. And I need to be able to enter at least 50 conditions in 3 adjacent cells.
View Replies!
View Related
Check Before Pasting User Input Information
i am writing a program that asks the user for an input and sets it equal to a variable. i want to look thru a column for this variable, if it is not in it, then paste it at the end of the column. if it is in already, throw up a dialog box saying its already used and exit the sub. i dont know how to set up this kind of thing up??? vlookup in combination with an if statement?
View Replies!
View Related
User Select Range Through Input Box
I'm trying to get a users' selected range to display in a cell as stored text. therefore, If a user selects A1:D7. The text in Cells("G", 10) will be:"A1:D7" w/o the quotes. I've attached an example workbook. Here's the formula thus far: Sub SelectRangeBox() Dim rnBody As Range Dim vaMsg As Variant Do vaMsg = Application.InputBox( _ Prompt:="Please enter the message-text:", _ Title:="Message", _ Type:=2) Loop While vaMsg = ""................................ Actuall maybe my thread should be Titled. user selected cell range displayed in input box save to cell as a text value.
View Replies!
View Related
Protect/lock Cells After User Input
Have a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work. How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet?
View Replies!
View Related
Create User Input For LARGE Function
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 Replies!
View Related
Array To Check User Input Match
This is what i want to do: SEE ATTACHEMENT •Write codes to pop up an InputBox to ask the user for a customer name. •The program uses the user’s input to check whether the name is on the list. If it is, display an msgbox saying that the customer name is on the list, and the corresponding cell will be indicated in boldface and in blue. Otherwise, an msgbox will be displayed saying that the customer name is not on the list. Dim the customer list as an array (string var. type) and Dim Found as Boolean. You will need the If-Then construction and For-Next or Do-While/Until loop too). Create a button to run and another to restore the formatting to its original style. This is what i did so far and still having problems with it: Option Explicit Sub customers() Dim Arr() Dim R As Integer Dim C As Integer Dim ReturnColumn As Boolean
View Replies!
View Related
|
TRACKER
