integer value won't change
 

I have a procedure which contains the following:
------------------------------------------------------------------------------------------------------------------------------
Dim i as integer
Dim r as integer

CheckCSXU:
i = 3
r = 1

For Each ccell In Sheets("COMPARED").Range(Cells(1, 1), _
Cells(65535, 1).End(xlUp)) 'cycle through COMPARED sheet
comparedvalue = ccell.Value

Do
With Sheets(sheettitle)
csxuvalue = .Cells(i, r).Value
End With

If i 22 And r < 14 Then
i = 3 And r = r + 3 ***PROBLEM HERE***
End If

If i 22 And r = 14 Then
i = 26 And r = 2
End If

If i 54 And r < 14 Then
i= 26 And r = r + 3
End If

If i 45 And r = 14 Then
GoTo Continue 'calls next sheet in workbook
End If

If comparedvalue = csxuvalue Then 'found a match, so add the date
next to compared cell
Sheets("COMPARED").Select
ccell.Offset(0, 2).Value = Sheets(sheettitle).Cells(i, r +
1).Value
i = i + 1
Else
i = i + 1
End If
Loop Until i = 45 And r = 13
Next ccell 'next cell in "COMPARED" sheet
------------------------------------------------------------------------------------------------------------------------------
Problem is that when the first condition is met (i 22 And e < 14) I would
like i to now equal 3, and r to equal 4, but this doesn't happen. i changes
from 1 to 0 and r stays at 1. Could someone help a novice out?

Thanks for your time...

integer value won't change
 

Steve,

You can't use the AND statement to execute multiple statements in one line.
It is a boolean operator. Change this

If i 22 And r < 14 Then
i = 3 And r = r + 3 ***PROBLEM HERE***
End If

To this:

If i 22 And r < 14 Then
i = 3
r = r + 3
End If

Robin Hammond
www.enhanceddatasystems.com

"Steve" wrote in message
ink.net...
I have a procedure which contains the following:
------------------------------------------------------------------------------------------------------------------------------
Dim i as integer
Dim r as integer

CheckCSXU:
i = 3
r = 1

For Each ccell In Sheets("COMPARED").Range(Cells(1, 1), _
Cells(65535, 1).End(xlUp)) 'cycle through COMPARED sheet
comparedvalue = ccell.Value

Do
With Sheets(sheettitle)
csxuvalue = .Cells(i, r).Value
End With

If i 22 And r < 14 Then
i = 3 And r = r + 3 ***PROBLEM HERE***
End If

If i 22 And r = 14 Then
i = 26 And r = 2
End If

If i 54 And r < 14 Then
i= 26 And r = r + 3
End If

If i 45 And r = 14 Then
GoTo Continue 'calls next sheet in workbook
End If

If comparedvalue = csxuvalue Then 'found a match, so add the date
next to compared cell
Sheets("COMPARED").Select
ccell.Offset(0, 2).Value = Sheets(sheettitle).Cells(i, r +
1).Value
i = i + 1
Else
i = i + 1
End If
Loop Until i = 45 And r = 13
Next ccell 'next cell in "COMPARED" sheet
------------------------------------------------------------------------------------------------------------------------------
Problem is that when the first condition is met (i 22 And e < 14) I
would like i to now equal 3, and r to equal 4, but this doesn't happen. i
changes from 1 to 0 and r stays at 1. Could someone help a novice out?

Thanks for your time...

integer value won't change
 

Robin has given you one way to fix your problem. The other is to use
the colon (:) as a statement separator
i = 3 : r = r + 3

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article . net,
says...
I have a procedure which contains the following:
------------------------------------------------------------------------------------------------------------------------------
Dim i as integer
Dim r as integer

CheckCSXU:
i = 3
r = 1

For Each ccell In Sheets("COMPARED").Range(Cells(1, 1), _
Cells(65535, 1).End(xlUp)) 'cycle through COMPARED sheet
comparedvalue = ccell.Value

Do
With Sheets(sheettitle)
csxuvalue = .Cells(i, r).Value
End With

If i 22 And r < 14 Then
i = 3 And r = r + 3 ***PROBLEM HERE***
End If

If i 22 And r = 14 Then
i = 26 And r = 2
{snip}