Problem with For Next Loop

[SWmiller]

I recently converted about 100 IF statements into 1 For Next Loop for efficiency. But for some reason, the Loop is not working.

Here is what I have:
Dim intRow As Integer

For intRow = 2 To 150 Step 1
If Source = Cells(intRow, 3).Value Then Cells(intRow, 10).Value = Date
'Column 3 = C, Column 10 = J
Next intRow

My intensions are have column J give the Date if changes are made to the corresponding row in column C. Any help will be appreciated.

 

[logius]

When you say the "Loop is not working", what do you mean? Are you getting erroneous data? Is the loop giving an error? Also, how are you getting the "Source" and "Date" values?

 

[SWmiller]

I knew I left something out. I guess an explanation of my problem would help.

I don't get any compiling errors. When I run the spreadsheet and edit Column C, nothing changes in Column J; it just stays blank.

"Date" is the function that I am using to return a Variant(Date) containing the current system date.
As for "Source", I'm not to sure; that was given to me by the person who assisted me with converting the IF statement to the LOOP.

If this will help at all, here is one of my original IF statements which worked fine:
If Source = Range("C8&quot Then
Range("J8&quot.Value = Date
End If

 

[logius]

Well, if nothing is happening when you run the macro, then the problem must be in your

If

statement. I'm guessing the value "Source" is never getting set. A good way to find out is to use the debugger in VBA. Go to the editor and select the macro that you want to run (view the code). Then, hit F8 and the debugger will step through each line of code (make sure your mouse cursor is somewhere inside the

Sub

). You have to keep hitting F8 everytime you want to execute the next line of code. It's kind of tedious, but if you move your mouse over individual items, you can see their values during execution. This way you can see if the value for "Source" is ever getting set.

 

[SWmiller]

That was it; I didn't have my Source set. It was just a careless and inexperienced error on my part.

Now that I fixed that error, I have another problem which is rather strange. I compiled the code successfully. But when I run the macro, it automatically fills in the current date in rows in column J from correspoding rows that are blank in column C. When actually what I need is it to fill in the date in column J from correspoding rows that are ever changed or edited in column C, not when they are blank. For example, if I change a value in cell C8, the current date should display in J8. But if cell C9 is blank, cell J9 needs to be blank.

Here is an update of my code. If you see anything wrong, please let me know.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Dim intRow As Integer

For intRow = 2 To 150 Step 1
If Source = Cells(intRow, 3).Value Then Cells(intRow, 10).Value = Date
'Column 3 = C, Column 10 = J
Next intRow
End Sub

 

[Sawatzky]

Could it be that your source is empty too, when cell C9 is empty. Because if they are equal aswell

if ("" = "" )
'which would be true

perhaps you have to check

For intRow = 2 To 150 Step 1
if NOT Cells(introw,3).Value = "" then

If Source = Cells(intRow, 3).Value Then Cells(intRow, 10).Value = Date
'Column 3 = C, Column 10 = J
end if
Next intRow

 

[logius]

Or, you could leave out the blank cells altogether and set the data into a *gasp* Named Range (wow, I'm actually using tech terms). To do this, just select the whole column of data and set it to a variable.

Dim OhLookAtMyRange As Range
Dim ThisIsOnlyACell as Object

     Set OhLookAtMyRange = ActiveSheet.Range( _
                           Range("C2"), _
                           Range("C2").End(xlDown))

That little

End(xlDown)

command is a beauty. The only problem would be if you have any blanks between data, I'm not sure if it'll work in that case.

From there, you can use the For Each loop to reference each individual cell in the range. It works a whole lot better since you don't need to worry about counters.

     For Each ThisIsOnlyACell in OhLookAtMyRange
         If Source = ThisIsOnlyACell.Value Then
             ThisIsOnlyACell.Offset(0,7).Value = Date
         End If
     Next ThisIsOnlyACell

Don't be shaken by the

Offset

method. All it does is use the ActiveCell as the starting point for a cell reference. Since "J" is 7 columns away from "C", I used the 7 in the column reference. And since you want the cell in the same row as "C", I used a 0 in the row reference.

Hope this helps. You may have to tweak it a little to suit your needs.