User To Select Top Left Of Data And Then Automatically Do Text To Columns
Feb 15, 2012
Data is input into my spreadsheet. It is not always in the same cell, and is not always the same length vertically. I want the user to select the top left of the data and then automatically do text to columns. Here is my code that is not working! :
Sub TextToCol2()
Dim rng As Range
Set rng = Application.InputBox(prompt:="Select a cell", Type:=8)
ActiveSheet.Range("rng:A" & Range("A65536").End(xlUp).Row).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
End Sub
I have two workbooks. I am copying cells data from one workbook A to workbook B with macro - pretty easy. Now, after selecting and copying cell data in workbook A and switching to workbook B I want to be able to pause the macro and wait for the user to chose a correct cell where he/she wants to paste the data. After the selection the macro would automatically paste the data in selected cell. Since I am completely newbie any help would be appreciated. Here is the macro I have so far :
Sub CopyVIN() ' ' CopyVIN Macro ' Macro recorded 5/26/2009 by Pacific Coachworks ' Sheets("Sheet1").Select Range("G9:R9").Select Selection.Copy Sheets("For copying VIN").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False Selection.Copy Windows("Cami's Production Schedule.xls").Activate ' Here I'd like to have a code for waiting for a user selection and pasting the data automaticlly.
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.)
I have created a user form using the addins that come with excel to create a calendar that will allow the user to select a date and automatically put that in a specific cell. My system is office 2000.
The code is as follows:
Private Sub OK_Click()
Dim i As Integer Dim myCell As Range
i = 0
For Each myCell In Selection myCell.Value = Calendar1.Value + i i = i + 1 Next myCell
Unload Me End Sub
Now, I gave it to my boss who has xp, and I get the following VB error when she chooses a date and clicks on the ok button:
I have two column (A and B) text data in worksheet1. If I type same text of column A (of worksheet1) in worksheet 2 column A, how can I retrieve data from worksheet 1 to 2 from same row.
What I am looking to do is search using a user input and select all the rows that contain the entered search text.
I have 4 sheet workbook, columns are the same across them all, and what i require is to be able to take a text input from a user, maybe what column to search, and in what sheet. Then search the relevant sheet for matches. Once found the record that contains the match is to be copied to another sheet where filters can e applied. There will be multiple records selected for each search.
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
I have a big table with a lot of data in it. I basically want to get rid of all of the empty cells, so that all the data that is there for the one person is all next to each other.
All the people's names are in a row for themselves so the empty cells would have to move to the left.
I have a spreadsheet where user can search for information inside a search box and the appropriate rows are returned using formulas.
I have a drop down list (Category: Model) in the search box as well as a search field (Category: Program, cell D2). Underneath the search box, search rows are returned with column categories: Program, Model, etc.
What I require is that if a user enters the specific program into the search field D2, then the dropdown list would automatically choose which model that program belongs to based on the returned row.
For example, if I typed "engine" into D2 and pressed enter,
Under the records section (Row 14 and beyond), the following record will pop up:
Program Model Indicators
Engine F-16 3a
Based on the record, F-16 would be chosen from the dropdown list. Is there any way this can be done?
I am trying to learn the concepts, then apply them to a set of actual worksheets. I am trying to use named ranges from one worksheet in another worksheet. The named ranges need to expand. Although I can get the add data to a dummy worksheet to work within the same worksheet and I can even get a named range to work in the foreign sheet.
My problem is, when I can end up adding the additional data to the named range, it will only add one name. I have a number of named ranges that are referenced on various worksheets. The idea is, certain named ranges should be able to expand so I donft have to type them all in. (There could be hundreds of manufacturers or models.) I would also like them to be able to sort themselves after additional names are added to the named ranges. I would also like them to auto populate as well. I am using Excel 2003 SP3 (11.8307.8221). I have enclosed the file. Drop down lists (named ranges) are on the worksheet called DROP DOWN LISTS (LOCKED) **ITfS NOT CURRENTLY LOCKED**.
The data validation issues are on the worksheet called Rev7.0 Wood (Basic Sheet.) There are other instances to use these, but I hope to learn to do them myself on the other worksheets. Of particular interest is Column f.................
problem i am having is lets say in cell A1 i put this text:
Andy,64,8,12 (which is taken from a drop down list)
Then convert it to columns in rows B&C
When i change the text in A1 to Something else from the drop down menu, the text in rows B&C does not update and stays as ANDY,64,8,12 instead of updating to the new text entered in A1.
how to get the text in rows B&C to update automatically with row A, If it can be done it would hit the nail right on the head as this is holding me back big time.
I have a cell in workbook X on Sheet1 (cell AB3) that states which columns should be exported from workbook X on Sheet3 to a new workbook. The value of cell AB3 on Sheet1 changes based on what a user selects in some check boxes on Sheet1. I would like my macro to read the value of cell AB3 and interpret it is a range reference of which columns to copy from Sheet3 into a new workbook. The problem I'm having is knowing what line(s) of code I would use in VB to read cell AB3 as a range and what kind of referencing rules I need cell AB3 to contain. Right now this is what the cell looks like to the user:
Code: Sheet3'(A:A,B:B,E:E,F:F).Select
For cell AB3 to look this way I'm just using some hard coded text, such as the sheet number and .Select, plus some concatenated values in between. Perhaps this text string needs to be modified, but I'm also wondering what I would use in my macro to reference the cell and read it as reference to which columns to select in Sheet3.
I need a formula to automatically change the summary column according to the month we are in.
Ie: Last Months Data Nov Dec Jan Feb
1 8 1
7 4 7
9 2 9
'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?
I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))
I need to use the user form to create the fields in the next free column in the parts data sheet.
At the moment, it just writes over the firsst part i create. Have tried countless methods but am new to this vba business. To take it a step further, i need to concatenate the three fields and copy this into the parts data entry, Again i have tried (see code) but cant get it to work. Finally, If possible, I would like to check the parts already created, so duplicates cannot be made.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 13 Then Exit Sub If InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then Columns("N").Hidden = True End If End Sub
but I have a lot of columns that I need to perform as above and I have put the code together as below
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next
If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................
Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
basically I want to be able to keep track of how much vinyl material I have left after each order.
The process would be - When I order a roll of vinyl material I would input the colour ordered and cm ordered by selecting from drop down lists in the 'Vinyl Tracker' sheet. When a customer makes an order, I would select an item, size and colour from drop down lists in the 'Orders' sheet. Depending on what size is selected in the 'Orders' sheet, I would then like Excel to automatically update column 'Cm Remaining' in the 'Vinyl Tracker' sheet, however using the smaller number in the relative size column from 'Item Sizes' sheet.
E.g. If we take the first order:
World Map Medium Black - (M)
This would then refer to cell M5 in the 'Item Sizes' sheet (as 43.35 is less than 90).
I would then like the number which is retrieved to be taken away from the relevant cell in the 'Cm Ordered' column, depending on what colour was chosen in the 'Orders' sheet.
To make matters a bit more complex, obviously when any of the numbers in the 'Cm Ordered' column in sheet 'Vinyl Tracker' is 0, I will re-order the same vinyl roll and insert it into the sheet as per usual. How can I make it so that any new orders will take away from the latest instance of the same coloured vinyl?
Private Sub Workbook_Open() Dim ws As Worksheet Application. ScreenUpdating = False For Each ws In Worksheets ws.Select ActiveWindow.Zoom = 75 ActiveWindow.ScrollIntoView Left:=0, Top:=0, Width:=100, Height:=100 ws. Cells(1, 1).Select Next ws Worksheets("Summary Form").Select Application.ScreenUpdating = True End Sub
This works dandy, except for one thing - it will select Cell (1,1) in each sheet, but when you go there, the view is still where it was last time. (Given the nature of the sheet, usually scrolled down to the end.) How do I make sure that the view is at the top of the page? So that A1 is not only selected, but at the upper left corner of the page? Is there a "Scroll to top" type of function?
I need to write some code that will write a sum formula and include all the cells to the left of the active cell, however this range is variable. I suspect it would be something like:
I have a large spreadsheet of species activity data and want to separate information from two columns to create a bar chart of when a certain species is active over a 24-hour period. There are duplicates of each species in one column and a unique corresponding time of activity for that species in another, something like the following:
Species A 23:01 Species B 07:09 Species C 13:45 Species D 08:30 Species A 01:55 Species A 22:07 Species C 15:29
What formula can I enter that only selects Species A and their corresponding times that I can copy into a new spreadsheet?
I want to select email addresses in a particular cell. Since every email address will contain @ so I want to extract the email address from the cell on the basis os all the characters to be selected on the left & right of the '@' before a space is encountered.
I'm trying to get a code that will do the following: I've got a list of stocks followed by extra information in de columns to the right of it. If, based on that extra information you'd like to know more about the stock, I'd like the user to just select the cell that holds the name, and click a button.
What this button should do is the problem for me right now: it needs to select the cell left to the one selected holding the name of the stock. The cell to the left holds the code that is used to lookup information in Bloomberg. This cell is invisible to the user since I hide it to make the tool look good. How do I write a piece of code to select for instance: B10-1=A10. The rest is just a recording job. Also, I know you can add text to the text in a cell, however in this case I want to cut some text. Is the any way to get: "B10-1"-"equity sedol1", leaving me with the text in A10 minus equity sedol1?
I have a spreadsheet with a heap of raw data in it. I need to perform a search based on a customer name, then return certain columns from the raw data IF the customer name is matched in one column, AND a special flag name is matched in a second column. The customer name is always a single entry for the purpose of the query, however the special flag must be checked against a possible list of values in a table. I know this is hard to explain, and I can't add attachments to my posts, so I have created a sample document and placed in in dropbox:
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I'm trying to create a sub that will save my worksheet to a tab delimited text file anytime there is a change in the worsheet data (all cells are linked to cells in other workbooks). I've figured out the command to save the file
I have 12 columns of numeric data in cells AA80 through AL80. Beginning with the cell on the far right (AL80), return the value if >0. Otherwise, proceed to the cell to the immediate left (AK80) and return the value if >0, otherwise proceed to the next left cell...all the way to AA80. I tried this by nesting IF functions, but have too many IF functions to nest. Lintcoop posted a similar thread in 2008 but with text data.
I am trying to find any way possible to freeze left side 3 columns and 3 right side visible columns on the screen. and the rest columns in between these freeze columns scrollable on left or right arrow keys as normal.
Is it possible to re-order entire columns based on cell values? For example, row A gives values of 1 thru 10 to the first 10 colums. I would like a macro that reorders the entire columns based on that value.
I have a file for which I wish only selected pages to be printed, so I have disabled the print function in the ThisWorkbook module. Instead, the user chooses from a menu of available sheets to print, via a user form, and then requests the print.
I've got most of this sussed in my mind, but the users need the option of which printer to use (loads of networked printers available and the choice will depend upon monochrome for draft print or full colour for final print), so I want to be able to call up the printer dialog box using
Application.Dialogs(xlDialogPrint).Show BUT!!!!
I want all the options on the dialog box disabled, except for the choice of printer as clicking OK after choosing the printer adds the active sheet to the list of items to print.
