W
Wil
I am new using VBA and need assistance with a FOR NEXT loop. What I need is
a subroutine that will detect a change in a range of cells in a ROW, and in
the last cell of the row, put in the date that any one of the cells in the
range was updated. Then, I need the subroutine to go to the subsequent row
and do the same process, until the subroutine encounters an empty range and
stop.
For example, if any data changes in (B2:E2), then place the date it was
changed in F2. Then go to the subsequent range (B3:E3) and then enter the
date that any data in the range was changed in F3. And then to keep checking
each row until there are no more rows to check.
A B C D E F
Part Unit Unit Pack Pack Date
Cost Rtl Cost Rtl Changed
A123 0.30 0.35 2.00 4.00 10/1/07
B234 0.45 0.40 3.00 5.00 09/1/07
C345 0.55 0.60 4.00 6.00
I saw a subroutine in a previous posting from Joerg Mochikun, but because
I'm not familiar with properly constructing a LOOP, i could only use it for a
single row:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim txt
For Each cell In Range("B2:E2")
txt = txt & cell.Text
Next
If txt <> Range("F1") Then
Range("G1") = "Last change: " & Now()
Range("F1") = txt
End If
End Sub
I think if I could construct a LOOP that incorporates the above sub then I
can get the results I need. Any help and guidance is greatly appreciated.
Thank you - Wil
a subroutine that will detect a change in a range of cells in a ROW, and in
the last cell of the row, put in the date that any one of the cells in the
range was updated. Then, I need the subroutine to go to the subsequent row
and do the same process, until the subroutine encounters an empty range and
stop.
For example, if any data changes in (B2:E2), then place the date it was
changed in F2. Then go to the subsequent range (B3:E3) and then enter the
date that any data in the range was changed in F3. And then to keep checking
each row until there are no more rows to check.
A B C D E F
Part Unit Unit Pack Pack Date
Cost Rtl Cost Rtl Changed
A123 0.30 0.35 2.00 4.00 10/1/07
B234 0.45 0.40 3.00 5.00 09/1/07
C345 0.55 0.60 4.00 6.00
I saw a subroutine in a previous posting from Joerg Mochikun, but because
I'm not familiar with properly constructing a LOOP, i could only use it for a
single row:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim txt
For Each cell In Range("B2:E2")
txt = txt & cell.Text
Next
If txt <> Range("F1") Then
Range("G1") = "Last change: " & Now()
Range("F1") = txt
End If
End Sub
I think if I could construct a LOOP that incorporates the above sub then I
can get the results I need. Any help and guidance is greatly appreciated.
Thank you - Wil