CODEWITHSUNDEEP
excelvba
EL BAILARIN
EL BAILARIN

Reputation: 33

How do I copy/paste cell values if the condition is true and enter "0" if the cell being looked at in the condition isn't there?

I'm very new to VBA and I haven't been able to figure this out yet.

I need a code that copies cell .Range("E" & i) to cell Sheets("CANmonitor").Range("C" & k) if Column B = 1731.

However, it needs to look at .Range("C" & i) as well and set Sheets("CANmonitor").Range("C" & K) = 0 if .Range("C" & i) is between 6 & 16 or between 28 & 39.

To simplify, the value on column C jumps from 6 to 16 and from 28 to 39. I need the code to enter 0s for the missing values (ex: 1731.6 = data from cell ("E" & i) on sheet DIC2; 1731.7 = 0). Here's what I have so far:

Sub DIC2toCAN()
    Dim LR As Long, i As Long, k As Long

    With Sheets("DIC2")
        LR = .Range("B" & Rows.Count).End(xlUp).Row

        k = 1
        For i = 1 To LR
            With .Range("B" & i)
                If .Value = "1731" Then
                    If .Range("C" & i) is between 6 & 16 or is between 28 & 39 Then
                        Sheets("CANmonitor").Range("C" & k) = 0
                    Else
                        Sheets("DIC2").Range("E" & i).Copy _
                        Destination:=Sheets("CANmonitor").Range("C" & k)
                        k = k + 1
                    End If
                End If
            End With
        Next i
    End With
End Sub

Upvotes: 2

Views: 559

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Here is another version. This code declares all the objects beforehand and then works with it. This way if later you need to change the sheet name, you do it at only one place. This code (Untested) also uses Select Case for simplicity.

Sub DIC2toCAN()
    Dim LR As Long, i As Long, k As Long
    Dim wsI As Worksheet, wsO As Worksheet

    '~~> Set your sheets here
    Set wsI = ThisWorkbook.Sheets("DIC2")
    Set wsO = ThisWorkbook.Sheets("CANmonitor")

    With wsI
        LR = .Range("B" & Rows.Count).End(xlUp).Row

        k = 1

        For i = 1 To LR
            '~~> Use Val to get the value
            If Val(.Range("B" & i).Value) = 1731 Then
                '~~> Use select case for simplicity
                Select Case Val(.Range("C" & i).Value)
                Case 6 To 16, 28 To 39
                    wsO.Range("C" & k).Value = 0
                Case Else
                    .Range("E" & i).Copy wsO.Range("C" & k)
                    k = k + 1
                End Select
            End If
        Next i
    End With
End Sub

Upvotes: 2

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

I cleaned up your code just a bit to correct a few things (formatting, missing dot, removed problematic With statement), and most importantly, I added the syntax for the "between" tests that you needed.

Sub DIC2toCAN()

Dim LR As Long, i As Long, k As Long
With Sheets("DIC2")
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
    k = 1
    For i = 1 To LR
        If .Range("B" & i).Value = "1731" Then
            Dim test As Variant
            test = .Range("C" & i).Value
            If (test >= 6 And test <= 16) Or (test >= 28 And test <= 39) Then
                Sheets("CANmonitor").Range("C" & k) = 0
            Else
                Sheets("DIC2").Range("E" & i).Copy Destination:=Sheets("CANmonitor").Range("C" & k)
                k = k + 1
            End If
         End If
     Next i
End With
End Sub

Upvotes: 1

FreeMan
FreeMan

Reputation: 5687

You are very, very close.

Change this:

If .Range("C" & i) is between 6 & 16 or is between 28 & 39 Then

to

If (.Range("C" & i) >= 6 and .Range("C" & i) <= 16) OR _
   (.Range("C" & i) >= 28 and .Range("C" & i) <=39) Then

Also, simplify this:

Sheets("DIC2").Range("E" & i).Copy Destination:=Sheets("CANmonitor").Range("C" & k)

to this:

Sheets("CANmonitor").Range("C" & k) = Sheets("DIC2").Range("E" & i)

It gives the same result, but requires less typing and is easier to read.

Upvotes: 1

Related Questions