Is it possible to "offset" to a different cell on a preselected row using macro code? Meaning, if row 12 is currently selected can a macro select the cell on the same row under column "L"?
View 5 Replies
I have contacts list that I made and want to take it to the next level, but don't know how. See attached.
The goal:
1. Highlight an entire row of someone's contact info (i.e. row 9)
2. Click the "ORDER FORM" text box to activate a macro that populates specific cells on the 'order form' sheet with the customer's contact information from the highlighted row. The information I want copied over is in red on the order form sheet. THAT'S IT!! (Oh, and 'paste value' the current date from H5 so that the date is static on the order sheet
If possible, I would like to have an error check in case no row is selected prior to clicking on the text box that pops up the note "Please highlight a contact row before clicking the 'Order Form' button."
How do I preselect items in a listbox based on combobox selection and values stored in matrix.
I have attached the file with a description of what I mean
I used to do some VB stuff several years ago, but it's unfortunately not exactly like a bicycle and I'm having a hard time remembering what I'm needing to do.
I have several functions in my macro which are supposed to select an entire column of data and do various things with it, from cutting & pasting to combining data from multiple text cells. Also it's got to input in one column, a text string "CIS". Anyway, if I tell it the whole column, then it winds up overflowing beyond what I need, and I really only want it to affect as many rows as have data in the spreadsheet.
Here's the data:
'Unlimited Range K here', that's where I want it to select to the last row of data for the range.
Sub ComputeCSV()
'
' ComputeCSV Macro
' Macro recorded 12/18/2006 by Matthew Roberts
'
' Keyboard Shortcut: Ctrl+l
'
Columns("G:G").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Cut
Can anyone help me to write a macro to select certain cell depending on a value in other cell.
I.e: If cell A100=1, then select cell B1, but if cell A100=2, then select B2, and so on.
the macro code to select the cell after the bottom of a column of data e.g. I have data in A1:A300 and I require the macro to automatically calculate the last row in the range (in this case A300) and then to select A301 (in this case)
(I know I can record a macro but the number of rows can potentially differ every time I run the macro)
N.B. Excel version 2003
I am working with a spreadsheet that has asterisks in place of text that should be added. The problem is that it is surrounded by HTML code. I need to be able to select the cell, go to, and highlight the text, where I can hit delete, or just start typing to replace the asterisks, BUT NO OTHER TEXT IN THAT BOX CAN BE EDITED.
Attached is an example of what I'm wanting to replace.
example 1.jpg
I want to create a macro that will go to the text I have highlighted here, and STOP just how this is, so I can simply put what I want in place of the asterisks, and move to the next cell.
Is this possible?
Currently working on a project for university and still learning how to use macro.
Got a problem which im sure is an easy solution for all you experts out there. As shown in the picture; http://img300.imageshack.us/img300/1205/macrohelp.jpg. When the Sap number is entered (from A19 and onwards), it copies the value to cell B10. The general code is used below.
Using vba how do I tell a macro to select the row that the active cell is in?
I'm just using a basic delete Row macro but I'd like for the macro to automatically select the entire row when it's time to delete instead of me highlighting the section.
I am wanting to pause a macro to allow a user to select a cell and when the user hits enter the macro starts running again. My intention is that a user would select cell B5 or B8 or B55 and then hit enter and the cell would move 6 cells right and enter a value from the macro. The following is what I have tried and even though it allows to select a cell, it doesn't make that cell the active cell.
[Code].....
I'm having to run at low resolution on a sheet currently, so I'm using a zoom/center routine to make the dropdown lists visible for selections.
Issue I have is how do I select cell A1 after my error handling and exit routine properly. I couldn't get it to work without trying to select cell A1 before I would make a dropdown list selection. For now I am just running a separate macro to select a1 tied to a pushbutton as a temp fix. Code is attached.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long
lZoom = 57
[Code] .............
What would a macro look like that would find the value in the cell of the last row in the A column, then set that as the selection criteria so macro could copy all rows that match that to another workbook?
View 9 Replies View RelatedWhen recording a macro, how would I have it record me searching for a word in a cell, then select the two rows beneath it so that I can hide/unhide the hidden rows?
The reason it has to search for the word in the cell is that with changes above, the rows keep getting bumped further and further down (i.e., a moving target).
In a particular sheet the data (with formulae) are present from rows E5:Q5000. I require a macro which automatically does the following:
- When user enters “submitted” in the row Column N, entire rows above that cell should be selected, and copy-pasted as values to remove all formulas. So if user enters “submitted”, at N31, then data set E5:Q31 should be selected and copy pasted special so as to retain only values.
-Finally the copy pasted data should be sorted as per the status of Column N.
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 was planning to do was have a macro where the document opened and the cell that information was pasted in was decided depending on which cell was selected when the macro was run.
This worked fine, but it does mean that it needs someone who can edit the VBscript every time there is a new starter or someone leaves so isn't really viable.
What I was wondering, is if it is possible for me to start a macro, which then drags in the information I need from a text file, does all the calculations and rearranging, and then brings up a prompt for the user to open the document they want to paste it in, click on the cell where the pasting will start, and then the macro continues from there?
To try to explain it in as clear a way as I can. Say we have three managers, Alan, Brenda and Charlie. In Alan's team there are 10 staff members. Aaron, Bill etc.etc. If we have just run the report for Aaron, the macro will run, automatically drag all the information in and rearrange it, then it will pause while the user opens up the "Alan" spreadsheet, they select the "Aaron" first cell, click OK or something, and then the macro continues on its happy way?
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.
H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.
Below is what my limited understanding of VB came up with.
Sub Date_Range()
Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="
How do I populate a second worksheet with only the preselected highlighted rows of cells from another worksheet.
This way I will end up with only the selected data from the first worksheet in the second worksheet.
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx
I have a large database of equipment on one sheet and an input form on another. For inventory control, when a user scans a number into the input form, the main inventory sheet is updated with the current location. I have it working using functions, but I need to do this in VBA. I am looking to do the following;
User enters 2222 on the input form. The code matches 2222 on the inventory sheet, moves the activecell 10 columns and updates a value in that cell. Cell A2343 is "2222" then Cell K2343 is changed to "WAREHOUSE 4". What is the best way to find the match and then select that cell in VBA?
I have a client list in a table in my excel sheet. I currently enter the clients in a sheet that looks similar to a form. After the clients info is in this Form I have a button with a macro to copy the client info into a table on another sheet. This works perfectly.
Now I have another form that will retrieve the customers info in a click of a button in order to make changes to the client. Ei:I now have a second phone number and I want to update that client's info. What I want to do from here is take the info in Cell B2 (clients full Name), Do a vLookup in the "Saved Clients Table" in a Range within my table range. (Range: Saved Clients and the client's full name is in $A:$A and called "Clients Full Name" and once that name is found I need to select the cell in column C and the corresponding row.
This will allow me to copy the info in my form, Paste it in Transpose to the cell that is selected. (I already have that code)
Here are screenshots of what I am trying to accomplish. [URL] .......
i have a macro that opens 10 other workbooks and copies cells onto a "master" workbook. Until recently, everything worked fine. Suddendly, while it opens the 9 books and copies as it is expected, but when it opens the 10th wb, the macro breaks at a very simple copy/paste.
The weirdest thing is that after it breaks, I cannot select any cells neither in the opened wb nor in the master wb. This continues even after I press the "reset" button in the vba. So, I am only able to select objects (text boxes etc) in my wb and not any cells.
Also, the "arrow" icon on the design toolbar is not active. And I've tried pressing and de-pressing the F8 key, but I still cannot select any cells.
I have option explicit in the beginning of my macro. And i'm using excel 2003.
I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.
I'm very new to excel macros and the parts I think I've put together are:
Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
[Code].....
I need to get a macro to select all the data in column "A", sort it in ascending order, omit the blanks if any, then select (highlight) all the data so that another macro can be run.
When I record it, it will only record up to the last row I highlight but the data always changes so there could be more or less.
I want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies View RelatedI wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedI have column A with various values in cells.
For instance, DG, GS, HG etc
I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.
I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.
ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")
I have a workbook with a 2 macros "PopulateSheetlist" and "SaveEditedversion"
Normally they are each button operated, and prompt the user with vbYesNo style options. I wanted to write a Macro, say "Macroautomatewkbk that would automatically answer the prompts.
Sub Macroautomatewkbk ()
Call PopulateSheetlist
' Always answer YES or OK to any prompts this macro may offer
Call SaveEditedversion
' Answer No for the 1st prompt and YES for the 2nd prompt
End Sub
MsgBox "Both macros "PopulateSheetlist" and "SaveEditedversion"
' Some error handler here, don't know how this should work exactly.
Could any one please explain how to fill in code in the commented sections in the above sample code?
Edit: I tried recording a macro to do the above, but it only showed the zooming and scrolling that occurred, none of the button prompts being answered.
I need to create a new macro that will select all rows that have data and create a (3) pivot tables.
I have tried to create macros that will create a new tab and populate the pivot table. I keep getting an error that says "subscript out of range", so instead of creating the tab during the macro I have created the tabs as part of the template.
Again the problem is when running the macro with more rows, the macro doesn't recognize the additional rows. Ctrl + A is used during the macro but is hard coated for only the number of rows it selects, Ctrl + A twice is giving me "blank" data in the pivot tables. I have attached a sample sheet with 14 rows. Next month there may be only 10 rows or 50 rows. I have macros created for the pivot tables to view.
1: How can I create a macro to select all rows with data?
2: Can I create 1 Macro to create all 3 of the pivot tables needed?
3: Can the macro also create the tabs during the execution of the macro?
