Not sure if this can be done, but a colleague of mine is trying to record a macro which is not cell or area specific, but content specific. i.e :copy only if cell A3:A10 = "rabbit", then paste in another tab cell B7:B14.
View 2 RepliesI want to create some VBA code, but I don't know how to do it, that allows the user to make a copy of any worksheet he/she wants within a workbook and then name the copy of the worksheet whatever he/she wants. The copied worksheet should come at the end of however many worksheets exist in the workbook.
I want to accomplish this by using input boxes for the user. I want the user to click the macro and then the input box appears asking him enter the name of the worksheet he/she wants to copy. Then I want the input box to ask the user to enter name for the new copied worksheet. That's it!
A cancel button on the input would be nice just in case the user decides not to copy any worksheet.
I don't know if input boxes are what I want but you'll be able to tell me
When Excel is opened, I want a box to pop up where the following can be pasted:
"Trades
Merchandise: 100,000.000
Merchandise: 1,000,000.0000
Merchandise: 1,000.0000
Merchandise: 10,000.0000
Total: 1,111,000.0000"
The text and numbers will be copied from a webpage. When Excel is then opened, rather than having to type in those values (I'm only interested in the numbers), could an input box (or something else) handle and filter those lines to basically just the numbers?
I have 3 input boxes on my sheet. They are T10, T12, T14. Right now I click with mouse, but is it a way that I can use tab key to jump from one box to another?
View 2 Replies View RelatedI have the following (intense) formula
=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A5,{1,4,8,12,16},3),B5)))>=3
I was wanting to make either 3 input boxes or 1 if thats possible that basically you click and it captures the column you want the answer in, and then the two columns the data is in. It then places the formula down in that column with the answers based on what your input was.
I would want the range to be atleast 1 to 6000 rows (just in case)
How would I go about making a user form that has an input box where you can type in a year (in yyyy format), and then two radio buttons that allow you to choose either "Jan-Jun" or "Jul-Dec", and a submit button to run a corresponding macro.
The purpose of this is I have two macros that run the same report, one is for first half of the year, second is for second half of the year. For simple purposes we will call them "JanJun" macro and "JulDec" macro ...
Having problems with input boxes. I'm trying to creat 3 input boxes that will prompt the user to input 'Add1' in cell A1 'Add2' in cell B1 and 'Add3' in cell C1. Then return back to the first prompt box ready for further info - but this time, if it finds info in cell A1 it needs to move down to A2....and so on. Just about down the easy bit and have the three input boxes working - but unfortunatley that's it.
View 7 Replies View RelatedI have designed a spreadsheet to calculate time and speed averages of a ships voyage. When using a date and time value entered into a input box the value in the cell is reversed form the U.K date format to the U.S date format. this has been causing great amount of fustration to me. the cell has the correct date format i want and the excel system is configured to the U.K format. my program to allow you to look at it my knowlege is not very good of VBA. When entering the date in the FAOP from the drop down menu as dd/mm/yy hh:mm in is reversed.
View 2 Replies View RelatedI need some code to use text boxes to change the data in certain cells. Basically I want 3 text boxes to appear one after another. The first box should add text to cell T1, then 2nd to cell T2 and the 3rd will add text to cell T3. All these values should be added at numbers.
View 2 Replies View RelatedHow to set up a Inputbox to make the user pick the start line and end line for a loop
the sheet will have items on it numbered 1,2,3,etc I want a process done to the lines the user selects by item number not cell range.
line 4 to line 20 for example.
I am creating a spreadsheet where the user enters food they have eaten that day. The way this is done at the moment is with validated list boxes, one depending on the other using the indirect function. I want to be able to make this into a user input form. Is there a way of adding these validations from the spreadsheet, to an input form.
I want the input form to have an input for the day, food group, food item, and quantity of the food. The food item needs to depend on the food group.
> When the workbook is opened I want a box to appear with a message and 4 choices (as buttons?).
> Based on the button clicked I want 1 of 4 new boxes to appear (replacing the first box) and ask for input values.
>I then want the inputs to be written to specific cells on a specified worksheets inside the workbook.
I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front.
Here is the code for the columns additions, and formulas:
Columns("O:S").Insert Shift:=xlToRight
Columns("W:Z").Insert Shift:=xlToRight
Columns("AB:AF").Insert Shift:=xlToRight
Columns("AH:Al").Insert Shift:=xlToRight
Range("O2").FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])"
Range("P2").FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))"
Range("Q2").FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))"
Range("R2").FormulaR1C1 = "=RIGHT(RC[-4],4)"
Lets take this first one for example:
Range("O2:S2").Copy Range("O2:S2", Range("O2:S2" & Range("A" & Rows.Count).End(xlUp).Row))
If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254.
How can this code be modified so the formulas will not be copied past the last row of data?
How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.
See attached file illustrating exactly what I'm trying to do.
I won't repeat the exact same things I wrote in the file, but the Summary is that I need to first make such that only certain check boxes can checked at any one time, then make the command button run certain macro(s) depending on which boxes are checked.
Control Code input. I work with Autocad, when I extract data from a drawing into an Excel format (.xls) the extraction does not up date.
What I mean is:
In cad I input 6”%%C L=7” and get 6”ø L=7”. I need the excel file to convert as well. I need to program the cell? How?
If I have a cell which has been validated and has a drop down list of 4 names in it.
I need it to prompt the user that when they choose there name and select, a window pop up or similar window appears asking for a 4 digit(or similar) code.
This is to stop others using peoples names against jobs they have completed so there is accountability if there are issues with information.
For each line I need it to re-prompt the user to enter the 4 digit pin so it cannot be tampered with.
Can anyone help with sending through a form and VB code possibly which I can copy into my spread sheet or a method I can use!
I want to have a code where I can have an input box accept a value of text, such as aname and then use that value throughout my code, I will also be saving a file using that name
View 4 Replies View RelatedIs there a way to change a line of vba code using an input box?
The current macro downloads a file from our intranet. However the filename changes each week. It is only the last portion of the filename that changes since this part is the date it is created. e.g filename010507.xls filename090507.xls
What I want to do is for an input box to pop up, the user then enter the date and this will then replace the existing filename in the code with the new date.
I am developing a spreadsheet with numerous information on our different suppliers offices in the country for each of our outlets. I am tryin to define a way to do the following:
If a colleague selects a company another list will appear with the region and when the region is selected a list of the offices will appear.
On the "If not Isempty" line, I am trying to input a box prompt. Instead of standardizing this macro, I want the user to be able to select a dollar amount to search and place on sheet3. Right now it works fine using 1.29 as the amount. However, I have other stores where this dollar amount is different, so I want the store managers to be able to choose an amount to search, for example 1.39, 2.99 etc...
View 2 Replies View RelatedI have made a MsgBox which opens on Workbook opening with a message as follows:
View 4 Replies View RelatedI wanted to change a column text to call intial Caps. I found this macro code on the microsoft web site
Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub
I need to change the macro to have a input box (asking what column to convert text to initial CAPS)
I am trying to build a simple spreadsheet for my salesman that given a customer's zip code, it looks up the corresponding territory code for that zip code. I have conditional formatting setup so that it colors the zip code that matches the one inputted, but how do I then return the value of the corresponding territory code. The spreadsheet being searched is a simple 2 column list, with the headings: Zip Code and Territory. What's the best way to get the territory code back to my worksheet?
View 9 Replies View RelatedI usually just use macros to clean up Data from non-excel sources. So I wrote a macro to do this, but the process requires a date to be added, so attempted to do this via an input box. The input box works, but the code doesn't, clicking the button to which the macro is bound spits out an error 91 (object out of bounds?) when it hits the search function right after the inputbox code. Both bits work as intended separately, so I guess it's just some moronic formatting error on my behalf.
[Code] ......
Couple ways:
1) Format the cell as Text: Format->Cells->Text
2) Enter the zip code with a ' preceding the number, i.e. enter 00345 as
'00345
3) Enter the zip code with the formula ="00345" ...
I have code that I just noticed will not work if user inputs a capital S. I have tried a few things but I can not seem to get it to work. Here was the original code:
If Range("H13") "s" Then
I tired this:
If Range("H13") "s" or "S" Then
I have a button set to bring up an inputbox. enter serial number.
What I need to do is enter the serial number and then have the macro look through a
single column of values to match it then go to that row.
This seemed so simple I thought I could get it but alas here I sit...
I have tried to search the forum for a similar code but haven't found one that was close enough to make sense to me. I have ut together alot of individual actions, but how to get the value inputed to "search" the column and once found go to that row.
I also need to make sure it accounts for new rows being added in the future. (no set row range)
I have created an AddIn for a project I'm working on using the 'Open' command in Excel Vb. While this worked perfectly for the file I was testing it on (1,740,754 bytes) it doesn't work on a new file (121,445,125 bytes).
On testing the code, one line at a time, it gets stuck on
Line Input #1, DataLine
The files only contain one line so I suspect there is too much data for the command to handle.
Is there an alternative?
Modify Macro3 and use the InputBox function twice so that Macro3 would ask the user for a particular month and a particular year; and then Macro3 uses these user’s inputs to create the calendar template for that month of the year. For example, if the user enters February for the month and 2012 for the year, Macro3 would create a new
VB:
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(1)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "January"
[Code]...
Need way to interupt my code whilst the user selects a row. ie. if I wanted to move the contents of one row to another I would want to say: "select source row" (and want the user to click on any cell in that row) I could then put the rownumber in a variable with
View 2 Replies View Related