Saving over a range

[TimN]

I have the following code which makes a copy of a portion of my spreadsheet
at the "OK" command event and saves to the next available cell in the Data
worksheet.

Private Sub cmdOK_Click()
Dim rCell As Range
With Application.ThisWorkbook
Set rCell = .Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Worksheets("STD Calc").Range("B14:N33").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Unload Me
End Sub

Prior to the OK button being clicked, the user has entered via a user form,
a beginning date for a two week payroll period. That date range and
coresponding data is then copied with the code above.
What I need to add in is a way to check for an occurance where the user
enters a date that already exists on the Data worksheet and the new copy is
saved right over the top of the old data that coresponds to that same date
range. This will happen if an error is made for a payroll period and the
user needs to correct it by going back to that payroll period. In other
words, I don't want to save the corrections to the next empty cell, I want it
to copy over the original data which was entered incorrectly and is now being
corrected.

How can I do that?

Thanks for any ideas.

 

[Jim Thomlinson]

Give something like this a try...

Private Sub cmdOK_Click()
Dim rCell As Range
DimrFound as range

With Application.ThisWorkbook
Set rfound = .Worksheets("Data").Cells.Find(What:="TheDate", _

LookAt:=xlWhole, _

LookIn:=xlFormulas)
if rfound is nothing then
Set rCell = .Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
else
setrCell = rFound
end if
Worksheets("STD Calc").Range("B14:N33").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Unload Me
End Sub

 

[TimN]

Jim,

Would you help me understand a few things ( I am in way over my head with my
current understanding of VBA).
Tha following piece of your code:
(What:="TheDate", _

LookAt:=xlWhole, _

LookIn:=xlFormulas)

Are you saying something like; Look for August 7, 2006, if you find it copy
over the whole data. If not continue the copy at the next available cell?

I'm just not sure of the correct syntax to use.

 

[Jim Thomlinson]

The code is trying got find the date somewhere on the sheet. This should
probably be narrowed down to a single column (probably D but I was not too
sure). If it finds the date then make rCell into the Date that was found. If
rfound is nothing then the date was not found so just use your original
code...

 

[TimN]

Jim,

OK, I'm almost there. I have plugged it in and it runs without error, only
it still copies to the next available cell. Here is what I have:

Dim rCell As Range
Dim rFound As Range
With Application.ThisWorkbook
Set rFound = .Worksheets("Data").Cells.Find(What:=("B1:B65536"),
LookAt:=xlWhole, LookIn:=xlFormulas)
If rFound Is Nothing Then
Set rCell =
..Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0)
Else
Set rCell = rFound
End If
Worksheets("STD Calc").Range("B14:N34").Copy
rCell.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Unload Me
End Sub

The date is in col "B" on the Data worksheet. I'm sure my "B1:B65536" is
not right. The date the user enters will be on a worksheet titled "STD Calc"
in cell C6. I need to have code to say "look at STD Calc cell C6, if that
date exists anywhere in Data, cells B1:B65536, then replace at that
location". I think that is what you are telling me, I just don't know how to
get it to work.

 

[Jim Thomlinson]

So you are looking in column B for the date... try this...

Set rFound = .Worksheets("Data").Columns("B").Find(What:=(???),
LookAt:=xlWhole, LookIn:=xlFormulas)


Replace ??? with the variable date value that is supplied by the user...

 

[TimN]

AWESOME!!!

Last question.......
When it finds a duplicate it is copying the new data in the wrong place....
it goes in 1 column too far to the right and 3 rows down i.e if in this case
it should have been at cell A1, it copied instead to B4. Is that an offset
problem?

Thanks so much for your help!!!!!!

 

[Jim Thomlinson]

It is finding a cell in column B with the date you have specified. If the
paste needs to be offset from that then by all means...

Set rFound = .Worksheets("Data").Columns("B").Find(What:=(???),
LookAt:=xlWhole, LookIn:=xlFormulas).offset(-3, -1)

Sorry for the lateness of my replies. I have been in meeting after meeting
today...

 

[TimN]

Jim,

So sorry to keep asking questions.

I have:
Set rFound = .Worksheets("Data").Columns("B").Find(What:=(.Worksheets("STD
Calc") .Range("C6")), LookAt:=xlWhole, LookIn:=xlFormulas).Offset(-3, -1)

with this I get a run-time error"91" Object variable or with Block variable
not set.

Any thoughts?