Copy to next empty row, if not a match

[Steve]

I have the following code to copy data from one sheet to another:

Sheets("CallerInput").Range("B29:BQ29").Copy _
Sheets("InfoLoader").Range("B" & LastRow(Sheets("InfoLoader")) + 1)

However, cell B29 in the range to be copied is a date. How can I amend
this code to first look down column B of the paste target in
"InfoLoader". It would then overtype any day already held, or otherwise
paste to the end?

Thanks in advance

Steve

 

[Dave Peterson]

You can use application.match() to see if there's a date that matches:

Option Explicit
Sub testme()

Dim infoWks As Worksheet
Dim callWks As Worksheet
Dim res As Variant
Dim myRng As Range
Dim DestCell As Range

Set infoWks = Worksheets("infoloader")
Set callWks = Worksheets("callerinput")

Set myRng = infoWks.Range("b:b")

'sometimes converting to a long will make the match work better with dates.
res = Application.Match(CLng(callWks.Range("b29").Value), myRng, 0)

If IsError(res) Then
Set DestCell = infoWks.Range("B" & LastRow(infoWks) + 1)
Else
Set DestCell = myRng(res)
End If

callWks.Range("b29:bq29").Copy _
Destination:=DestCell

End Sub

'my easy version of LastRow--for testing only
'use your own.
Function LastRow(wks As Worksheet) As Long
With wks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
End Function

 

[Steve]

Dave, thanks a lot for this; I really appreciate it.

However, the range to be copied all contain references to cells
elsewhere on that sheet, eg: (B29) =A4, (C29) = B4 and so on. Is there
a way that this can copy just the *values* in the cells, not the
*formula*?

Thanks in advance

Steve

 

[Dave Peterson]

If you record a macro when you do a copy|paste special|values you'll see the
code.

But another way is to just assign the values:

Option Explicit
Sub testme()

Dim infoWks As Worksheet
Dim callWks As Worksheet
Dim myRng As Range
Dim res As Variant
Dim myFromRng As Range
Dim DestCell As Range

Set infoWks = Worksheets("infoloader")
Set callWks = Worksheets("callerinput")

Set myRng = infoWks.Range("b:b")

'sometimes converting to a long will make the match work better with dates.
res = Application.Match(CLng(callWks.Range("b29").Value), myRng, 0)

If IsError(res) Then
Set DestCell = infoWks.Range("B" & LastRow(infoWks) + 1)
Else
Set DestCell = myRng(res)
End If

Set myFromRng = callWks.Range("b29:bq29")

DestCell.Resize(myFromRng.Rows.Count, myFromRng.Columns.Count).Value _
= myFromRng.Value

End Sub

 

[Steve]

That's done it. Thanks very much.

Steve