Followup question for Tom

[Guest]

Tom:

Sorry to bug you again, but I'm still encountering an error in my code.

For background, both SOURCE and RECIPIENT have been set correctly.
Mousing-over the variables during debbuging tells me that:
SOURCEKEY = "A"
SOURCEROW = 1
RECIPIENTKEY = "A"
RECIPIENTROW = 1

which is what they should be. The code barfs on this statement:

(This is why I asked the earlier question about .Range syntax)

If SOURCE.Range(SOURCEKEY & ":" & SOURCEROW) = RECIPIENT.Range(RECIPIENTKEY
& ":" & RECIPIENTROW) Then

It gives me an Application-defined or object-defined error.

Any ideas why?

Complete code listed below my display name in case you want to see it.

Thanks,
MARTY

Private Sub CommandButton1_Click()
'Copy cells from SOURCE to RECIPIENT based upon parameters entered into COPIER

Dim COPIER, SOURCE, RECIPIENT As Object
Set COPIER = ActiveSheet
Set SOURCE = Workbooks("SOURCE.xls").Sheets("Sheet1")
Set RECIPIENT = Workbooks("RECIPIENT.xls").Sheets("Sheet1")

SOURCEKEY = COPIER.Range("D13") 'this is a letter
SOURCECOLUMN = COPIER.Range("D15") 'this is a letter
SOURCEFIRSTROW = COPIER.Range("D17") 'this is a positive integer
SOURCELASTROW = COPIER.Range("D19")'this is a positive integer
RECIPIENTKEY = COPIER.Range("K13") 'this is a letter
RECIPIENTCOLUMN = COPIER.Range("K15") 'this is a letter
RECIPIENTFIRSTROW = COPIER.Range("K17") 'this is a positive integer
RECIPIENTLASTROW = COPIER.Range("K19") 'this is a positive integer
COPYTYPE = COPIER.Range("D21") 'this is a string
For RECIPIENTROW = RECIPIENTFIRSTROW To RECIPIENTLASTROW
For SOURCEROW = SOURCEFIRSTROW To SOURCELASTROW
If SOURCE.Range(SOURCEKEY & ":" & SOURCEROW) =
RECIPIENT.Range(RECIPIENTKEY & ":" & RECIPIENTROW) Then
If COPYTYPE = "Cell Text Only" Then
RECIPIENT.Range(RECIPIENTCOLUMN & ":" & RECIPIENTROW) =
SOURCE.Range(SOURCECOLUMN & ":" & SOURCEROW)
End If
If COPYTYPE = "Cell Text and Formatting (exact copy)" Then
SOURCE.Range(SOURCECOLUMN & ":" & SOURCEROW).Copy
Destination:=RECIPIENT.Cells(RECIPIENTCOLUMN & ":" & RECIPIENTROW)
End If
End If
Next SOURCEROW
Next RECIPIENTROW
End Sub

 

[Tom Ogilvy]

I only used the colon when I was refering to a multicell range. You don't
need it here:

paste this in your module and test it:

Sub Marty()
Dim SOURCE As Worksheet
Dim RECIPIENT As Worksheet
Set SOURCE = Worksheets(1)
Set RECIPIENT = Worksheets(2)
SOURCEKEY = "A"
SOURCEROW = 1
RECIPIENTKEY = "A"
RECIPIENTROW = 1
MsgBox SOURCE.Range(SOURCEKEY & _
SOURCEROW).Address(external:=True)
MsgBox RECIPIENT.Range(RECIPIENTKEY & _
RECIPIENTROW).Address(external:=True)
End Sub

 

[Guest]

That did it. I need some sleep.

Thanks again.

I only used the colon when I was refering to a multicell range. You don't
need it here:

paste this in your module and test it:

Sub Marty()
Dim SOURCE As Worksheet
Dim RECIPIENT As Worksheet
Set SOURCE = Worksheets(1)
Set RECIPIENT = Worksheets(2)
SOURCEKEY = "A"
SOURCEROW = 1
RECIPIENTKEY = "A"
RECIPIENTROW = 1
MsgBox SOURCE.Range(SOURCEKEY & _
SOURCEROW).Address(external:=True)
MsgBox RECIPIENT.Range(RECIPIENTKEY & _
RECIPIENTROW).Address(external:=True)
End Sub