Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
weird bug - Excel 2007
I was just running a small macro that processes a datasheet. This is
the code, in the worksheet's module: With wsh For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count Debug.Print iRow 'only process non-zero amounts If (Me.Cells(iRow, iSourceAnnualAmountCol).Value < 0) Then ' process each month's amount ' assumes first month is in column following annual amount For iCol = iSourceAnnualAmountCol + 1 To iSourceAnnualAmountCol + 12 .Cells(iRowDest, iDestLocationCol).Value = Me.Cells(iRow, iSourceLocationCol).Value .Cells(iRowDest, iDestDateCol).Value = Me.Cells(iSourceDateRow, iCol).Value .Cells(iRowDest, iDestAmountCol).Value = Round(Me.Cells(iRow, iCol).Value, 2) * 100 .Cells(iRowDest, iDestDescriptionCol).Value = _ Me.Cells(iRow, iSourceFirstName) & " " & Me.Cells(iRow, iSourceLastName) iRowDest = iRowDest + 1 Next iCol End If Next iRow End With I noticed later that I'm missing some of the data, so I did some debugging - and turned out that UsedRange.Rows.Count was returning one row less than there actually were in the data sheet! Weird. Turned out what was causing this was the header row had wrapping turned on, at least in some cells. As soon as I turned it off, the row count became normal again. I'm not sure whether this behavior is known, but it was certainly unexpected. The only reason I caught it in the first place was because the resulting spreadsheet is imported into accounting system, and the debits did not equal the credits so I had to go back and figure out which numbers went missing and why. Anyway, thought I'd share - definitely something to look out for. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
weird bug - Excel 2007
Hopefully someone from Microsoft will see this thread. You might have to
get an account on the Microsoft Discussion Groups web site: http://www.microsoft.com/office/comm...el.programming and report this to them (but I don't know if this will compromise your real e-mail address!). I tested the behavior of UsedRange in Excel 2000, and it definitely does not exhibit this error. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
weird bug - Excel 2007
If Microsoft Online Community Support doesn't see this thread, then you
might go to http://support.microsoft.com/contactus/?WS=communities, scroll down to the "Product Help and Support" section and click on the link to "Send questions about your Microsoft product to a customer service representative." -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
weird bug - Excel 2007
What result do you get if you use one of the following to determine the last row? '-- iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row With ActiveSheet.UsedRange iRow = .Rows(.Rows.Count).Row End With -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "ilia" wrote in message I was just running a small macro that processes a datasheet. This is the code, in the worksheet's module: -snip- For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count I noticed later that I'm missing some of the data, so I did some debugging - and turned out that UsedRange.Rows.Count was returning one row less than there actually were in the data sheet! -snip- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
weird bug - Excel 2007
Well, here's the issue - I can't recreate the problem. Here's what was done:
* a portion of another worksheet, with wrapped headers, was copied into a new workbook * more columns were added to the new workbook with some formulas * the VBA code I posted earlier took the data out of the columns and formatted them suitable for import into the accounting system - each source row generated multiple line items The total of the ImportData worksheet (result of VBA) was not adding up to the grand total of the source data. I did some debugging and realized that the last row of source was not getting processed. I added some Debug.Print lines to tell me which lines were being processed, and isolated the UsedRange.Rows.Count as the culprit. I've tried several things, such as inserting more rows, deleting some rows, and the count was always coming up one short. So, I ended up turning off wrapping - and the problem went away! But now, if I go to the same worksheet and turn the wrapping on, the count is still correct. I cannot recreate the problem. Once the audit/budget nonsense that's going on right now is over, I'll look at this in more depth, but as of now I don't have time to repeat what was originally done step-by-step to isolate the bug. "Jim Cone" wrote: What result do you get if you use one of the following to determine the last row? '-- iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row With ActiveSheet.UsedRange iRow = .Rows(.Rows.Count).Row End With -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "ilia" wrote in message I was just running a small macro that processes a datasheet. This is the code, in the worksheet's module: -snip- For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count I noticed later that I'm missing some of the data, so I did some debugging - and turned out that UsedRange.Rows.Count was returning one row less than there actually were in the data sheet! -snip- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 formulas acting weird | Excel Discussion (Misc queries) | |||
Excel 2007 Weird Image problem | Excel Discussion (Misc queries) | |||
Excel 2007 Weird Image problem | Excel Programming | |||
weird excel 2007 bug | Excel Discussion (Misc queries) | |||
weird excel 2007 bug | Excel Programming |