VBA If Range Contains Cell Value

[reidyg]

Hi Folks,

I've used this forum for over 3 years to learn everything I can about excel. But I need some help now, as I'm very new to VBA and I need to use it for a macro button. Basically, I want to:

look up a range of cells (F44:F100000(or last blank cell in "F")) to see if it contains a specific cell value (C16)

If it does - I want to copy and paste a selection (F3:P43) to next blank row (I've figured this out by recording a macro)

If it doesn't - don't do anything.

I've googled and googled, but anything I've tried has failed so far. Any help would be much appreciated. Please remember I'm very new to VBA, so it might take me a while to "get it".

This is what I came up with that's failed:

Sub CopyPaste()

If InStr(1, Range("F44:F1000").Value, Range("C16").Value, 1) Then
MsgBox "Invoice number already issued."
Else

Range("F3:P43").Select
Selection.Copy
Range("F3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C18").Select

End If
End Sub

 

[Michael M]

Hi and Welcome to the Board.
What specific value are you looking for in "C16"
AND
You want to copy range "F3" to "F last cell" to the first blank cell in "F" ?

 

[reidyg]

Hi Michael,

Thanks for the welcome. Basically I have created an invoice sheet (F3:P43) that uses lookups from standard formulas I use based on client, dates, etc, etc to calculate an invoice from other worksheets. What I was doing to keep track of invoices I've issued, is to copy and paste the issued invoice values below F43. I've ensured that the F column only contains the invoice numbers. So everything below row 43 contains copies of invoices.

I created a macro button to copy and paste values of F3:P43, starting from F44, to the next blank cell in column F. The invoices are always 40 rows long, and each cell in F contains the invoice number it corresponds to.

The reason I want to use VBA is to make sure that I don't copy and paste duplicates unnecessarily.

 

[Michael M]

Ok, now I'm really confused......so what does "cell "C16" have to do with the copy and paste ?

 

[reidyg]

Sorry Michael,

I'm a bit tired.

"C16" is the cell/value I want to check/search against "F44:F1000"

IF - the value of "C16" is found in "F44:F100"

THEN - don't copy and paste

ELSE - copy and paste

END IF

I've figured out my copy and paste code. What I'm stuck on, is trying to create a code that will search "F44:F1000" for a value in "C16".

 

[Michael M]

Maybe this

Sub CopyPaste()
Dim lr As Long
lr = Cells(Rows.Count, "F").End(xlUp).Row
    For Each cell In Range("F3:F43")
        If cell.Value = Range("C16").Value Then
           MsgBox "Invoice number already issued."
           Exit Sub
        End If
    Next cell
        Range("F3:P43").Copy
        Range("F" & lr + 1).PasteSpecial Paste:=xlPasteValues
    End Sub

 

[reidyg]

Thanks Michael,

That worked great, the only thing I had to change was Line 4, "F3:F43" to "F44:F1000".

But is there away to replace "F44:F1000" I entered to some thing like "F44: ("F" & lr + 1)"?

 

[Michael M]

MAybe

Sub CopyPaste()
Dim lr As Long
lr = Cells(Rows.Count, "F").End(xlUp).Row
    For Each cell In Range("F44:F" & lr)
        If cell.Value = Range("C16").Value Then
           MsgBox "Invoice number already issued."
           Exit Sub
        End If
    Next cell
        'Range("F3:F43").Copy ' so should this line change too !!
        Range("F44:F" & lr).copy ' to this ?
        Range("F" & lr + 1).PasteSpecial Paste:=xlPasteValues
    End Sub

 

[reidyg]

Perfect Michael, thanks a lot. It's way too late on this side of the world, but I apprecciate all the help. Time for some sleep.

 

[Michael M]

OK, glad it worked. Thanks for the feedback