i have a sheet that manipulates a variable amount of rows containing data input by the user. if a cell has an unexpected entry causing an error the program just halts with the default error message. the user then has to find the error which can take time. is there any way a message box can appear showning what row the error was found on ?
View 5 RepliesI have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
View 7 Replies View RelatedI 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.)
I am trying to create an input box where the user can only enter a whole number between 1 and 99. i can create the input box but am having trouble specifiying it can only be a whole number between 1 and 99.
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..
I have an excel sheet that I've automated for my shop ... Basically it copies a set of rows from one sheet, asks for which cell you want to start the paste at, then pastes it... Here's what I have so far:
[Code] .....
So I tried to use a variation of strReturn to indicate number of rows wanted but that didn't work ... Basically I'm going to create the "data" to include 50 rows, but have it selectable for the user to say they only want 5, 7 or 29 rows ...
I want to force my user to input a date in a userform. I have a normal Textbox and assumed i'd be able to set the properties to mean that imput had to be an integer and the format had to be "00/00/00" but I can't see how to do this.
View 9 Replies View RelatedDate
#
Lname
Fname
[Code].....
With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following
Row #
143
Name:
=C143&" , "&D143&" "&E143
[Code]....
How do I write the functions to keep the columns the same, but change the row number based on the number I input?
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
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.
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.
I have a spreadsheet of sales figures that I want to interrogate to show data relating to sales of specific manufacturers products to specific customers, I have a colomn (call it A) that has the customer name listed, coloum B that has the Manufacturer name, coloum C that has the type of product it is and coloum D, coloum E, coloum F, coloum G that has the sales figure for the last 4 quarters.
I would like a user input box to pop up on loading, which asks for the Manufacturer I want to search for, and then a report genertaed that lists each customer with their sales figures by type of product for the specified manufacturer for the last 4 quarters (individually not tottaled). If the customer doesnt have any sales figures for that Manufacturer I would like that listed showing '0'
I want to restrict user from entering alphabets like I,O,Q and S in a cell. Can we do this using data validation or any formula because I don't wan't to use drop-down list or refer to values in other cells either.
View 3 Replies View RelatedExcel 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 9 Replies View Related I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet.
Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
I've been going round in circles with this for hours and have got no-where. I 'm trying to get Excel to paste in a set pattern of data based on an input cell.
In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on
The user would enter the data in column A, starting in row 2
If the date in A2 = May 12 then Excel would enter 10 in cell C2, 20 in cell D2 and 30 in cell E2. If the date in A2 is changed to June 12, then the previous data would be cleared and Excel would enter 10 in E2, 20 in F2 and 30 in G2.
Date would be entered in A3, A4, A5 etc and I would want it to post the same fixed data depending on the date the user enters in teh relevant row.
I've tried IF statements and also some code but can't even get near it working.
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
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.
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 Relatedhow to input a data/number into excel through vba userform and get another data/number from excel back to the userform??
View 8 Replies View RelatedI want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.
View 1 Replies View RelatedHow can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample
View 4 Replies View RelatedHow 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] .....
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?
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 RelatedI 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.
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.
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 RelatedI 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.