#1
|
|||
|
|||
IF adjacent cell empty Copy from Other Column
Looking for vba sub solution that will do the following: On "Active.Sheet", IF Col I cell is empty, then COPY/PASTE adjacent cell content from Col E into Col I. *Start on row 2 (since header is on row 1) *Date formats of Col I when complete should be: mm/dd/yyyy Saw some ideas regarding copying from wkbk to wkbk and sheet to sheet but I simply need it from the adjacent column.. Thanks greatly! |
#2
|
|||
|
|||
Try...
Code:
Sub CopyandPaste() Dim LR As Long Dim i As Long With ActiveSheet LR = .Range("I" & Rows.Count).End(xlUp).Row For i = 2 To LR If Cells(i, 9).Value = "" Then Cells(i, 9).Value = Cells(i, 5).Value Next i End With Columns(9).NumberFormat = "mm/dd/yyyy" End Sub |
#3
|
|||
|
|||
THANK YOU Jeffrey!! That works perfectly! LOVE it!
Reputation points/stars for you! Update: argh! tried to click Reputation - but says I've got to go spread the love elsewhere first -- so I'll come back around to get you some points asap! Last edited by ChrisOK; 02-20-2018 at 09:12 PM. Reason: reputation explanation |
#4
|
|||
|
|||
Thanks Chris and you are very welcome. Glad to hear it's working for you.
|
#5
|
|||
|
|||
Reverse Copy Paste - If not blank paste to adjacent cell
I found that the reverse was needed and thought it would be easy to revert using the "<>" and changing the column refcs - but it's not working for some reason? Hoping someone can see what I'm missing?
IF Col I (9) is NOT empty, THEN copy/paste that date that's sitting in I (9) into adjacent Col E (5) Code:
Sub CopyandPaste() Dim LR As Long Dim i As Long With ActiveSheet LR = .Range("I" & Rows.Count).End(xlUp).Row For i = 2 To LR If Cells(i, 9).Value = "<>" Then Cells(i, 9).Value = Cells(i, 5).Value Next i End With Columns(9).NumberFormat = "mm/dd/yyyy" End Sub |
#6
|
|||
|
|||
Hi Chris,
If you want to say, not nothing, then try... <> "" |
#7
|
|||
|
|||
Like this? Changing this row:
Code:
If Cells(i, 9).Value = "<>" Then Cells(i, 9).Value = Cells(i, 5).Value To this? Code:
If Cells(i, 9).Value = <>"" Then Cells(i, 9).Value = Cells(i, 5).Value Code:
Sub CopyandPaste() Last edited by ChrisOK; 03-07-2018 at 11:12 PM. Reason: expansion |
#8
|
|||
|
|||
Sorry Chris, should have given you a little more.
Code:
If Cells(i, 9).Value <> "" |
#9
|
|||
|
|||
Ok perfect! That was the problem -- it's working great now.. (happy dance!)
Thanks so much!!! |
Tags |
if empty copy adjacent |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Increase number in cell, based on value in adjacent cell | scottyb | Excel | 3 | 02-02-2017 03:51 AM |
If column B cell is a certain value then copy and paste the value to column A | Snaybot | Excel Programming | 1 | 12-01-2015 07:10 PM |
Data validation,force cell to be filed with number if respective cell is not empty | nicholes | Excel Programming | 0 | 08-01-2015 09:08 AM |
How to conditionally format cells in Col. A if it matches adjacent cell in Col. B? | alshcover | Excel | 2 | 06-03-2014 12:50 PM |
Conditional Formatting Expiration Dates Based on Text in Adjacent Cell | Frogggg | Excel | 1 | 10-25-2011 08:44 PM |