IF adjacent cell empty Copy from Other Column

ChrisOK · #1 02-20-2018, 01:22 PM

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!

jeffreybrown · #2 02-20-2018, 05:07 PM

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

ChrisOK · #3 02-20-2018, 09:11 PM

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!

jeffreybrown · #4 02-21-2018, 05:29 AM

Thanks Chris and you are very welcome. Glad to hear it's working for you.

ChrisOK · #5 03-06-2018, 09:25 AM

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

jeffreybrown · #6 03-07-2018, 03:24 PM

Hi Chris,

If you want to say, not nothing, then try...

<> ""

ChrisOK · #7 03-07-2018, 11:08 PM

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

It does not seem to be acceptable -- it turns red

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, 5).Value = Cells(i, 9).Value
        Next i
    End With
    Columns(9).NumberFormat = "mm/dd/yyyy"
End Sub

IF Col I (9) is NOT empty, THEN copy/paste whatever is sitting in Col I (9) into adjacent Col E (5)

jeffreybrown · #8 03-08-2018, 05:31 AM

Sorry Chris, should have given you a little more.

Code:

If Cells(i, 9).Value <> ""

There is no need for the equal sign as <> is the operator

ChrisOK · #9 03-08-2018, 09:17 PM

Ok perfect! That was the problem -- it's working great now.. (happy dance!)

Thanks so much!!!

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