Copy values if zero, if not zero.

[Charles_]

Hi there,

I am running into an issue when trying to run the following:

*If values in column A are greater than zero, paste values in column C
*If values in column A are zero, copy values from column B (in same row) and paste in column C

Columns are of variable lenght and values begin from A2 onwards.

So far, I've tried the first step only without success using the below. Ideally I would not like to choose "range A:A)" but something that will run trough cells with data only.

Can somebody please point me in the right direction?

Thanks!!!

"

Sub CopyValues()
     
    Dim x As Range
    For Each x In Range("A:A")
        If x.Value > 0 Then
            x.Select
            Selection. Copy
            Columns("C:2").Select
    ActiveSheet.Paste
            End If
    Next i

Dim z As Range
    For Each z In Range("A:A")
        If z.Value = 0 Then
            Columns("B:B").Select
            Selection. Copy
            Columns("C:2").Select
    ActiveSheet.Paste
            End If
    Next z


End Sub

"

 

[Zack Barresse]

Hi,

You're somewhat close.

Sub CopyValues()

    Dim WS As Worksheet
    Dim iLastRow As Long
    Dim i As Long

    Set WS = ActiveSheet
    Let iLastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To iLastRow
        If WS.Cells(i, 1).Value > 0 Then
            WS.Cells(i, 3).Value = WS.Cells(i, 1).Value
        ElseIf WS.Cells(i, 1).Value = 0 Then
            WS.Cells(i, 2).Value = WS.Cells(i, 1).Value
        End If
    Next i
    
End Sub

This will move values only, not formats.

HTH

 

[Charles_]

Thank you Zack!

The code is working for the first step:

*If values in column A are greater than zero, paste values in column C

However for step 2 (*If values in column A are zero, copy values from column B -in same row- and paste in column C
), the code is copying the zeros in column A and pasting them into column B and not pasting anything in Columnn C, as opposed to copying the values on column B and pasting into Column C. Can you please help me fix this?

Also, if it isn’t too much to trouble, could I ask you to put comments in the code. I would like to have a better understanding of the code for future reference

Thanks again!!</SPAN>

Hi,

You're somewhat close.

Sub CopyValues()

    Dim WS As Worksheet
    Dim iLastRow As Long
    Dim i As Long

    Set WS = ActiveSheet
    Let iLastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To iLastRow
        If WS.Cells(i, 1).Value > 0 Then
            WS.Cells(i, 3).Value = WS.Cells(i, 1).Value
        ElseIf WS.Cells(i, 1).Value = 0 Then
            WS.Cells(i, 2).Value = WS.Cells(i, 1).Value
        End If
    Next i
    
End Sub

This will move values only, not formats.

HTH

 

[Michael M]

Maybe

Sub MM1()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Range("A" & r).Value > 0 Then
        Range("C" & r).Value = Range("A" & r).Value
        Else: Range("B" & r).Value = Range("A" & r).Value
    End If
Next r
End Sub

 

[Zack Barresse]

Ah, I misread. Sorry. Just change the one line in the ElseIf statement, as with the below code...

Sub CopyValues()

    '/// Delcare variables
    '/// Important to declare a worksheet variable so we explicitly reference the sheet
    '///    we're working on.  Set to be specific if you don't always want to work on
    '///    the active sheet.
    '/// Use two long variables to set last row and step through rows
    Dim WS As Worksheet
    Dim iLastRow As Long
    Dim i As Long

    '/// Set worksheet variable
    Set WS = ActiveSheet
    
    '/// Set last row variable
    Let iLastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
    
    '/// Loop from row 2 to the last found row variable
    For i = 2 To iLastRow
    
        '/// Check value of cell in col A of the iterative row
        If WS.Cells(i, 1).Value > 0 Then
        
            '/// If it's greater than zero, put value in column C
            WS.Cells(i, 3).Value = WS.Cells(i, 1).Value
        
        '/// Check if value is equal to zero in col A of the iterative row
        ElseIf WS.Cells(i, 1).Value = 0 Then
        
            '/// If it's equal to zero, put value from col B into column C
            WS.Cells(i, 3).Value = WS.Cells(i, 2).Value
            
        End If
        
    Next i
    
End Sub

 

[Zack Barresse]

That won't work Michael, not if the value is equal to zero. Or, rather if the value is less than zero. The conditions are wrong. You'd just be copying from B regardless if the value from A wasn't greater than zero. Make sense?

 

[Michael M]

Hi Zack, I take your point....but I made the assumption....probably foolishly....that the numbers would be zero or greater !!

 

[Charles_]

Thank you both so much.

Thanks for the comments Zach, it really helps. The code does exactly what I need!

If I can bother you with one last question...Could I make it so that if value in column D is i.e "OK" then paste values from A to C but if not "OK" paste B to C?

I would like to have type of code for a different step in the macros. Would something like this work? (its based on your code)

"Sub test ()

Dim WS As Worksheet
Dim iLastRow As Long
Dim i As Long

Set WS = ActiveSheet

Let iLastRow = WS.Cells(WS.Rows.Count, "D").End(xlUp).Row

For i = 2 To iLastRow


If WS.Cells(i, 1).Value = OK Then

WS.Cells(i, 3).Value = WS.Cells(i, 1).Value


ElseIf WS.Cells(i, 1).Value Is Not OK Then

WS.Cells(i, 3).Value = WS.Cells(i, 2).Value

End If

Next i

End Sub

That won't work Michael, not if the value is equal to zero. Or, rather if the value is less than zero. The conditions are wrong. You'd just be copying from B regardless if the value from A wasn't greater than zero. Make sense?

 

[Rick Rothstein]

Thank you both so much.
Thanks for the comments Zach, it really helps. The code does exactly what I need!
If I can bother you with one last question...Could I make it so that if value in column D is i.e "OK" then paste values from A to C but if not "OK" paste B to C?
I would like to have type of code for a different step in the macros.

Here is a different macro that will do what you asked for originally without looping through the cells...

Sub CopyValues1()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate("IF(A2:A" & LastRow & "=0,B2:B" & LastRow & ",IF(A2:A" & _
                                     LastRow & ">0,A2:A" & LastRow & ",C2:C" & LastRow & "))")
End Sub

Using the same "structure", here is the code modified to handle your latest request...

Sub CopyValues2()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate("IF(D2:D" & LastRow & "=""OK"",A2:A" & LastRow & ",B2:B" & LastRow & ")")
End Sub

 

[Charles_]

Thank you Rick!

It is amazing how helpful this forum is!

Thanks again!