Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need Time Range to return Value Eric Ocasio Excel Worksheet Functions 4 October 18th 13 09:43 PM
Protecting range limits OMER Excel Programming 0 April 27th 10 02:01 PM
Macro formula array return limits? twaddell Excel Programming 4 August 12th 09 10:00 AM
I need to return the lowest time value in a range of cells. al elkins Excel Worksheet Functions 2 January 26th 07 07:24 AM
Help with setting range limits Pete Csiszar Excel Programming 5 December 16th 03 09:32 PM


All times are GMT +1. The time now is 10:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"