Capturing a Cell Reference After Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With the following sample...

A B C
1. 2/1/05 AAA 344
2. 1/2/05 BBB 342
3.
4. 2/3/05 AAA 250

I want to create the following action in a macro.
When an entry is made in column B, I want the macro to enter the next
consecutive number higher than the maximum number in column C. I want that
new maximum number to be entered into column C next to the entry just made in
column B.
In the above example, if I enter anything into column B, the number 345
would be entered into C3.

1.Is the code below going in the right direction?
2. Instead of cell C2 getting the new max number, I need to "capture" the
row reference from the column B input and put the new max number into the
same row in column C.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("C2") = Application.WorksheetFunction.Max(Range("C:C")) + 1
End If
End Sub

TIA.
 
You almost have it. Change the "Range("C2")" to "Target.Offset(, 1)"
without the quotes.
HTH Otto
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro storing as a date? 11
Copy columns and paste with blank column between 5
Modify working code to expand the output. 5
Excel VBA to move cursor from 1 cell to another, once original cell is filled with data. 3
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Transfer array element offset value to a matched value 3
Array with many cells from a row but not in order or together 11
Adding numbers in a range 3

Back
Top