Copy Cell Above To Blank Cell Below & Format
Dec 20, 2006
I am using this code to copy a cell to a blank cell below. It works great but I just need to format it to "White Font". I have tried using "colorIndex = 2" but I am not able to do it.
Dim vData() As Variant, i As Long
With Intersect(Columns("B"), ActiveSheet.UsedRange)
vData = .Value
For i = 2 To UBound(vData, 1)
If Len(vData(i, 1)) = 0 Then _
vData(i, 1) = vData(i - 1, 1)
Next
.Value = vData
End With
View 5 Replies
ADVERTISEMENT
Jul 8, 2014
I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
View 4 Replies
View Related
Nov 26, 2013
I have 2 worksheets, let's call them "Sheet1" and "sheet2".
Sheet 1 has 2 columns (A, and B)
Ie. "Sheet1:"
Col A, Col B
Red
Green Yes
Blue
Yellow
Orange Yes
For each entry in Column "B" that has a "Yes" value, I need to copy the color value in column "A" into the next available empty cell in "Sheet2" in column A.
"Sheet2"
Col A, Col B
Red 3
Blue 5
Yellow 6
Final Result:
Col A, Col B
Red 3
Blue 5
Yellow 6
Green
Orange
how to do this particular challenge? For Sheet2, I think I'll need a function to determine the first available blank cell in column A.
View 8 Replies
View Related
Apr 7, 2009
I have Column A and Column B. Every row in column A is populated, but only some of the rows in column B are populated. I need a formula that copies column A if B is blank. (i.e. If B6 is blank, copy A6). Thoughts?
View 6 Replies
View Related
Dec 10, 2006
I want to copy a changing value from a cell (A3) to the next blank cell in the column creating a list of numbers for a total.
View 3 Replies
View Related
Jan 30, 2007
I need some VBA code that will allow me to copy the prior columns cell value into the current row if the cell is blank. More specifically if there is a blank cell in column D, then copy the adjacent value in column C to the cell. Hope this makes sence. I've attached a sample spreadsheet that shows my desired output.
View 3 Replies
View Related
Apr 27, 2008
I have a spreadsheet called "temp.xls" that will change file name often.
This "temp.xls" has text that needs to be copyed to a new spreadsheet named "punchlist.xls" that does not change names. I know how to acheive this using record macro but I need the text to find the next row in the punchlist.xls file. Do all the macros run on the 1st file or do both files need macros?
View 6 Replies
View Related
Feb 4, 2009
using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..
View 9 Replies
View Related
Aug 17, 2006
Is it possible to lock in the formatting that would require 12 digits even if someone was copying and pasting to the cell?
We are looking to make our users add information into cells in the format 0-00000-00000-0. They are often copy and pasting, thus overwritting the custom format we have in those cells.
I've looked at validation, but custom only works for a formula.
View 9 Replies
View Related
Mar 17, 2008
I would like a macro that copys the contents of a cell and pastes it into the column before it and continues to copy that same number downward as long as the cell next to it contains numbers. So: ....
View 9 Replies
View Related
Mar 16, 2009
I have another query that I believe can only be solved using VBA.
In a row of input data, some cells are duplicated from previous cells in the same row but in some instances, the user needs to be able to input different information.
e.g. M4=F4 if left blank and the same would apply to columns N=G, O=H, P=I & Q=J.
I have done some searching but nothing quite matches my requirements and recording a macro only produces the following result.
View 10 Replies
View Related
Oct 31, 2008
As a pert of a spreadsheet I have 2 columns (M5:N400) of 400 cells deep. Each cell in these columns contains a formula that counts days, when appropriate. They can end up showing a value of blank, a number between 0 and 5, or any number greater than 5. (But very rarely greater than 30)
If the number is greater than 5 then it means that it has exceeded it's target time and I wish to highlight this with conditional formatting (CF). I have a CF on all cells in both columns that says.
If "The cell value" is "Greater than" "5"... then
format the background colour to a pale yellow.
It works, but doesn't work.
If the cell value is 0 to 5 it's unchanged. Good.
If the cell value is >5 it changes. Good.
BUT, if the cell is blank it also changes. Bad!!!
I assume it is because it is looking at the fact that there is a formula in the cell, even though the displayed value is "". So I tried a second level saying that if the cell is equal to "" then no format, but it still changes.
View 4 Replies
View Related
Aug 10, 2009
My excel recently the General number format so that when the general number fomat is used and I type a number in a blank cell with the general format, the number is always divided by 10.
E.g., I enter "102", the number is automatically improperly converted to 1.02.
However, if I enter "=102", the number is properly entered as 102.
How can I reset the General format to the original setting without this divide by 100 problem?
View 3 Replies
View Related
Jan 9, 2008
I know that I need to use Conditional Formatting, but I can't get the formula code correct.
This is what I need it to do:
If cell A2 is populated (not blank) AND cell B2 is blank, then cell B2 to be highlighted in bright red fill.
If cell A2 is populated (not blank) and cell B2 is also populated (not blank), then leave formatting as per normal
If cell A2 is blank then leave formatting as per normal
Basically, as soon as someone types anything into cell A2, cell B2 to turn red. Then once they have input something into cell B2, the red highlight can disappear. The inputs will be text, not numbers, so I can't use </>
I have tried varying circumstances of IFs, ANDs, NOTs, ISBLANKs etc and I can't crack
View 5 Replies
View Related
Sep 26, 2009
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck
View 2 Replies
View Related
Nov 14, 2013
I would like to highlight (conditional format) the "response required by" (Row A) cells / dates if the "current date" (cell $B$2) is exceeded AND if the "response provided By" (Row B) remains BLANK:
Current Date
=Today()
Response Required By
Response Provided By
29-10-2013
23-10-2013
[Code] .......
View 6 Replies
View Related
Nov 30, 2009
Need to copy the value from I2 to O2 if K2 is not blank. After that, L2 and P2 must be deducted from O2 (without affecting I2).
View 4 Replies
View Related
May 30, 2013
In a column, there are data, but in between of each data in a column, there are many blank cells. I would like to do a copy and paste of the first data, and paste it in the same column column, while the cells are blank, and stops when there is a cell with a data, which is different than the previous one, and do the same again and again.
View 4 Replies
View Related
Jan 5, 2009
I am looking for a macro that will open a file called File1.xls highlight only the cells in columns A:B that have content, then toggle back to another file called File2.xls, scan columns A:B, find the next blank cells within A:B and paste.
Here is a sample of what i am looking for:
Macro to scan File1.xls and find & copy only cells in columns A:B that are not blank.
File1.xls
_|__A_ |__B_|
1|_text|_text| (Copy)
2|_text|_text| (Copy)
3|_text|_text| (Copy)
4|_text|_text| (Copy)
5|blank|blank| (DO NOT Copy)
Toggle to File2.xls, scan columns A:B, paste content copied cells from File1.xls in next blank cells in columns A:B of File2.xls.
File2.xls
_|__A_ |__B_|
1|_text|_text| (Pre-existing text)
2|_text|_text| (Pre-existing text)
3|blank|blank| (Paste Row1 from File1.xls)
4|blank|blank| (Paste Row2 from File1.xls)
5|blank|blank| (Paste Row3 from File1.xls)
6|blank|blank| (Paste Row4 from File1.xls)
View 9 Replies
View Related
May 30, 2012
I have a Worksheet that is manually populated with file names into the 2nd cell of each column for columns A through D.
I need a macro that will loop through each column, starting at B2, c2 etc, and copy each value, and then paste it into the first available blank cell in column E
Before
YesterdayToday10108595101085661010859510110182
After
ALL10108595101085661010859510110182
Ive tried a few combinations of code, but nothing seems to be working.
View 2 Replies
View Related
Dec 1, 2006
It's finals week and I need to figure out this last VBA problem for my excel class. (Yes, we are allowed any outside help.)
The code below is something I tried to develope to do the following:
1. Search "N" column for blank cell
2. If cell not blank then select
3. Copy select cell information 3 cells to the left
4. Continue function to next row
I cannot figure out two things with this code. I need the code to copy/paste the information in the cell versus equalling values. Sometimes there is a small .jpg or .gif file inserted into the cell, so copy/paste is desired. Second is I need the function to not copy formulas if possible. I cannot figure either of things commands out. Hoping message boards can help me as they always have in the past.
Code: .....
View 9 Replies
View Related
Mar 6, 2008
The attached picture shows the problem. I want to automatically fill the blank cells in column A with the value immediatey above them. I want to do the same for column B and C. I have never worked with VB except to copy key strokes.
View 3 Replies
View Related
Mar 4, 2008
I have a workbook with 12 sheets. On the 12th sheet I need some VB to go to each of the other tabs and find the letter “E” or “H” in column F. Once the “E” or “H” is found in column F and a number =>9 is found in column E then copy that row from column A-F and paste this row to sheet 12.
On sheet 12, I would like to be able to paste the row in a way that will hold the date in column A. The date can also be copied from each sheet found in cell E1. Also, the tab name has to be copied to sheet 12 with the row the E or H was found if “=>9 criteria” was met.
View 9 Replies
View Related
Jun 23, 2009
If cells in column J are not blank, values in column K must be copied from column I.
If not, i.e. cells in column J are blank, values in column K must be copied from column I + the fixed value in cell $C$2.
Can't get "if" nor "notblank" formula working.
View 6 Replies
View Related
Feb 19, 2009
I receive a huge spreadsheet with account numbers in col A and names in col b with dates in col g and amounts in h. However if there is more than one date and amount for the same account number and name, the people/program who originated the data left those fields blank.
In order to sort the list by name by date, I have been manually copying the data from the row immediately above to the blank row(s). (L O N G process) So if I have ...
View 9 Replies
View Related
Dec 13, 2007
What I have are invoices that span an entire month that I need to convert into a format for the billing department. The columns I have right now are: "Invoice Number"Cost CenterAccountFundType of Service" Date of Completion"Unit PriceQty Total Price
I need to generate a file with the headings as: Cost CenterFund"Date of Completion"Account Total Price. Since Total Price only appears on the rows that have the final information I need, I want to have a script that grabs every line in which the Total Price column has data, generate a file of the summary lines, then throw that through another macro to reformat it into the final format, or just do it in one step. In summary I need to grab rows where Column I is not empty, whats the best way to do this?
View 3 Replies
View Related
Jun 5, 2008
Basically this is what I want to do:
1. Search a specific column (Column 21/U) for non-blank values in Worksheet 1
2. Copy the entire row containing the non-blank values
3. Paste these rows into Worksheet 2.
Repeat steps 1-3 an additional 2 times, where Worksheet 1 is always searched but one more column to the right (ex. Column 22/V) is the target column for the search, then the rows are pasted into the next Worksheet (for ex. Worksheet 3)
View 7 Replies
View Related
Jul 25, 2002
I wonder how to copy a conditional format from one cell to its current row.
I've attached an example (if B3 is green then I'd like its whole row turns green too).
I think there should be an "easy" way to turn the whole rows green, red or whatever color based on the B column formats (or colors)..........
View 9 Replies
View Related
Nov 21, 2006
Is there a macro that can copy the information of any italicize item in Sheet1 to Sheet2 and automatically italicize the item in Sheet3 if the same item exist?
In my attached example:
In Sheet1, B3 to B8 contain information about a restaurant.
I want the macro to copy any restaurant (and its information) that is italicize to Sheet2. And in Sheet3, any restaurant that is in italic in Sheet2 will become italicize in Sheet3 if the name is match. Is there such a macro that can do these things?
View 5 Replies
View Related
Jan 8, 2008
I have been working several weeks on producing a macro that can quickly eliminate blank lines in an .xls file which is created by exporting a Crystal Report into Excel. The macro can do that, but the big problem is that it freezes up the users PC for 3-5 minutes. If you go into Task Manager you can observe that CPU usage is at 100%. The .xls files that I am trying to run this macro against are around 50,000 rows long with data in about 10 columns. About 2/3 of those rows are blank. This is an inherent problem when exporting Crystal Reports into Excel. I went to Business Objects website (Crystal Reports) and their solution is simply not an option. They suggest removing If-Then-Else logic. I simply cannot do that because that is the heart of how the report selects data for each row to print in the report.
Below is the code that I wrote with documentation to explain how it works. If you would like to try it I attached an .xls file. It is only 800 rows long so that I could stay under the forum's requirement of attaching nothing larger than a 48.8 kb file. The file has only one column, 800 rows long. You may want to copy and paste it a few times to make it longer. When running the macro it will open a dialog box so that you can go wherever you copied the file, and open it up.
Sub OpenXLSFile_DeleteBlankLines()
Dim fileToOpen As String
Dim lRows As Long
'open dialog box for user to access an Excel file for deleting blank lines
fileToOpen = Application. GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Select an .xls file to open for deleting blank lines...", MultiSelect:=False)
If fileToOpen <> "" Then
Application.Workbooks.Open fileToOpen
End If.......................
View 4 Replies
View Related
