Using Find In Macro To Select Range Of A Table
Jan 13, 2010
I am trying to find a table of text, and copy it to another worksheet. I want to copy it as a range, not as rows or columns. Using Find, I can get to the 1st row of the table. Using Find again, I can get to the last row of the table.
What I don’t know how to do next is select the range.
For example; The table is in cells A10:F20. “Start” is in cell A10, and “End” is in cell A20.
Starting from cell A1, how do I find and select the range A10:F20.?
I will be doing this twice in the same macro, as there are 2 tables. Each of the 2 tables has the same “Start” and “End” text, as described above. The 2 tables will not always be located in the same location within the original WkSht.
View 4 Replies
ADVERTISEMENT
Oct 18, 2013
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] .......
View 3 Replies
View Related
Feb 4, 2014
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].....
View 1 Replies
View Related
Jan 31, 2014
I am having issues with a VBA script. The Spreadsheet is linked to a SQL DB and updated with data from scripts that are executing throughout the day. This is then fed to a dashboard. We have a Baseline that shows in the Date column as 0/0 and 1/1. I need a way to ensure that the 1/1 is always in the last row of the chart so the baseline shows across the entire range of dates. The current method is to declare a variable and add 1 to it whenever we iterate through the sort for the other dates. However everyday the 1/1 baseline column ends up further to the beginning until it is next to the 0/0 in position 2.
So I am trying to find a way to have the macros find the last row of the Date field and then set the position to be equal to the last row.
View 4 Replies
View Related
May 19, 2009
I am trying to work out how to select a range from a formula.
One formula works out when a specific number in a list of rows is reached, and returns the number of values it counted before it reached the number.
MATCH(TRUE,INDEX($A$2:$A$50>=B1,0),0)
With the figure returned from that can I then select the range from the first row, to the number of rows counted. And with that selection find the highest number within that range, specifying the highest number as the result?
View 14 Replies
View Related
Aug 27, 2009
Is there any way to create a script or macro to "find" a number from column A that is in column B, then select it that row? Then it willl repeat that process with the next number in column A until the end....
View 14 Replies
View Related
Feb 19, 2008
When 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).
View 9 Replies
View Related
Jun 6, 2014
I have multiple tables like the one in the picture and have to duplicate this code for different known ranges.
View 11 Replies
View Related
May 24, 2008
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?
View 8 Replies
View Related
May 9, 2013
I am trying to create a macro using the recorder that will select the latest date in a drop down list. The dates upate each day in the pivot table after the table is earlier refreshed. Using the recorder simply picks the same date each time. I want it to select the latest date. Am using Excel 2010 and have some very limited experience in vba for editing macros.
View 1 Replies
View Related
Nov 13, 2013
I have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.
Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.
I am using Microsoft Excel 2010.
View 1 Replies
View Related
Feb 17, 2010
range("A1").select
can I replace A1 with l where
l = worksheets("List").cells(3,2)
?
it doesnt like it, is there a better solution?
View 9 Replies
View Related
Apr 3, 2008
Hopefully someone can help me and understand what I am trying to do. I'm almost finished my project and stuck on one last part! If I have missed a similiar example on the forum could someone point me to it
I have a list of Lines (a,b...) which start at point i and end at point j.
Line i j Point x y
a 1 2 1 6 10
b 2 3 2 7 4
3 2 3
I have have a seperate list of the points. Each i point and j point have x and y coordinates.
I want to find the i point value in my list of points and copy the following two columns (the x and y values) to Sheet2. On sheet2 I want to paste the i values in row 1 and the j values in row 2, skip two lines and move onto Line b.
View 6 Replies
View Related
Feb 9, 2007
I'm in charge of taking a report; streamlining its functions; and automating future reporting.
I'm using a database and some of the reporting function Access has. For another variation I'm forced to export the query to excel and transpose the vertical layout horizontally.
I'm making the entire process automated via a macro. The code is too long to post in its entirety but here is an example of what I'm trying to accomplish:
View 14 Replies
View Related
May 30, 2007
I am looking for a macro that will allow me to select the range of A5 to the last column with data in row 5
View 9 Replies
View Related
Aug 27, 2009
I have a protected worksheet with a mixture of locked and unlocked cells, and I'd like to provide an option to allow the user to select a large range of mixed (locked and unlocked) cells and, in the unlocked cells only, pre-populate with a standard formula.
I've got most of it working, but what I need is the specific code that allows the user to specify the range of cells they want the macro to act on.
View 9 Replies
View Related
Aug 23, 2006
I have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have
ActiveCell.Range("A1").Select
ActiveCell.R1C1
Range("R1C1:R1C85").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True
Range("A1").Select
I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.
View 2 Replies
View Related
Dec 7, 2006
Using a script but when I use the given Range it is still using the complete WorSheet.
View 9 Replies
View Related
Apr 17, 2008
I have made a sheet where the user can insert/delete rows in a certain intervall of rows. The upper row is 17, that is I have named the cell "First" and then the last row "Last" and then I am using integers:
Dim intStartrow As Integer
Dim intLastrow As Integer
intStartrow = ActiveSheet.Range("First").Row + 1
intLastrow = ActiveSheet.Range("Last").Row - 1
So it is only possible to add/delete rows if the markerer is in between the above rows.
Now I would like to select the rows, from first to last. Since the rows are never the same, users add and delete rows all the time, I can not use
Range("A17:Z49").Select
Can I somehow make a range selection using the Integers, somethimg like
With ActiveSheet
Selection."intStartrow:intLastrow"
End With
View 3 Replies
View Related
Jul 23, 2008
I have this:
Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub
it errors to: SELECT METHOD OR RANGE CLASS FAILED
View 9 Replies
View Related
Dec 15, 2006
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.
Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.
Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.
I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.
Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.
Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
View 9 Replies
View Related
Jan 12, 2009
i have a spreadsheet that data is imported to from a different program. however the amount of rows of data will change from day to day. I need to be able to select this range of data before i run a macro on it.
View 11 Replies
View Related
Mar 12, 2003
How would a code look like to select a range with a column header on top. in the code you provided, it selected the entire W column. How would you select the range given that the W column was called Address?
Sheets("Sheet1").Select
Range("W1").Select
Range(Selection, Selection.End(xlDown)).Name = "Problem_Area"
View 6 Replies
View Related
Feb 25, 2014
I have a macro that contains a line:
Range("BB1011:CX1064").Select
The number of columns selected remains the same, but the number of rows changes. The indicator that tells me how many rows to select is contained in cell F1007 and in this example contains "54".
How do I adjust the macro to change the number of rows to select dependent on the value in F1007?
View 6 Replies
View Related
Feb 18, 2008
I would like to Pause a macro to allow the user to select a range on the spreadsheet and then click OK to continue with Macro.
I tried to use Msbbox, but I can't figure out how to allow user to access the spreadsheet to highlight the desired range.
View 9 Replies
View Related
Apr 30, 2008
I cant seem to get to work together in the same macro but that work great seperated. I need them to be in the same macro. The first just simply copy's text from one workbook to another:
Sub Test2()
ActiveSheet. Range("a1").Copy _
Destination:=Workbooks("punchlist.xls"). Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1,0)
End Sub
That worked ok but I needed to change it to the "active cell" instead of cell "a1". So then this line of code was made:
Dim userInputCell As Range
On Error Resume Next
Set userInputCell = Application.InputBox("Use the mouse to select a cell on any sheet", type:=8)
On Error Goto 0
If userInputCell Is Nothing Then
MsgBox "Cancel pressed"
Else
Msgbox "You selected " & userInputCell.Address(,,,True)
End If
The second code works just the way I want it but it doesnt copy over the text to the other workbook. I assume the 2 codes need to be together but I cant get it to work without errors. I also need the text to copy over without changing the borders on either workbook.
View 3 Replies
View Related
Feb 4, 2013
how to find the source data range of a pivot table (that already exists) in Excel 2007?
View 1 Replies
View Related
Oct 11, 2012
I have used the macro recorder to generate the select range and clear part of the following macro.
Private Sub CommandButton2_Click()
Dim test As Worksheet
Sheets("Industry").Copy After:=Sheets(Sheets.Count)
[Code]....
I get the runtime error 1004 - Select method of range class failed.
I have used similar script in other macros without error.
View 2 Replies
View Related
Feb 2, 2010
I have a base document that i can import another data document with a button (this is working).
I then have another WS ("search") in the base document with lots of identifiers which I want to use as my search range to look through the document that I just imported (column A).
I need a msg to ask the user what month they would like to find the $ value on (Ie, January) in the imported WS... this way it doesnt copy the entire line only cell in the selected month column.
Then I want to the user to be able to click a button that will check through the identifiers on the "search" WS and if the same identifier appears in the imported WS in column A, then for the $ value in the column selected to be copied to the search Range work sheet.
If the idenfifier is not matched then in place of the $ value copied can be the string "no in XXX WS" .
I have attached the document with dummy data in each work sheet with details more cleary what I have meantion above.
View 9 Replies
View Related
Apr 12, 2011
Am working in Excel 07, but this would need to work in 2000 as well.
Need a macro that will...
1. Select a range of cells from B4 to RX. X is defined as the last row where Column A has a value.
2. Copy the visible cells
3. Open an email in Outlook (not via the email workbook function of excel), enter "Submission" into the title, enter "Dear X," insert 2 returns.
4. Paste the copied table (not the workbook, just what is on the clipboard) into the body the email.
View 4 Replies
View Related
