Followup question for Tom

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

Should this not be

Please Login or Register  to view this content.

this

Please Login or Register  to view this content.

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

--
Regards,
Tom Ogilvy

"Marty" <[email protected]> wrote in message
news:[email protected]...
> 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

That did it. I need some sleep.

Thanks again.

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "Marty" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>