PDA

View Full Version : Something "Off" About my Code ??



Saladsamurai
09-01-2009, 02:28 PM
:) Okay then :)

I wrote this little ditty just to check the lengths of the rows of the 1st WorkSheet and compare them with the rest of the relevent WorkSheets to make sure that they have the same amount of data in each row.

It appears that they do, but I built in the MsgBox to error check and I am getting a wacky result.

The code simply runs through all of the cells in a row and checks to make sure that the two rows are of equal lengths. A row ends with the 1st empty cell.

My message box is saying that there is a problem on worksheets 7 and 8 at Cells(18, 51)

But there is NO DATA there on ANY of the worksheets.

What gives? File Attached

Sub CheckRowLengths()

Dim i As Long, j As Long
Dim k As Integer
Dim RefSheet As Worksheet
Dim RefCount As Long, Counter As Long

Set RefSheet = Worksheets("Power")
RefCount = 0
Counter = 0

For k = 2 To 8


For i = 1 To 30
For j = 1 To 50
If RefSheet.Cells(i, j) <> "" Then
RefCount = RefCount + 1
End If

If Worksheets(k).Cells(i, j) <> "" Then
Counter = Counter + 1
End If
Next j

If Counter <> RefCount Then
MsgBox i & " , " & j & " , " & k
End If

RefCount = 0
Counter = 0

Next i
Next k
End Sub

p45cal
09-01-2009, 04:00 PM
A couple of things wrong here:
1.You only check if the counters are equal at the end of a row of 50 cells, but the inequalities could have occurred anywhere in that row.
2. You use the value j to report which column you think the difference is in, but j has been incremented by 1 by the line Next j directly above your test. Worse, j will always be 51 in the message box.
Try this instead:Sub CheckRowLengths()
Dim i As Long, j As Long
Dim k As Integer
Dim RefSheet As Worksheet

Set RefSheet = Worksheets("Power")
For k = 2 To 8
For i = 1 To 30
For j = 1 To 50
If (RefSheet.Cells(i, j) <> "" And Worksheets(k).Cells(i, j) = "") Or (RefSheet.Cells(i, j) = "" And Worksheets(k).Cells(i, j) <> "") Then
Application.Goto Worksheets(k).Cells(i, j)
MsgBox Cells(i, j).Address & " on sheet " & Sheets(k).Name
Stop 'step through the next 1 line with F8 before pressing F5 again:
Application.Goto RefSheet.Cells(i, j)
'Exit For 'enable this if you want to abandon searching for differences on a row after the first difference on that row has been detected
End If
Next j
Next i
Next k
End Sub

macropod
09-01-2009, 04:12 PM
Hi S,

But there is NO DATA there on ANY of the worksheets.There ARE data on the sheets concerned in the workbook attached to your post. Sheets 7 & 8 are named 'Temp ISX' and 'Temp CFD', respectively. Hint: do Ctrl-Home then Ctrl-End to see where the last data are.