Copy Data from One Column to Another

[computerman29651]

I need a formula that will look in column E, find 'CO', and copy over the data that is in column L. If the data in column E does not equal 'CO', then I want to leave it alone.

For example.

Column E Column L
84 2.0
CO 1.0
55 3.0
CO 1.0
60 3.0
90 9.0
45 2.0
CO 1.0
100 4.0

If any other information is need, please let me know.

 

[PHV]

Have you tried the Find method of the Range object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Sub test()
  Dim r As Range, rng As Range, lFirst As Long
  
  If ActiveSheet.[E1].Value = "" Then
    Set rng = ActiveSheet.[E1].End(xlDown)
  Else
    Set rng = [E1]
  End If
  
  For Each r In ActiveSheet.Range(rng, rng.End(xlDown))
    With r
      If .Value = "CO" Then
        ActiveSheet.Cells(.Row, "L").Value = .Value
      End If
    End With
  Next

End Sub

Skip,

_[red][/red]

 

[computerman29651]

Skip,

I appreciate the help.

The code you provided, takes what is in column E and replaces what is in column L. I would like it to do the opposite based on the information that is in column E.

 

[SkipVought]

Then just replace the order of the assignment statement.

Skip,

_[red][/red]

 

[computerman29651]

PHV,

I am not sure what you are referring to here....

Have you tried the Find method of the Range object ?

 

[computerman29651]

Skip,

I tried to replace the order of the assignment statement, but then I have to put in data that is found in column L.

I want the formula to look at column E, and then copy data over from column L.

Once again, thank you for all your help.

 

[kjv1611]

computerman,

PHV is referring to the Find Method built into Excel. It is the same method that runs when you do the <Ctrl> + <F> to search for a string of text. You can run this same command from within VBA. The help file has info on it, there are tons of examples on the web, and you can get the code directly by recording a macro where you search for text on the spreadsheet.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Sub test()
  Dim r As Range, rng As Range, lFirst As Long
  
  If ActiveSheet.[E1].Value = "" Then
    Set rng = ActiveSheet.[E1].End(xlDown)
  Else
    Set rng = [E1]
  End If
  
  For Each r In ActiveSheet.Range(rng, rng.End(xlDown))
    With r
      If .Value = "CO" Then
        [!].Value = ActiveSheet.Cells(.Row, "L").Value[/!]
      End If
    End With
  Next

End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886