conditional copy/paste

[tjktm]

I have a large worksheet with data from Row 10 to Row 433. I need code to copy cell value (dollar amount) from column L to column O of the same row if the cell in column A contains the letter “S”. The cells that should allow for changes will have content S.1 through S.16. The other Column A cells may be blank or have other content but will not have the letter “S”

Not all rows will have content in Column A but will have content in columns L and O. I need to avoid making changes to those rows which do not have content “S” in column A.

I've searched throughout the forum and online trying to find something but have had no luck.

Hope you can help

TJ

<style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-536859905 -1073732485 9 0 511 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:8.0pt; margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:8.0pt; line-height:107%;} @Page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>

 

[davesexcel]

You can loop through column A and find a string that has an "s" in it.

Sub SelectS()
    Dim FrstRng As Range
    Dim c As Range
    Set FrstRng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

    For Each c In FrstRng.Cells
        If LCase(c) Like "*" & "s" & "*" Then
            Cells(c.Row, "O").Value = Cells(c.Row, "L").Value
        End If
    Next c

End Sub

 

[My Aswer Is This]

If you only want to search rows 10 to 433
And the only value that will be in column A is "S" then try this:

Sub Test()
'Modified 5/10/2018 2:35 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 10 To 433
        If Cells(i, 1).Value = "S" Then Cells(i, "L").Copy Cells(i, "O")
    Next
Application.ScreenUpdating = True
End Sub

 

[davesexcel]

Ah yes, if you want the range to start at A10, change A1 to A10, thought I had A10 in the code.

@my-aswer-is-this the cell should contain "S" not equal "S"

 

[tjktm]

Thanks. I did a quick run with it and it didn't work. I noticed that I said Column O instead of N. It needs to copy Column L to Column N. Should be an easy fix. I will change the appropriate letters when i get back in the office in the am.

Thanks to both of you for the quick reply and help! I'll reach back out if i can't get it to work.

TJ

 

[My Aswer Is This]

If your referring to my script then you should be able to correct it.
Just change o to n

If Cells(i, 1).Value = "S" Then Cells(i, "L").Copy Cells(i, "N")
​

 

[tjktm]

I made a couple of changes to the code due to :

"S" is not the only character in searched cell in Column A
I need to copy "L" to "N"

Everything works except that it is copying the formula from "L" , and i just need it to copy the value.

Sub Copy_test()

Application.ScreenUpdating = False

Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 10 To 433
If Cells(i, 1).Value Like "*" & "S" & "*" Then Cells(i, "L").Copy Cells(i, "N")
Next

Application.ScreenUpdating = True

End Sub

Can you help?

 

[davesexcel]

Look at the difference between my code and your code, you are using copy & paste, I am using Value=Value

 

[JackDanIce]

Alt script to test:

Sub Copy_Test()

    Dim arr()   As Variant
    
    Application.ScreenUpdating = False
    
    arr = Cells(10, 1).Resize(424, 14).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        If LCase$(arr(x, 1)) = "s" Then arr(x, 14) = arr(x, 12)
    Next x
    
    Cells(10, 14).Resize(UBound(arr, 1)).Value = Application.Index(arr, 0, UBound(arr, 2))
    
    Application.ScreenUpdating = True
    
    Erase arr
    
End Sub