Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
VBA experts,
Please help with an explanation as to why this code is failing sporadically.. Lately, it works about 50% of the time. I'm running Excel 2010 on a Win 7 system A routine I wrote compares any two worksheets and writes out the differences (on a new sheet -"shtNew") in the form of: Column A - cell address being compared Column B - worksheet 1 cell value 'shtSource Column C - worksheet 2 cell value 'shtTarget Part of my code traps the range of rows and columns that contain data (uses the max value between sheets being compared), then utilizes this boundary for looping purposes to run the comparison. Here's how I'm capturing the range boundaries: Dim lngRow As Long ' max data row Dim lngCol As Long ' max data col Dim rngSource As Range ' range of all used cells Set rngSource = .UsedRange With rngSource lngRow = .Rows.Count lngCol = .Columns.Count End With Here's the comparison loop: With shtSource For i = 1 To lngRow For j = 1 To lngCol If .Cells(i, j) < shtTarget.Cells(i, j) Then shtNew.Range("A65000").End(xlUp).Offset(1, 0).Activate ActiveCell = .Cells(i, j).Address 'cell address being compared ActiveCell.Offset(0, 1) = .Cells(i, j) ' value in cell above on shtSource ActiveCell.Offset(0, 2) = shtTarget.Cells(i, j) ' ' value in cell above on shtTarget End If Next j Next i End With The issue is that the variable lngRow always correctly captures the row number of the last cell entry in the sheet, but lngCol sometimes gives me the max column address of the sheet ("XEX16378"). The net result is that the comparison no longer occurs and the routine returns a blank sheet if the column limit isn't correctly returned. Interestingly, checking what's being captured when things go haywire gives me a range that is correct with respect to the row, but goes to the limit on columns. For example, a range that should be "A1:Z100" returns as "A1:XEX100" Any ideas why this is happening? Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
Either there's code you left out -OR- there's no object refs to the
sheets. I suspect you've only provided a snippet and so we can see what's happening in the missing code! No need to activate anything as that throws object refs into jeopardy if you're not syntax correct in your coding. Why are you doing this? Note that UsedRange.Columns.Count does not reliably return the last column containing data. How does shtNew happen? Why not use an array? Your comparison will get done much faster than reading/writing each cell... <aircode Dim vData1, vData2, vDataOut(), n&, j& Dim lMaxRows&, lMaxCols&, wksSrc1 As Worksheet, wksSrc2 As Worksheet Set wksSrc1 = Sheets("Sheet1"): Set wksSrc2 = Sheets("Sheet2") vData1 = wksSrc1.UsedRange: vData2 = wksSrc2.UsedRange lMaxRows = Ubound(vData1) lMaxRows = Iif(UBound(vData2) lMaxRows, _ UBound(Data2), lMaxRows) lMaxCols = UBound(Data1, 2) lMaxCols = Iif(UBound(vData2, 2) lMaxCols, _ UBound(vData2, 2), lMaxCols) 'Match the array sizes vData1 = wksSrc1.UsedRange.Resize(lMaxRows, lMaxCols) vData2 = wksSrc2.UsedRange.Resize(lMaxRows, lMaxCols) 'Set output array size Redim vDataOut(1 to lMaxRows, 1 To 3) Since your loop compares all columns row by row... For n = 1 To lMaxRows For j = 1 To lMaxCols vDataOut(n, 1) = wksSrc1.Cells(n, j).Address vDataOut(n, 2) = wksSrc1.Cells(n, j).Value vDataOut(n, 3) = wksSrc1.Cells(n, j).Value Next 'j Next 'n Worksheets.Add After:=wksSrc2 ActiveSheet.Cells(1).Resize(lMaxRows, lMaxCols) = vDataOut -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
Lots of errors in my aircode, so here's tested code...
Sub CompareSheets() Dim vData1, vData2, vDataOut(), n&, j&, r& Dim lMaxRows&, lMaxCols&, wksSrc1 As Worksheet, wksSrc2 As Worksheet Set wksSrc1 = Sheets("Sheet1"): Set wksSrc2 = Sheets("Sheet2") vData1 = wksSrc1.UsedRange: vData2 = wksSrc2.UsedRange lMaxRows = UBound(vData1) lMaxRows = IIf(UBound(vData2) lMaxRows, _ UBound(vData2), lMaxRows) lMaxCols = UBound(vData1, 2) lMaxCols = IIf(UBound(vData2, 2) lMaxCols, _ UBound(vData2, 2), lMaxCols) 'Match the array sizes vData1 = wksSrc1.UsedRange.Resize(lMaxRows, lMaxCols) vData2 = wksSrc2.UsedRange.Resize(lMaxRows, lMaxCols) 'Set output array size ReDim vDataOut(1 To (lMaxRows * lMaxCols), 1 To 3) 'Since your loop compares all columns row by row... For n = 1 To lMaxRows For j = 1 To lMaxCols r = r + 1 vDataOut(r, 1) = wksSrc1.Cells(n, j).Address vDataOut(r, 2) = wksSrc1.Cells(n, j).Value vDataOut(r, 3) = wksSrc2.Cells(n, j).Value Next 'j Next 'n Worksheets.Add After:=wksSrc2 ActiveSheet.Cells(1).Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
Garry,
Thanks - you are correct about the snippets - I posted only as much as I thought was required. NewSheet is always created from scratch after first testing for that sheet's existence (and deleting it if it exists) . I normally don't activate an object if I've defined it - you are correct that there's no need to do so as you can reference it, its methods and procedures, directly. You answered my question about used range.columns - thanks I hadn't thought to use arrays when I originally wrote the routine as my comparison sheets were very small However, they've now become much larger, so I'll put your code into mine (and credit you in the header block!). By the way, I am unfamiliar with using the ampersand ('&') for variable typing. Is this a shortcut for integer? Art |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
By the way, I am unfamiliar with using the ampersand ('&') for
variable typing. Is this a shortcut for integer? It's symbolic for 'Long Integer' as row/col indexes are Long type. I rarely use 'Short Integer' since most of my loops iterate large amounts of data. I use the symbols to save typing (I have Lou Gehrig's) and so they were recommended to me by a veteran VB developer. They are... VB6 Type Declaration Character Identifiers % Integer & Long ! Single-Precision Floating Point # Double-Precision Floating Point $ String @ Currency -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
Sorry to hear you have ALS.
I looked in one of my earliest VBA books and I found the same list as you've outlined. I'm in the habit on putting a Hungarian style prefix on my variables (my dialect - somewhat close to the mainstream) Art |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
GS wrote:
By the way, I am unfamiliar with using the ampersand ('&') for variable typing. Is this a shortcut for integer? It's symbolic for 'Long Integer' as row/col indexes are Long type. I rarely use 'Short Integer' since most of my loops iterate large amounts of data. I use the symbols to save typing (I have Lou Gehrig's) Sorry to hear that. That sucks a lot. and so they were recommended to me by a veteran VB developer. They are... VB6 Type Declaration Character Identifiers % Integer & Long ! Single-Precision Floating Point # Double-Precision Floating Point $ String @ Currency All of those except Currency are standard MS BASIC and date back to *at least* the 80's. I avoid using the type symbols because there are (supposedly) interpreters and/or compilers that support this nonsense: Dim A% As Long (I've never found one that will accept that kind of thing, but I haven't looked very hard.) -- We are about to change history. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
range does not return limits correctly every time
All of those except Currency are standard MS BASIC and date back to
*at least* the 80's. I avoid using the type symbols because there are (supposedly) interpreters and/or compilers that support this nonsense: Dim A% As Long (I've never found one that will accept that kind of thing, but I haven't looked very hard.) That would be like using the apostrophe followed by REM for code comments... 'REM... Not the same as say Left() or Left$()! The point of using the symbols is to obviate the typing... Do Dim A& Or do Dim A As Long As you say, using both is nonsense; -but more like just bad practice!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need Time Range to return Value | Excel Worksheet Functions | |||
Protecting range limits | Excel Programming | |||
Macro formula array return limits? | Excel Programming | |||
I need to return the lowest time value in a range of cells. | Excel Worksheet Functions | |||
Help with setting range limits | Excel Programming |