Copy cells that contain text to another cell

[jmkerzic]

I need a formula that will copy text in column f or g (the last cell that contains text) and paste it in column e. If f or g have no text than do not paste anything in column e

Example column f (Cornish) I need copied to column e (c). Column g (freedman) to column e (And).

Column e Column F Column G

DECKER
C	CORNISH
SACHAR
SHARIPOVA
DHALIWAL
JAHANT
MCLAUGHLIN
GOODMAN
MCGILL
YEARWOOD
J	ORTIZ
AND	JOEL	FREEDMAN
SMITH
BAKARE
LOZANO
FELDTMAN
ROSEAU
WALSH
MEYER
GITA
KORBAL
UGWU
EGAN
VAN	HALSEMA

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Yongle]

Try this on a copy of your data. Assumes that
- column E can be used to find the last row used
- data starts in row 2

Sub PasteValuesToE()
    Dim r As Long, E As Range, F As Range, G As Range
        With ActiveSheet
            For r = 2 To .Cells(Rows.Count, "E").End(xlUp).Row
                Set E = .Cells(r, "E")
                Set F = .Cells(r, "F")
                Set G = .Cells(r, "G")
                    If Len(G) > 0 Then
                        G.Copy E
                        G.Clear
                    ElseIf Len(F) > 0 Then
                        F.Copy E
                        F.Clear
                    End If
            Next r
        End With
End Sub

 

[jmkerzic]

Thanks for the info. Works just they way I wanted it to.

 

[jmkerzic]

I need to go to coumn I in the fomula:

I added it this to it but it only going to column G:


Sub PasteValuesToE()
Dim r As Long, E As Range, F As Range, G As Range, H As Range, I As Range
With ActiveSheet
For r = 2 To .Cells(Rows.Count, "E").End(xlUp).Row
Set E = .Cells(r, "E")
Set F = .Cells(r, "F")
Set G = .Cells(r, "G")
Set H = .Cells(r, "H")
Set I = .Cells(r, "I")
If Len(G) > 0 Then
G.Copy E
G.Clear
ElseIf Len(F) > 0 Then
F.Copy E
F.Clear
ElseIf Len(G) > 0 Then
G.Copy E
G.Clear
ElseIf Len(H) > 0 Then
H.Copy E
H.Clear
ElseIf Len(I) > 0 Then
I.Copy E
I.Clear
End If
Next r
End With
End Sub

 

[jmkerzic]

I figured it out. Thanks for the help once again.

 

[Yongle]

If you figured it out, you could post it for others reading the thread to benefit