| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Followup question for Tom
I only used the colon when I was refering to a multicell range. You don't
need it he 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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Followup question for Tom
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 he 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deleting a row - followup question | Excel Discussion (Misc queries) | |||
| Followup on Format a Ratio | Excel Discussion (Misc queries) | |||
| Followup: Folder listing | Excel Discussion (Misc queries) | |||
| countif followup | Excel Worksheet Functions | |||
| Bob Phillips followup question on text macro | Excel Discussion (Misc queries) | |||
Linear Mode
