Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
Hi,
I'm using Excel 2000 running on Windows XP professional. Private Sub cmdComplete_Click() Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub DoTheExport is Chip's code which works great if I manually make the selection. The range I want to select start on row 19 and can be of variable length. Is there a way to select a variable range? Thanks, Dan Dungan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
Post ONLY in the group where you want an answer. Otherwise, you waste a lot
of responders time. -- Don Guillett Microsoft MVP Excel SalesAid Software "dan dungan" wrote in message ps.com... Hi, I'm using Excel 2000 running on Windows XP professional. Private Sub cmdComplete_Click() Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub DoTheExport is Chip's code which works great if I manually make the selection. The range I want to select start on row 19 and can be of variable length. Is there a way to select a variable range? Thanks, Dan Dungan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
The range you chose is a string. Any stement that works on a string will
also work inside a range. Here are a lot of choices that will replace "Range("A19:I19")" 1) set MyRange = Range("A19:A119") MyRange.End(XLUP).select 2) My1stRow = 2 LastRow = 10 My1stCol = "A" Mylastcol = "E" Range(My1stcol & My1stRow & ":" & MylastCol & LastRow) 3) LastRow = 10 Range("A19:A" & Lastrow) 4) Range uses letters for columns. when you have numbers use cells Range("A19",cells(lastrow,"A")) 5) Range(cells(19,1),cells(19,9)) Hope this give you some ideas. "Don Guillett" wrote: Post ONLY in the group where you want an answer. Otherwise, you waste a lot of responders time. -- Don Guillett Microsoft MVP Excel SalesAid Software "dan dungan" wrote in message ps.com... Hi, I'm using Excel 2000 running on Windows XP professional. Private Sub cmdComplete_Click() Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub DoTheExport is Chip's code which works great if I manually make the selection. The range I want to select start on row 19 and can be of variable length. Is there a way to select a variable range? Thanks, Dan Dungan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
Thanks for your response, Joel.
I'm not clear how to implement your examples. I'm looking for a way to describe a variable range. These examples seem to show the range limits defined. Allow me to describe some context: Customer service agents use this spreadsheet to calculate a quote for an electrical part number. If the customer has more than one part number listed in the quote request, our agent adds the price for each part quoted. The quotes are appended to this sheet, CompleteQuote. I don't want to append the first 18 rows because they contain header information and formulas. But I don't know what the last row will be until the service agent clicks the Completed button. So I have to questions: 1. How can I determine which cells have data and define the range I want to select? 2. When I select the range manually, the range color changes to a blue tint. When I'm debugging, I was looking for this blue tint to determine the correct range was selected. Is there a better way to verify what range is selected? Thanks, Dan On Sep 26, 6:59 pm, Joel wrote: The range you chose is a string. Any stement that works on a string will also work inside a range. Here are a lot of choices that will replace "Range("A19:I19")" 1) set MyRange = Range("A19:A119") MyRange.End(XLUP).select 2) My1stRow = 2 LastRow = 10 My1stCol = "A" Mylastcol = "E" Range(My1stcol & My1stRow & ":" & MylastCol & LastRow) 3) LastRow = 10 Range("A19:A" & Lastrow) 4) Range uses letters for columns. when you have numbers use cells Range("A19",cells(lastrow,"A")) 5) Range(cells(19,1),cells(19,9)) Hope this give you some ideas. "Don Guillett" wrote: Post ONLY in the group where you want an answer. Otherwise, you waste a lot of responders time. -- Don Guillett Microsoft MVP Excel SalesAid Software "dan dungan" wrote in message ups.com... Hi, I'm using Excel 2000 running on Windows XP professional. Private Sub cmdComplete_Click() Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub DoTheExport is Chip's code which works great if I manually make the selection. The range I want to select start on row 19 and can be of variable length. Is there a way to select a variable range? Thanks, Dan Dungan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
1. How can I determine which cells have data and define the range I
want to select? It depends if all the cells contain data (no blank row or columns). To get the last row the best way is to go to the last row of the spreadsheet and search up until you find the first cell with data. This makes sure you include all the blank rows or cells. Use a column that has data in every row. I chose column A rows.count is a constant in excel which is the last row = 65536 for excel 2003. Xlup says to searcxh up until a non-blank cell is found LastRow = cells(Rows.Count,"A").end(xlup).Row Likewise for columns (last column is 256) LastCol = cells(1,Columns.Count).end(xltoleft).Column To set the entire range of cells set MyRange = range("A19",cells(LastRow,LastCol)) -------------------------------------------------------------------------------------------- 2. When I select the range manually, the range color changes to a blue tint. When I'm debugging, I was looking for this blue tint to determine the correct range was selected. Is there a better way to verify what range is selected? You can do the same thing from VBA. Using example above MyRange.Select You can step through you code in VBA by pressing F8 or settting break points using F9. The go back to the spreadsheet a verify the corrrect cells are selected. Be careful when switch worksheets or workbooks when debugging code. The VBA doesn't know you made changes. You should return back to same workbooks and pages when you stopped before continueing. ----------------------------------------------------------------------------------------- "dan dungan" wrote: Thanks for your response, Joel. I'm not clear how to implement your examples. I'm looking for a way to describe a variable range. These examples seem to show the range limits defined. Allow me to describe some context: Customer service agents use this spreadsheet to calculate a quote for an electrical part number. If the customer has more than one part number listed in the quote request, our agent adds the price for each part quoted. The quotes are appended to this sheet, CompleteQuote. I don't want to append the first 18 rows because they contain header information and formulas. But I don't know what the last row will be until the service agent clicks the Completed button. So I have to questions: 1. How can I determine which cells have data and define the range I want to select? 2. When I select the range manually, the range color changes to a blue tint. When I'm debugging, I was looking for this blue tint to determine the correct range was selected. Is there a better way to verify what range is selected? Thanks, Dan On Sep 26, 6:59 pm, Joel wrote: The range you chose is a string. Any stement that works on a string will also work inside a range. Here are a lot of choices that will replace "Range("A19:I19")" 1) set MyRange = Range("A19:A119") MyRange.End(XLUP).select 2) My1stRow = 2 LastRow = 10 My1stCol = "A" Mylastcol = "E" Range(My1stcol & My1stRow & ":" & MylastCol & LastRow) 3) LastRow = 10 Range("A19:A" & Lastrow) 4) Range uses letters for columns. when you have numbers use cells Range("A19",cells(lastrow,"A")) 5) Range(cells(19,1),cells(19,9)) Hope this give you some ideas. "Don Guillett" wrote: Post ONLY in the group where you want an answer. Otherwise, you waste a lot of responders time. -- Don Guillett Microsoft MVP Excel SalesAid Software "dan dungan" wrote in message ups.com... Hi, I'm using Excel 2000 running on Windows XP professional. Private Sub cmdComplete_Click() Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub DoTheExport is Chip's code which works great if I manually make the selection. The range I want to select start on row 19 and can be of variable length. Is there a way to select a variable range? Thanks, Dan Dungan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
Hi Joel,
I've been debugging using F8. To get the yellow highlight to move through the code, I had to dim MyRange, LastRow and LastCol. I'm not sure how they should be dimensioned. . .So I chose as Range for MyRange, as Long for LastRow and LastCol. Anyway these variables show the last row is 87. I deleted blank rows below my data and closed the workbook--it still shows last row as 87. When I look at the spreadsheet it looks like the last row is 68. I suspect MyRange, LastRow and Last Column are calculating on a sheet other than my intended sheet. I don't know how to determine where the procedure is getting the values. MyRange returns an error--"Run-time error '1004': Application-defined or object-defined error" I suspect this is because I am running a private procedure from a button on the sheet, QuotedPart, that is manipulating a different sheet, CompletedQuote. Do you have any further feedback? Thanks, Dan Private Sub cmdComplete_Click() Dim MyRange As Range Dim LastRow As Long Dim LastCol As Long Sheets("CompleteQuote").Activate Sheets.Item(A1).Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row Set MyRange = Range("A19", Cells(LastRow, LastCol)) 'LastRow.Select 'MyRange.Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub On Sep 27, 11:57 am, Joel wrote: 1. How can I determine which cells have data and define the range I want to select? It depends if all the cells contain data (no blank row or columns). To get the last row the best way is to go to the last row of the spreadsheet and search up until you find the first cell with data. This makes sure you include all the blank rows or cells. Use a column that has data in every row. I chose column A rows.count is a constant in excel which is the last row = 65536 for excel 2003. Xlup says to searcxh up until a non-blank cell is found LastRow = cells(Rows.Count,"A").end(xlup).Row Likewise for columns (last column is 256) LastCol = cells(1,Columns.Count).end(xltoleft).Column To set the entire range of cells set MyRange = range("A19",cells(LastRow,LastCol)) -------------------------------------------------------------------------------------------- 2. When I select the range manually, the range color changes to a blue tint. When I'm debugging, I was looking for this blue tint to determine the correct range was selected. Is there a better way to verify what range is selected? You can do the same thing from VBA. Using example above MyRange.Select You can step through you code in VBA by pressing F8 or settting break points using F9. The go back to the spreadsheet a verify the corrrect cells are selected. Be careful when switch worksheets or workbooks when debugging code. The VBA doesn't know you made changes. You should return back to same workbooks and pages when you stopped before continueing. ----------------------------------------------------------------------------------------- "dan dungan" wrote: Thanks for your response, Joel. I'm not clear how to implement your examples. I'm looking for a way to describe a variable range. These examples seem to show the range limits defined. Allow me to describe some context: Customer service agents use this spreadsheet to calculate a quote for an electrical part number. If the customer has more than one part number listed in the quote request, our agent adds the price for each part quoted. The quotes are appended to this sheet, CompleteQuote. I don't want to append the first 18 rows because they contain header information and formulas. But I don't know what the last row will be until the service agent clicks the Completed button. So I have to questions: 1. How can I determine which cells have data and define the range I want to select? 2. When I select the range manually, the range color changes to a blue tint. When I'm debugging, I was looking for this blue tint to determine the correct range was selected. Is there a better way to verify what range is selected? Thanks, Dan On Sep 26, 6:59 pm, Joel wrote: The range you chose is a string. Any stement that works on a string will also work inside a range. Here are a lot of choices that will replace "Range("A19:I19")" 1) set MyRange = Range("A19:A119") MyRange.End(XLUP).select 2) My1stRow = 2 LastRow = 10 My1stCol = "A" Mylastcol = "E" Range(My1stcol & My1stRow & ":" & MylastCol & LastRow) 3) LastRow = 10 Range("A19:A" & Lastrow) 4) Range uses letters for columns. when you have numbers use cells Range("A19",cells(lastrow,"A")) 5) Range(cells(19,1),cells(19,9)) Hope this give you some ideas. "Don Guillett" wrote: Post ONLY in the group where you want an answer. Otherwise, you waste a lot of responders time. -- Don Guillett Microsoft MVP Excel SalesAid Software "dan dungan" wrote in message ups.com... Hi, I'm using Excel 2000 running on Windows XP professional. Private Sub cmdComplete_Click() Worksheets("CompleteQuote").Range("A19:I19").End(x lDown).Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub DoTheExport is Chip's code which works great if I manually make the selection. The range I want to select start on row 19 and can be of variable length. Is there a way to select a variable range? Thanks, Dan Dungan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
I started using the with statement to make sure I'm on the correct sheet and
avoid using the select method unless absolutely necessary. Some methods only work with select. The set MyRange statement would fail if LastRow or LastCol were not valid. Private Sub cmdComplete_Click() Dim MyRange As Range Dim LastRow As Long Dim LastCol As Long with Sheets("CompleteQuote") 'There are two last rows LastRow = .Cells(Rows.Count, "A").End(xlUp).Row 'LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column 'Not sure what this is doing? 'Sheets.Item(A1).Select Set MyRange = .Range("A19", .Cells(LastRow, LastCol)) 'LastRow.Select 'MyRange.Select DoTheExport Clear_Unlocked2 .Range("A19:G65536").ClearContents txtCount = 0 end with Sheets("QuotedPart").Activate End Sub Hi Joel, I've been debugging using F8. To get the yellow highlight to move through the code, I had to dim MyRange, LastRow and LastCol. I'm not sure how they should be dimensioned. . .So I chose as Range for MyRange, as Long for LastRow and LastCol. Anyway these variables show the last row is 87. I deleted blank rows below my data and closed the workbook--it still shows last row as 87. When I look at the spreadsheet it looks like the last row is 68. I suspect MyRange, LastRow and Last Column are calculating on a sheet other than my intended sheet. I don't know how to determine where the procedure is getting the values. MyRange returns an error--"Run-time error '1004': Application-defined or object-defined error" I suspect this is because I am running a private procedure from a button on the sheet, QuotedPart, that is manipulating a different sheet, CompletedQuote. Do you have any further feedback? Thanks, Dan Private Sub cmdComplete_Click() Dim MyRange As Range Dim LastRow As Long Dim LastCol As Long Sheets("CompleteQuote").Activate Sheets.Item(A1).Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row Set MyRange = Range("A19", Cells(LastRow, LastCol)) 'LastRow.Select 'MyRange.Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble exporting using Chip Pearson's export
Thank you Joel.
'There are two last rows LastRow = .Cells(Rows.Count, "A").End(xlUp).Row 'LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column 'Not sure what this is doing? 'Sheets.Item(A1).Select I was trying to determine what sheet the procedure was operating on and I neglected to remove the test. Since you added the "With" clause, the procedure is working. Thanks for your attention and help. Dan On Sep 27, 5:49 pm, Joel wrote: I started using the with statement to make sure I'm on the correct sheet and avoid using the select method unless absolutely necessary. Some methods only work with select. The set MyRange statement would fail if LastRow or LastCol were not valid. Private Sub cmdComplete_Click() Dim MyRange As Range Dim LastRow As Long Dim LastCol As Long with Sheets("CompleteQuote") 'There are two last rows LastRow = .Cells(Rows.Count, "A").End(xlUp).Row 'LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column 'Not sure what this is doing? 'Sheets.Item(A1).Select Set MyRange = .Range("A19", .Cells(LastRow, LastCol)) 'LastRow.Select 'MyRange.Select DoTheExport Clear_Unlocked2 .Range("A19:G65536").ClearContents txtCount = 0 end with Sheets("QuotedPart").Activate End Sub Hi Joel, I've been debugging using F8. To get the yellow highlight to move through the code, I had to dim MyRange, LastRow and LastCol. I'm not sure how they should be dimensioned. . .So I chose as Range for MyRange, as Long for LastRow and LastCol. Anyway these variables show the last row is 87. I deleted blank rows below my data and closed the workbook--it still shows last row as 87. When I look at the spreadsheet it looks like the last row is 68. I suspect MyRange, LastRow and Last Column are calculating on a sheet other than my intended sheet. I don't know how to determine where the procedure is getting the values. MyRange returns an error--"Run-time error '1004': Application-defined or object-defined error" I suspect this is because I am running a private procedure from a button on the sheet, QuotedPart, that is manipulating a different sheet, CompletedQuote. Do you have any further feedback? Thanks, Dan Private Sub cmdComplete_Click() Dim MyRange As Range Dim LastRow As Long Dim LastCol As Long Sheets("CompleteQuote").Activate Sheets.Item(A1).Select LastRow = Cells(Rows.Count, "A").End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row Set MyRange = Range("A19", Cells(LastRow, LastCol)) 'LastRow.Select 'MyRange.Select DoTheExport Clear_Unlocked2 Worksheets("CompleteQuote").Range("A19:G65536").Cl earContents txtCount = 0 Sheets("QuotedPart").Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chip Pearson's NewWorkDays formula | Excel Discussion (Misc queries) | |||
DeleteDuplicatesViaFilter From Chip Pearson's Website | Excel Programming | |||
How to Use Chip Pearson's Text Import Code | Excel Programming | |||
Chip Pearson's Forum Etiquette | New Users to Excel | |||
Help with Chip Pearson's Code for Deleting Blank Rows | Excel Programming |