I`m trying to create a macros that find new cell content in a column and add a row before it.
In the attached file is an example : insert_row.xlsx
In column A is the name of the row. And in some rows the name is changed.
I want to add a row before this change.
i attach my sample excel file here, book1.xls. sheet1, "input". it check the column "C" row by row and if it detect the different content data in next column, it will auto insert a new row. it will ignore the space. below is my coding:
View 3 Replies View RelatedI have a list of 12.000 pictures which have to be "connected" with the article number (1000) & color (21). Here starts the problem... Each article usually has more than 2 colors, but is listed only once per row and the colors are stated in columns.
What I would need is a macro or function that would do from such structure:
art description color1 color2 color 3
1000 product A 21 22 23
1001 product B 19 23
this:
art. description picture
1000-21 product A, color 21 1000-21.jpg
1000-22 product A, color 21 1000-21.jpg
1000-23 product A, color 21 1000-21.jpg
1001-19 product B, color 21 1001-19.jpg
1001-23 product B, color 21 1001-19.jpg
1. check if the row in color columns is not null
2. if this is true, then I would like to copy the entire row & paste it below existing
3. then the "art." column would be changed (=A2&"-"&C2), so the article woul get suffix of the color (and column "picture" would be created, maybe something like =A2&".jpg")
4. if any other color column in the same row is greater than null, then proceed to next color & repeat steps 2. & 3., else go to next row
5. until all 5000 rows are converted in about 12.000 rows.
I have two sheets
One is the master and the othere sheet which has the raw data.
On each sheet you have a unique code for the product which is the same on both sheets.
From the raw data sheet i want to the weight for that product to be inserted onto the master sheet.
(the problem is that both sheets are not in order so you cannot just copy and paste the column)
the unique code on the master sheet is G9 and the unique code on sheet 2 is A27 the weight on sheet 2 is r27 and want the corresponding weight for the product to appear on the master sheet in column BI9.
Daily We have to search the similar numbers in a enormous database (around 35000 - 45000). Base will vary daily.
The Numbers in 2nd Row of 'wish sheet' has to be compared with 'base sheet' (B,G & L columns in Base sheeet) and the similar numbers has to be copied down from corresponding 3rd row. single character variants and double charcter varients need to be found. box is made to differentiate single and double variants. The test file may be useful to identify my problem.
It is very difficult to use find Numbers manualy. We have been helped by the experts on various circumstances in the past. So, We again seeking help from our senior brothers here.
I have a list of values that are the following:
Column A: City Name
Column B: Distance to destination
Is there a command to find the minimum value in Column B and then copy the corresponding city name into a cell of my choosing.
I have tried to these through formulas without success but i think i need VBA also which i am not very experienced.
I want to paste a list in the "InsertList" sheet. This list will only contain the word "Correct" or "False". From then on i need a way to search for the word "Correct" or "False" in the columnS P,Q,R,S,T,U,V.
e.g. If in the column "P" on the "InsertList" sheet the word "Correct" is found, i need that entire row from A to V to be copied onto it's destination, in this case "sheet1".
If the word "Correct" is found on the column "Q" on the "InsertList" sheet, the rows from A to V need to be copied in the Sheet2. And so on..
sheet1.png
Attachment: dropbox.com/s/vgs4kzhoa1pip0a/CopyRows.xltm
I would like to be able to find all cells in row 2 that are "greater than 0". Then I would like to return the coresponding job number in row 4. I need to create a list of each job with the number of hous spent on that job next to it. I may have 30 different jobs but only 5 jobs that have hours for the specific week.
How I attemted to solve this is to create a table that listed all the hours from the largest number in row 2 to the smallest. Then I found the job number corresponding to that number of hours. The problem with this meathod is that if two jobs have the same amount of hours then the first job number is returned for both values that are the same. See attached example.
My goal is following:
I do an inventory of my schools equipment and have much info in many columns. But I just want to concentrate on column A.
This is example of the room names at my school and look like this:
Column A
A01
A01
A02
A02
....
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
I am creating a worksheet of contacts and the date and time that I phone them, along with their current "status" in our company. I have created a form and have got it to do the insert of new data that I input, BUT What I would really like is enter a "client number" (which is unique) and have it search my table for any record(s) with that client number, and then show me the last record that matches the client number, (this can all be done from within my form) then If I click on the "insert" command button, I want it to insert a new row after the current row and move my form data in the form into the new row which will include a date and time. So, To summarize.....I need 2 similar subroutines.
LOOKUP ----- I need to have my form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches, then display the data or display nothing is there is no match. INSERT ----- I need to have the form scan down the data for a match in a particular (pre-sorted) column, stopping at the last line that matches and then insert a blank row here and move my data into it.
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
I need to enter text in a cell, then click on a command button that will run a macro to find that text in a column and if found, insert a row and copy down the formulas from the row above.
View 6 Replies View RelatedI would like to find a value w/i a column after sorting and insert a row right above it. After that I would like to sort again from that point downward.
View 2 Replies View RelatedI am running a macro which basically is a tidy up of a data report that comes from SQL.
I am on the last bit Every time this tidy up runs there will be a different amount of rows but always the same columns. Under the last row of data I want to put the word 'TOTAL' in that row and column J. Then in the same row but column K I want to put a sum of K2 to that last row. How do I do this?
Here's all my code so far - like I said its basic formatiing stuff I've cobbled together!
Sub usagetidyup()
'
' usagetidyup Macro
'
'
Range("1:4,6:7").Select
Range("A6").Activate
Selection.Delete Shift:=xlUp
Columns("H:H").Select
[Code]...
I'm having problems with trying to find something in the spreadsheet and inserting a row before it. For example I would like write code that basically goes threw my rows and if it finds the "2800" then it inserts a row before that. I have now bought several books but I have yet to come up with an answer.
View 3 Replies View RelatedHow to get the GetFromClipboard syntax from here working with my code (below), but am having a really frustrating time. Basically, I need to search for whatever value is copied to the clipboard (as you can see below which will be relative to a certain cell).
Here's what I have so far, and I'm trying to replace the contents of the What:="121212121" part of the Find method to look for the clipboard contents (a 9 char number) in another sheet (where a duplicate exists - the value I am searching will be a unique identifier for the incorrectly created duplicate record that needs to be deleted).
So, What:=<insert number/string on clipboard here>
VB:
Sub DeleteDupes()
ActiveCell.Offset(0, 5).Range("A1").Select
Selection.Copy
Sheets("ActvMbrs_MatchCombos").Select
Cells.Find(What:="121212121", After:=ActiveCell, LookIn:=xlFormulas, _
[Code] .....
I have a spreadsheet named: PartNumVsJobNum
The rows, from row 3 down in column A contain part numbers.
The Columns, in row1, from B to (last column with data) contains job numbers.
On another sheet, named: Non_Completed. I have the part numbers in column A (as in the PartNumVsJobNum worksheet). In column E down are
Job Numbers (IE: 4PZ). In column G are quantity values.
So for instance if Cell (A3) = 360010 (first part number listed)
and Cell (B1) = 4PZ I need a macro the will find the rows with 360010
in Column A on sheets( Non_Completed) and try to find the job number (4PZ)
in (columnE) for one of the rows. If the job number is found, then the value
in G of the (Non_Completed) sheets should be copied to the corresponding XY
cell in the PartNumVsJobNum spreadsheet.
I will need to do this for each job number on PartNumVsJobNum Sheet.
I'm attaching a sample worksheet below.
Need a VBA macro that is similar to the subtotals function but without the subtotals? Basically I want the macro to look at column A and say, for each change in column A insert a blank row… I don’t need to worry about the sorting, I just would like to know how I can tell a spreadsheet to look at a column find changes in that column and just insert a blank row. On the second page of the attached spreadsheet is an example of the desired output… I will probably use this for many different spreadsheets so the simpler and more versatile the better, ie I can change the column I want the macro to look at.
View 3 Replies View RelatedIs it possible to insert a cell refenece into the FIND function.
So, replace JAN-2013 with Sheet1 cell A1
Selection.Find(What:="JAN-2013", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
I have a worksheet with several thousand rows. During a month new entries are added at the bottom of the previous entries. One of the columns for each entry is a stock symbol and I've written a macro to enable going quickly to any specific stock symbol's location by searching for the symbol.
Once a month I select rows for some of the new entries for a given symbol, cut the relevant rows, activate the macro to find the location of other entries of the same symbol, and then "Insert Cut Cells" so as to move the new rows to the same area as other similar older entries.
What I was finding was that activating the macro was causing a recalculation of the workbook, which delays the macro from finding the symbol in question. This was getting bothersome, so I avoided the delay by adding an Application.Calculation = xlCalculationManual to the beginning of the macro, and at the end of the macro I reset the Calculation back to Automatic.
This eliminates the delay, in finding the old symbol, but it also clears out whatever memory location was storing the cut cells, so that I don't have the "Insert Cut Cells" option available once I get to the found location.
So, currently I have either: A) a quick find of the symbol, but I can't use the "Insert Cut Cells" feature, or B) I have a delay in the finding, but am able to Insert.
how I can have the quick find of the macro but still retain the ability to Insert the cut cells ?
I've got a problem that's causing me a headache. i have a document with 1 column and 4000 rows (its an export from active directory).
I need to do a search cell by cell (so A1 - A4000) for any cells that contain the text "changeType". When a cell is found, i need excel to insert a new row above the current cell and insert the text "modify" into the new row (in the first column).
eg.
x
x
x
changeType
x
x
Becomes:
x
x
x
Modify
ChangeType
x
x
I am trying to find the first "Tour" after cell A138. If "Tour" is not in cell A177, then I want to insert rows until that "Tour" is in cell A177. How can I do this?
Sub InsertRow()
Dim FoundTour As Range
Range("A5").Select
If Range("A30") = "TOUR" Then
Range("A31").Select
Else
Do While Range("A30") <> "TOUR"
Selection.EntireRow.Insert
Loop
End If
Range("A31").Select
If Range("A40") = "TOUR" Then
Range("A41").Select......................
I have a macro that was working fine until I added a new column to my active sheet. Now it fails with a run time error 1004, insert method of class failed. The code is as follows:
Private Sub UpdatePart_Click()
Dim S As String
Dim r As Excel. Range
Range("A2").Activate
S = InputBox("Enter the part no. you wish to update")
On Error Resume Next
If S = "" Then 'Exit Sub
'If StrPtr(S) = 0 Then
MsgBox "Update Cancelled"
Else
Set r = Columns(1). Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)...............
I would like to be able to add vb code to my developing macro that searches within 1 column for the last instance of a particular word, then inserts a blank row under that word. All the data is sorted, so the words will be used multiple times, but I need the row to go under the last instance of each word in the column.
I have spreadsheet with data all over. I want a macro which identifies the first cell (in Column A) which has the text "BNY" and insert 6 blank rows above the text "BNY" (First text in the Column).
Once, it is done - the macro should also assign names to the last inserted row.
Column A - Should reflect "Bank", Column B should reflect "Field1", Column C should reflect "Field 2" and Column D should reflect "Field 3".
And after the names are assigned - It should also highlight the last inserted row in Yellow.
For Example:
Raw Data (Snap):
Bank
Field 1
Field 2
Field 3
JPM
123
456
789
[Code] .....
Output (After Macro):
Bank
Field 1
Field 2
Field 3
JPM
123
456
789
[Code] .......
I'm working with a document that has several cells which require double carriage returns within cells. The location of each space is marked with an @ sign. A few weeks ago, I found an article online that explained how to do a find replace where the find value was '@' and the replace value was a series of numbers/characters that created a carriage return. When I doubled the carriage return code I got my two spaces. If I remember correctly, the string that I entered still appeared in the cell, so I needed to clean up afterwards by replacing that string with ' ', but it still left the line break.
View 9 Replies View Related
