VBA copy and paste w/ conditions

[hiccup2020]

I'm trying to copy elements in column B to column C if column A is blank. Please help!

Thanks!

 

[Joe4]

Welcome to the Board!

For row 1, the formula to put in column C would be:
=IF(A1="",B1,"")
and copy down for all your rows of data.

 

[hiccup2020]

Actually, it's little more complex. Column A I have unique ID, Column B includes work activity associated with this unique ID and the person doing the work. The person doing the work will not be assigned a unique ID so there will be a blank cell. I would like to move the 'person doing the work' to column C but at the same time align it with the work activity.

This is how data comes to me.

ID	Work Activity
1​	Lay out foundation
	Cement expert
2​	Build framework
	Builder
	Engineer

This is how I would like it to be

ID	Work Activity	Resources
1​	Lay out foundation	Cement Expert
2​	Build framework	Builder
2​	Build framework	Engineer

I'm not sure if this can be done just through formulas in excel. I'm thinking that a macro needs to be used??

 

[Joe4]

That is significantly different than you original question.

I see that you are new here, so here is a little friendly tip to maximize your chances of getting the help you need. The last reponse you posted, that is what you want as your original post, so people can see exactly what you are after from the beginning. Otherwise, you may get someone who responds, and then finds out the request is over their head, and they may not be able to help. And then your thread is taken off the "Zero Reply Posts" listing, so many people will not see it as a new, unanswered thread. So the chances of getting help are greatly reduced, since less people are seeing it.

OK, back to your question. Here is some VBA code that should do what you want. I added lots of comments to the code to explain what each step is doing.

Sub MyMacro()

    Dim r As Long

    Application.ScreenUpdating = False
    
'   Set intitial row value of 3
    r = 3
    
'   Put title on column C
    Range("C1") = "Resources"

'   Loop through column B
    Do
'       Exit if no more items in column B
        If Cells(r, "B") = "" Then Exit Do
'       Check to see if there is a value in column A
        If Cells(r, "A") <> "" Then
'           If there is, add one to r to move to next row
            r = r + 1
        Else
'           Check to see if there is a value in column C in row above
            If Cells(r - 1, "C") <> "" Then
'               If there is, move column B entry to column C...
                Cells(r, "C") = Cells(r, "B")
'               ... and copy values in columns A and B from row above
                Cells(r, "A") = Cells(r - 1, "A")
                Cells(r, "B") = Cells(r - 1, "B")
'               Move to next row
                r = r + 1
            Else
'               If there is no value in column C in row above, move value up there...
                Cells(r - 1, "C") = Cells(r, "B")
'               ...and delete current row
                Rows(r).Delete
            End If
        End If
    Loop
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[hiccup2020]

That makes sense. Thanks for your advice and council. I'll keep that in mind going forward. This is so awesome! Thank you!!!!

 

[Joe4]

You are welcome.