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

 

[heniek00]

Hi Michael, you resolved that smoothly. I have a similar problem:

I have a list of Action Type B8:B (list lenght is different in different sheets).
I have also activity times given range E8:GV (again depends on the lenght of Action Type in column B).

There is usually none or one value per row but sometimes it can be two.

What I need to do is to check cell by cell in that range and

IF value exists
THEN
1. copy it to another file ("ConsFile") column A
2. copy Action Type from column B accordingly to ("ConsFile") column B
3. copy Customer ID from Row 7 above the value to ("ConsFile") column C.
4. Repeat that for every value found in range building a list of all activities with Types and Customer ID's.

Looks like there is another problem with setting reference to a cell when value was found.

Hope you can help with that.