Macro To Find A Cell Containing Text /select Range Offset / Cut And Paste To Lower Rows?
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
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
Aug 23, 2012
I am trying to achieve something like this :
Find a specific text in my column B (example : "Proposal ID"), when "Proposal ID" is found, select this cell and offset to the column C (Offset(0, 1)). Then copy this cell value in another sheet.
This will be repeated with different texts (always in the column B), so if the text is not found, I need the macro to continue running.
View 5 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Feb 1, 2009
I have a data sheet with employee information. Only one column. It prefixes information with codes, but keeps it in the same cell. I want to move data of certain types into their own columns, but the amount of data is variable, so I cannot simply move every Nth cell, etc.
i.e.
Employee#1
200 Firstname Lastname
204 99999999 (Employee ID)
G38 00005000 (i.e. Pension Deduction)
H38 00007580 (i.e. Benefits Deduction)
X96 00012099 (i.e. Staff Club Deduction)
Employee#2
200 Firstname Lastname
204 99999998
G38 00000775
X96 00001000
So you see some employees may have different codes altogether. But I know that I want all the cells that start with 200 to be offset (-1,1), and all the cells that start with 204 to be offset (-1,2), and so on so that basically I end up with columns of info instead of a one column list.
I have been reading and studying other peoples' macros, and am just starting to grasp the basic. When I wrote my own to accomplish this, I put this together, which doesn't work. But I don't know enough to know what I don't know.
View 7 Replies
View Related
Nov 5, 2013
Let's say sheet - "delivery" - has a row1 that includes all possible delivery days. So A1 is 1.9.2013, B1 is 15.9.2013, C1 is 1.10.2013 and so on.
I want to make a macro, that will gradually go through whole row 3 in sheet "delivery" and look for "A1" date in sheet "Orders". Sheet "orders" have for example in column B the date of delivery, and in column C product of the delivery.
I want the macro to find all deliveries with "A1" date, and paste all products that will be delivered on this day under cell A1 (sheet delivery). then move on to the cell B1 (sheet delivery) - find all orders in sheet orders, that will be delivered on B1 date, and list all products with this delivery date under cell B1 (sheet delivery).
Example:
Sheet order
A
B
C
Date of delivery
Product
1.9.2013
orange
[Code] ........
Unfortunately simple Pivot table is not able to do this simple list.
View 3 Replies
View Related
May 13, 2009
I am in the process of writing a VBA code(I should I am fairly new to this code writing!!!).What I need to be able to do is below.(enclosed please find a output in an xls file) What I should be able to do is below:
1.Find the text "Cash(No Listing)(Monthly)"
2. Offset to the 13th Column after that
3.Select the value in this column ,copy the value
4.Go to a row above and move one column to the left i.e.offset (-1,-1) and move 12 column backwards i.e.offset(0,-12)
5.Copy the value in 3 above to this entire range
6.Repeat this process for the entire file
My code is as below.Let me know where am I making a mistake. Just to let you know that this code performs the job well for the first entry i.e in the yellow makde area and doesn't do the job for any further entries .Llooks like I have some problem with the loop but not sure as to whats happening!
View 3 Replies
View Related
May 23, 2012
What can I add to the macro I already have in place (below) to accomplish what I'm looking for (2 parts)? ...
1) I need to copy everything (formulas) that is in C7:F7 and paste it down to all "active" rows - I'm defining an active row by any row where column A is not blank.
2) I need to copy everything (formulas) that is in Q7:AF7 and paste it down to all "active" rows - I'm defining an active row by any row where column P is not blank. (You'll notice by the screenshot that there will be blank cells in column P mixed in with non-blank cells.)
Code:
Sub AdminTool()
'
' CreateAdminTool Macro
'
'
ActiveWindow.Zoom = 90
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
[Code] ...........
View 7 Replies
View Related
Nov 27, 2007
i need a method to find the end value in a row (right side) and select the empty cell to its right to paste data in.
problem is the row where this data is may change so using
limit = (row, col).end(XlRight).Col
View 9 Replies
View Related
Feb 13, 2014
I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
View 1 Replies
View Related
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
View Related
Sep 2, 2009
The initial code was from this forum. I modified it so it wont work
The code finds the first instance but none after, why?
View 3 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
Jan 20, 2007
I would like to select cell A6 after pasting a large range so that when send to user the entire range is not selected. I tried
wsDestPage.Range("A6").Select
And
wsDestPage.[a6].Select
I receive
Select method of range class failed
How can I select a single cell after pasting the large range?
View 5 Replies
View Related
Mar 31, 2014
So ive got this macro that will find the text "SEP" in the active worksheet and paste it into the new worksheet. The Macro looks to see if a row contains "SEP" and copy that whole row and paste it into the new worksheet. I have over 5000 rows in column A that contains "SEP0014A9713685" and "SEP0014A981785C" So they are all different so its not just finding "SEP" So i need some type of Contains "SEP" for column A. This is the macro i have right now.
' Devices that contain SEP
Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
[code].....
View 3 Replies
View Related
Apr 23, 2008
I am trying to select a range in VBA, where I know the name of the starting cell, and the size of the range are values returned earlier in the VBA function. I guess the pseudocode I am trying to do is: range(range("StartCell"):range("StartCell").Offset(x,y)).Select. Obviously this code doesn't work, but I don't know the correct way to select this range
View 2 Replies
View Related
Jun 7, 2008
I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.
Sub RatingsFix1SP()
Dim FindWhat, rngCell As Range, i As Integer
FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
For i = 0 To 3
For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If InStr(rngCell, FindWhat(i)) <> 0 Then
rngCell.Offset(0, 6) = 0.15
End If
Next rngCell
Next i
End Sub
To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.
View 5 Replies
View Related
Mar 4, 2009
I have a workbook with many sheets in it. Within each sheet there is a cell with the Text "March 09" there are then 3 cells to the right of this cell with relevant information. Is there a Macro that can search for this text March 09, then copy this and the 3 cells to the right of it and paste special this information 1 cell below for all 4 cells.
View 2 Replies
View Related
Sep 3, 2008
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?
View 9 Replies
View Related
Sep 30, 2013
create a formula copying a formula from cell a5 to a20 but i want the result to be 1 cell lower from a5's result
View 6 Replies
View Related
Jan 28, 2009
I am trying to create a macro that goes down Column A of my sheet and looks for a string, "Number". When it finds it, it resize (0, 14) and cuts. Then it pastes the array at an offset of (-1, 10) This is what I got so far:
View 4 Replies
View Related
May 27, 2008
I'm trying to use an offset from a Named Range as my source cell in a vlookup but get a -1004 Object not defined error. When Debugged the Range.Formula appears to be blank when queried. Using Record a Macro the Reference to a named range worked fine but I could not test for an offset or the use of the range reference. What can I do to use to make this work
Range.Formula="=vlookup(Range(""SomeName"").Offset(1,0),DataRange,2,False)"
View 7 Replies
View Related
Jun 17, 2008
I am having trouble with the Find method in Excel VBA.
This is the piece of code that I have written:
Sub NCMVariables()
Dim x As Integer
Dim c As Object
Sheets("Data").Select
With Worksheets("Data"). Range("A1:A60000")
Set c = .Find("USD", LookIn:=xlValues)
If Not c Is Nothing Then
View 3 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
Dec 12, 2013
how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).
So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.
Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.
I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?
View 8 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
Nov 16, 2007
The Sheet below is an example of what I need to fill in daily
In column B I have the week commencing date (ie the Monday)
When the sheet is opened, I want the approriate day to be selected (ie the day before the current day)
Eg. if today is Friday 16th November, when the sheet is opened I want the cell for Sales on Thursday of the week which begins with 12/11/07 to be selected ie L5
If today is Monday 26th November i want Sales on Sunday of the week which begins with 19/11/07 to be selected ie U6 ....
View 9 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
Nov 17, 2007
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 Related
May 19, 2014
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?
View 4 Replies
View Related
