Macro for...If column C equals "Cherry", copy and paste it to column E

[duugg]

Hi all,

I need a Macro that does this...

If column C equals "Cherry", copy and paste it to column E

I'm sure this is an easy one for you guys. I am slowly getting the hang of this code stuff.

Thanks

[VBA Noob]

Why not adapt the code you got in your last post

VBA Noob

[duugg]

Hi Noob,

I thought about it but the code would be different I think. Let me explain why...

Grape Code says...
If grape is in column C, copy column D and paste into E.

Cherry Code says
If cherry is in column C, copy that same column (C) and paste into E.

I thought about it for a moment and did try to change to code, but it didn't work. Here's what I did...

Dim rcell As Range
For Each rcell In Range("C:C")
If rcell.Text = "Cherry" Then rcell.Offset(0, 2).Value
Next rcell
End Sub

What did I do wrong?

Thanks

[VBA Noob]

You didn't tell it what you want the offset to be

e.g

Change

rcell.Offset(0, 2).Value

to

rcell.Offset(0, 2).Value = "Cherry"

also you should only go down to the last cell instead of the whole Column to increase speed

e.g

Dim rcell As Range
Dim Wrd As String
Wrd = "Cherry"
    For Each rcell In Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
        If rcell.Text = Wrd Then rcell.Offset(0, 2).Value = Wrd
    Next rcell

VBA Noob

[duugg]

Noob,

As always, great code! After thinking about both codes (Cherry and Grape) for a moment, I realized that what I truly want is a bit of a variance to this.

If this should be a new thread, my apologies but I think it's relevant to this thread.

I think what I really want Excel to do is this...

1. IF a row in column E has data in it, leave that whole row alone.
1. IF a row in column E has NO data in it, find the first column in the same row that has data in a cell (right to left...ie first D, then C, then B) then copy and paste the contents of that cell into column E.

Thanks

[VBA Noob]

Maybe

Dim rcell As Range
    For Each rcell In Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        With rcell
            If IsEmpty(.Value) Then
                .Value = .End(xlToLeft).Value
            End If
        End With
    Next rcell

It assumes data always in Col D, B or C and you want to go to last cel in Col E instead of whole column

VBA Noob