How do i use the IF condition depending on the input contained in a column (not in a cell)?

Question

I have an excel-workbook containing two worksheets, and I have written code to transfer data from sheet No.1 to sheet No.2. What I need is to include a condition that checks if the column G does not contain a certain value. In that case I would like a MsgBox to display "Check..".

The interested range in the Sheet 1 is (A3:J50), so the condition would interest cells G3 to G50.

My current code is:

Sub kk()

Dim lastrow As Integer

lastrow = [b50].End(xlUp).Row
Range("b3:J" & lastrow).Copy Sheets("Daily Rec.").Range("b" & Sheets("Daily Rec.").[b1000].End(xlUp).Row + 1)
Range("b3:j" & lastrow).ClearContents
MsgBox ("Date Posted")
Sheets("Daily Rec.").Activate
MsgBox ("Check..")

End Sub

please advice

The code you provided lacks full qualification. Especially, if you wish to work with multiple sheets (as mentioned in the post) you should write in your code on which sheet you want to work / copy / paste data. So, `Range("b3:J" & lastrow).Copy` should really be something like `Worksheets("Sheet1").Range("b3:J" & lastrow).Copy`. This should be fixed in all of your code. For the filtering I'd suggest that you apply a filter to your table with a criteria that suits you. Record what you are doing and your VBA code is written for you. Is as simple as that. — Ralph, Mar 16 '17 at 11:41
:Let's see if I understand correctly. You want to copy Range("b3:j" & lastrow) from the activesheet and put it on to Sheets("Daily Rec."). If the contents in the "G" column contains a certain value, then you don't want to copy the row and instead send a messave via msgbox. Is that correct? Try this: Use the find method to find the value in column G. If it exists then send your message, if not then do the copy. — John Muggins, Mar 16 '17 at 12:41
Possible duplicate of [Check if value exists in column in VBA](http://stackoverflow.com/questions/12642164/check-if-value-exists-in-column-in-vba) — David Glickman, Mar 16 '17 at 17:15

score 1 · Answer 1 · answered Mar 16 '17 at 17:31

This should help get you started. But like others have mentioned, we need more info to help.

Sub Okay()
Dim source As Range
Dim target As Range
Dim found As Range
Dim cell  As Range

    Set source = ThisWorkbook.Worksheets("Sheet 1").Range("A3:J50")
    Set target = ThisWorkbook.Worksheets("Sheet 2").Range("G3:G50")

    For Each cell In source.Cells
        Set found = target.Find(cell.Value)
        If found Is Nothing Then
            MsgBox "Check.." & vbNewLine _
            & "Cell [" & cell.Address(0, 0) & "] on sheet [" & cell.Parent.Name & "]" _
            & vbNewLine _
            & "was not found within " & vbNewLine _
            & "cell range of [" & target.Address(0, 0) & "] on sheet [" & target.Parent.Name & "]"
        End If
    Next cell
End Sub

How do i use the IF condition depending on the input contained in a column (not in a cell)?

1 Answers1