Solved stop date being entered if it already exists in a column

June 16, 2013 at 12:04:20
Specs: Windows 7
Hi,
I have the following problem

I enter a date into cell "K6"
I then have a command button with an assigned macro to paste the date to two locations on the same worksheet, "D5:D6" (merged cells) and to the next blank cell in Column "R"

What I require is to ensure that the date entered cannot paste to the desired locations if the date already exists in Column "R"

I attach the macro as follows

Sub datepaste()
'
' datepaste Macro


   If MsgBox("Is the Competition Date Correct?", vbYesNo Or vbInformation, "Date") = vbYes Then
' date is entered in to this cell
    Range("K6").Select
    Selection.Copy
' date is pasted into this range of merged cells
    Range("D5:E5").Select
    ActiveSheet.Paste
' date is pasted into the next blank cell in column R
 nxtRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row + 1
  Sheets("SCARD").Range("K6").Copy _
    Destination:=Sheets("SCARD").Range("R" & nxtRow)
    
    Else
 MsgBox "Please re-enter the Correct Date", vbCritical Or vbOKOnly, "Date"
 End If
End Sub

Any ideas as I think the check between the Date entered in "K6" and what exists in Column"R" must take place before the first line of the above macro.

Any assistance or ideas of how to overcome this would be greatly appreciated.


See More: stop date being entered if it already exists in a column

Report •

#1
June 16, 2013 at 16:34:11
✔ Best Answer
First, there is no need to Select the ranges you want to perform the Copy and
Paste operations on.

You don't do it for the Copy/Paste for Column R, why do it for D5:E5? I have
modified that section of your code.

The following code will check Column R for the Date in K6 before presenting the
confirmation message box. You will note that I moved the instruction to determine
the last Row in Column R to the top of code so that it can be used to set the
Range that .Find will use.

Sub datepaste()
'
' datepaste Macro
 nxtRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row + 1
   With Sheets("SCARD").Range("R1" & ":R" & nxtRow)
     Set d = .Find(Range("K6"))
      If d Is Nothing Then
        If MsgBox("Is the Competition Date Correct?", vbYesNo Or vbInformation, "Date") = vbYes Then
' Date is Copied and pasted into this range of merged cells
           Range("K6").Copy _
             Destination:=Range("D5:E5")
' date is pasted into the next blank cell in column R
           Sheets("SCARD").Range("K6").Copy _
             Destination:=Sheets("SCARD").Range("R" & nxtRow)
        Else
           MsgBox "Please re-enter the Correct Date", vbCritical Or vbOKOnly, "Date"
        End If
      Else
        MsgBox "Date Already Exists. Please Try Again", vbCritical Or vbOKOnly, "Date"
      End If
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 17, 2013 at 10:27:38
DerbyDad03
What can I say, you are a star. Works perfectly

Please forgive me but most of the VBA macros that I produce are through the recording option that is available. I can see from what you produce and the comments you have made that there are simpler ways to write the code.

Where can I go to on the WWW to try and understand how to overcome my short comings and learn how to write this stuff as you do


Report •

#3
June 17, 2013 at 11:04:42
re: "Please forgive me but most of the VBA macros that I produce are through the recording option that is available."

I've never seen the recorder produce this type of code:

nxtRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row + 1
  Sheets("SCARD").Range("K6").Copy _
    Destination:=Sheets("SCARD").Range("R" & nxtRow)

I don't have any specific sites to recommend. I learned to write code by hanging out in forums, asking questions and reading questions and answers from other posters. Unfortunately, as much as I like this site, there aren't too many people offering macros, so you won't get much variety in techniques.

I would subscribe to other Excel related forums and drop in as often as time allows to review what's posted.

In the meantime, read this How-To for some tips on debugging VBA code, both macros that you have written and ones that you find on the web. I learned a lot about writing macros by "debugging" code that I found on the web. I wrote this tutorial as means to share some simple debugging tips.

http://www.computing.net/howtos/sho...

Above all, keep in mind that the recording feature typically results in very bloated code that is hard to follow. I still record some things, but I always clean it up afterwards.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question