CODEWITHSUNDEEP
excelvba
Mien Yi
Mien Yi

Reputation: 45

excel vba copy cells to another sheet if cell value is greater than 0

I'm trying to copy cell values if a cell value in another column is greater than 0.

In Sheet1:

G3 : G25 = Name

L3 : L25 = ActiveX Check box (if checked then price shows, else price = empty)

M3 : M25 = Price (corresponding to name)

In Sheet2:

A82 : A104 = empty cells

I want to copy Names on G to empty cells (Sheet2), if the price is shown (which means cells on L are checked).

This is what I've tried but I'm getting an error.

Sub option_list()
    Dim Source As Worksheet, Target As Worksheet
    Dim i As Long, j As Long

    Application.ScreenUpdating = False
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    j = 82

    For i = 3 To 25
        If Source.Cells(i, 13).Value = 0 Then
            i = i + 1
        Else
            Source.Cells(i, 10).Copy
            Target.Cells("A" & j).PasteSpecial xlPasteValues
            j = j + 1
        End If
    Next i
End Sub

Upvotes: 1

Views: 1845

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Maybe this would work in your intended way.

  • The for loop will always take the next value in the iteration, therefore we can remove i = i + 1.
  • Since we want to copy everytime we have a value that is not 0, we can express this as <> 0
  • I think you get an error here, since you mixed it how we write for range(). For cells() it's a bit different. This part will cause error: Target.Cells("A" & j). The cells is written like: Cells(row, column) -> Cells(j, "A").

Full revised code:

Option Explicit

Sub option_list()
Dim Source As Worksheet, Target As Worksheet
Dim i As Long, j As Long

Application.ScreenUpdating = False
Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")

j = 82

For i = 3 To 25
    If Source.Cells(i, 13).Value <> 0 Then
        Source.Cells(i, 10).Copy
        Target.Cells(j, "A").PasteSpecial xlPasteValues
        j = j + 1
    End If
Next i
End Sub

Upvotes: 1

Related Questions