I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.
For example:
- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.
I am trying to use VBA to populate the first blank cell in row 6 with the word "Short"
I keep getting sub or function not defined and I am sure its because I am trying to Frankenstein some code together to get it to work. So far everything works as I need but this last step.
I want to insert blanks rows above rows that have the number 1 inserted in column C.
I about 60,000 rows in all, doing it manually takes forever because I have to keep waiting for Excel to push the rows down every time I insert a new row.
Is there an efficient way to grab all the rows with a 1 in column C and insert rows in one swoop? If not, I will settle for any way other than manually.
Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?
This has seemed to have happened on a computer at work.
In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"
If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.
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] .......
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies View RelatedI have the following datas as an example;
A1: %90
A2: %100
A3: %75
According to those datas I want to get the following results;
B1: On going
B2: Done
B3: On going
So basically I want to tag columns which are equal to 100% as "Done", and the rest is as " On going".
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedAdd a blank space after a specific text? I have many cells in a column that all begin with the same two letters followed by more text. Something like this:
ab123
ab456
ab789
ab159
ab951
What I'd like to see is:
ab 123
ab 456
ab 789
ab 159
ab 951
I've create an userform with 2 textbox and a command button.
The user is allow to paste an article into textbox1, when they clicked the button, it should be able to find a specific text string in the article, then right after that text I want to insert addition note and a new article with notes will be generated in textbox2.
Currently i stuck on how to insert the note after the specific text string?
I've a sheet and in row A is a list of tasks to do like:
A1 - Task1
A2 - Task2
A3 - ...
Is it possible in VBA to do this:
A1 - Task1
A2 - Blank cell
A3 - Task3
A4 - Blank Cell
A5 - ....
in other words to input a balnk cell between tasks (text)
I have in column D 1200 rows of data. I need to insert a blank cell after every 10 rows. Is there a formula or code to do this?
View 9 Replies View RelatedI have a spreadsheet with the following (example) :
Column A :
alabama
alabama
alabama
arizona
arizona
arizona
arkansas
arkansas
I want to insert a blank line (row) wherever the state name changes.....
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies View RelatedI am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).
View 3 Replies View RelatedI would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.
View 3 Replies View RelatedI have a dataset that i want to update, to do this I want to insert 8 rows after each row that has a value in Column C 2000. The set has 600,000 rows, comparing countries (cty, cty2) from 1980-2000. I want to make room for information up till 2008. I see lots of codes for doing this but I will also need help with actually inserting into my spread sheet.
View 3 Replies View RelatedI was trying to use a modified version of JBeaucaire's code to achieve the same results within my form. In my workbook I have a a table (called Table27) that ranges from A7:CL109
This is the code I'm attempting to use:
[Code] .....
Column A is where my target cells are... where the user will enter data. In cell CI (the 87th column) I want the Data & Time stamped.
I thought I'd modified the formula correctly, but I can't seem to get it to work.
May seem straightforward, but not to me. I need some code to scroll down column A and insert the sum total of A2:A16 in B17, and repeat this everytime a blank cell appears in colum B, continuing down to the end of the worksheet.
View 5 Replies View RelatedI have a large spreadsheet with about 18000 rows or data and about 60 columns. I need to a macro to find a specific word in a column like "charge" and anytime that word is found in the column to insert a blank cell in front of it.
Currently the sheet looks like this:Fee AChargeFeeChargeFee AChargeFeeChargeFee AChargeFee AChargeNeed to get it to look like this: (so I can sort and subtotal)Fee AChargeFee AChargeFee AChargeFee ACharge
a macro to the for whichever column I choose.
I need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?
View 1 Replies View RelatedI have written such loop which I want to delete entire row when a given cell is empty:
Code:
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For Counter = lastrow To 2 Step -1
If Cells(Counter, 6).Value = "" Then
Selection.Rows(Counter).EntireRow.Delete
End If
Next
It works not the way I want. It has ommited some blank rows and also removes 5th row everytime I run it.
I have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50
Balloons 12567 Blue Princess balloon .86
Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27
Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
etc..
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.
I have a spreadsheet with four columns of text.
In column A, i have multiple levels followed by a letter (i.e. Level 1A, Level 1B etc).
In column B, i have some other details and then so on and so forth.
In column C/E/G lets say, i want to copy the information from column A to show only items that appear as "Level 1" (not "Level 1A", i only want it to check for things without the letter at the end). Then the same in column E but with "Level 2" and so on and so forth.
Column A...Column B-Column C...Column D--Column E...Column F--Column G...Column H
Level 1A....Metals----Level 1A....Metals ---Level 2A....Integral---Level 3A....Television
Level 1B....Energy----Level 1B....Energy--- Level 2B....Flowers---Level 3B....Kitchen
Level 1C....Synergy---Level 1C...Synergy--Level 2C....Full
Level 2A....Integral---Level 1D....Orders
Level 2B....Flowers
Level 1D....Orders
Level 3A....Television
Level 3B....Kitchen
Level 2C....Full
I also have data in Column B that is to do with column A (i.e "Level 1A" - "Metals") and so on with the following columns. I want the items that are in column B to also move over to column D when the things from Column A move to Column C, so at the end it will appear as below so it appears as above.
where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.
View 1 Replies View RelatedI have created a form where a date and remark is added every few days. I am using Phone Number as the base of VLookup. I want a code so that the date and remark which is entered in a Text Box, is stored in the first available blank cell pertaining to that employee. I want the first date and remark to be under "Date 1" and "Remark 1".
Thereafter, if another remark is subsequently added for that employee, the form would ask to enter his phone number to identify which employee the remark pertains to. Then the Text Box where remark is entered should be stored under "Remark 1" if there has been no remark before, "Remark 2" if "Remark 1" already exists, "Remark 3" if "Remark 2" already exists and so on.
I do not want to enter it in a new row. I want to enter the remark in the next blank cell of the row which matches the employee Phone Number.
Untitled.png
I'm trying to figure out the if conditions for this relatively simple problem. Basically, this is an import from a word document where the table strays onto a new page. I want to try and repair this with a bit of VBA.
This is what it looks like:
1: cell 1 |the content should all be in this cell
2: |but the import sometimes splits it into two
3: cell 2 |
Basically if and only if cell Ax is blank (in this example A2), then I want the rows to merge each cell and repair the table.
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank).
The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ).
Example:
C D E F G H I J K >..........................GZ
23 34 67 74 2 34 6 2 56 all blank ("") to end
or:
C D E F G H I J K >..........................GZ
23 34 67 "" "" 34 6 all blank ("") to end
I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example.
I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.
i am trying to Put a specific value i,e "NO Deliveries" in blank Cells in the same row based on value in 1st cell of the Row which has a fixed value . note:the columns and rows are dynamic for example: I want to put the value "no Deliveries" in cell C because its blank.
A B C D
% of Availability
0.00%
80.00%