VBA code to move data from 1 column to another based on there being data in a 3rd column

[clau818]

Hello,

I am a vba novice and need help with a code. I am trying to cut and paste the data in column A over to column B only if there is data in column C. if no data in column C, then do nothing. I need to work on my entire sheet.

For example;

Column A	Column B	Column C
ALEKO00 Aleksey Korzun
BB10072016		10/11/2016
ALIT00 A Little Tech, LLC
OCT2016		10/28/2016
ALIV00 Alison & Ivy
363086		11/7/2016

<tbody>
</tbody>

Should look like this:

Column A	Column B	Column C
ALEKO00 Aleksey Korzun
	BB10072016	10/11/2016
ALIT00 A Little Tech, LLC
	OCT2016	10/28/2016
ALIV00 Alison & Ivy
	363086	11/7/2016

<tbody>
</tbody>

Thanks in advance for your help!

 

[MattH1]

Enter this into Cell B1 and drag down accordingly:

=IF(ISBLANK($C1),"",$A1)

Edit: If you want to do this in VBA let me know, but it's a very simple equation in Excel that can just be dragged down.

 

[mrmmickle1]

Try this out. I commented it for your reference.:

[COLOR=#0000ff]Sub [/COLOR]Test()

 [COLOR=#0000ff]   Dim [/COLOR]LngLp [COLOR=#0000ff]As Long[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]Lrow [COLOR=#0000ff]As Long[/COLOR]
    
    Lrow = Cells(Rows.Count, "A").End(xlUp).Row[COLOR=#008000] 'Define Last Row[/COLOR]
    
[COLOR=#008000]    'Increment through rows....[/COLOR]
[COLOR=#0000ff]    For[/COLOR] LngLp = 2 [COLOR=#0000ff]To [/COLOR]Lrow
[COLOR=#008000]        'Check for value in Col C[/COLOR]
     [COLOR=#0000ff]   If[/COLOR] Cells(LngLp, "C") <> "" [COLOR=#0000ff]Then[/COLOR]
            Cells(LngLp, "B") = Cells(LngLp, "A") [COLOR=#008000]'Put Value from Col A into Col B[/COLOR]
            Cells(LngLp, "A").ClearContents [COLOR=#008000]'Clear Cell Value[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
    
  [COLOR=#0000ff]  Next[/COLOR] LngLp

[COLOR=#0000ff]End Sub[/COLOR]

 

[frabulator]

This is a quick VBA macro that should do the trick. It works by selection. You can either select all columns A B and C or just A. Basically, make sure that the column that contains the data you want to duplicate is the first column in your selection and your criteria is the third column in your selection. After that run the macro. It will loop through every selected row and the the first, second and third columns.

Sub SelectSwap()
    Dim rws As Long
    Dim rwe As Long
    Dim cls As Long


    
    rws = Selection.Row
    rwe = Selection.Row + Selection.Rows.Count - 1
    cls = Selection.Column
    
    
    For r = rws To rwe
        If ActiveSheet.Cells(r, cls + 2).Value = "" Then
        Else
            ActiveSheet.Cells(r, cls + 1).Value = ActiveSheet.Cells(r, cls).Value
        End If
    Next
    
End Sub

Let me know if this works for you
~Frab

 

[clau818]

Thank you! this worked like a charm! and thanks for the references as well!