Bringing up a message box if values already in cells - Help!!!!

[lafinca47]

This may be a tricky one to explain but I'll have a go.

As I understand it you cannot use Lookups in VBA?

I have a excel workbook and on sheet 1 I have some figures, then on
sheet 2 I have a summary sheet for the year, set out like so:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

On sheet 1 I have a Macro called "Transfer figures to Summary" when
pressed a User form appears with a Combox containing all twelve months
of the year depending on which month you click the figures then appear
on sheet 2 under the correct month heading on the summary.

What I now want to put in is a msgbox containing the Message "This
Month Already contains values do you want to Overwrite?" if the user
selects OK then the Macro continues and if the user hits cancel then it
exits the sub, even this I have managed to do

But here is where I'm stuck because I don't think you can use look ups
in VBA and if you can I'm not to sure how too. I have put the VBA code
in 12 times pointing to each cell ref on the summary sheet so when you
run the macro each time the msgbox comes up all the time not just for
the particular month the combobox has selected.

Example:

(D8 = Jan, I have then copied this another 11 times for each month of
the year and changing the ref i.e. E8 = Feb, F8=Mar, G8=Apr etc etc)

Worksheets("Sheet2").Select

If Range("D8").Value = ComboBox1 And Range("D9").Value = "" Then
Worksheets("Sheet1").Select
Range("E20:E31").Select
Selection.Copy
Worksheets("Sheet2").Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ElseIf Msgbox("Values already exist for this month do you want to
Overwrite?", vbOKCancel) = vbCancel Then
Exit Sub
Else
Worksheets("Sheet1").Select
Range("E20:E31").Select
Selection.Copy
Worksheets("Sheet2").Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End If

End If

I'm sure this is a very long way round but I'm new to VBA and really
have hit a brick wall any help would be much appreciated

 

[Tom Ogilvy]

If Range("D8").Value = ComboBox1 And Range("D9").Value = "" Then
Worksheets("Sheet1").Select
Range("E20:E31").Select
Selection.Copy
Worksheets("Sheet2").Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ElseIf Range("D8").Value = ComboBox1 then
If Msgbox("Values already exist for this month do you want to
Overwrite?", vbOKCancel) = vbCancel Then
Exit Sub
Else
Worksheets("Sheet1").Select
Range("E20:E31").Select
Selection.Copy
Worksheets("Sheet2").Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End If
End If

 

[lafinca47]

Thanks Tom,

Worked superbly, absolutely brilliant.

Regards