Macro targeting specific row

July 11, 2011 at 08:28:56
Specs: Windows Vista
I have a spreadsheet with a list of clients and the number of days since they were contacted last. I have a macro assigned to a button that sets the date as today when clicked (stating the client was contacted today) I also have a sort macro assigned to a button that sorts the content with the most number of days since contact at the top. Everything works to that point. But when the data is sorted the macro button's target to put the date stays the same.

Is there any way to have the macro put the output in the D column and the row is whatever row the button is in? Or just the cell immediately next to it?

Here's the macro Im using:

Sub Button2_Click()
ActiveSheet.Range("D2").Value = Date
End Sub


See More: Macro targeting specific row

Report •


#1
July 11, 2011 at 09:50:31
Are you looking to have the Date placed in the Column D next to the MAX number in a range?

Using A1:A6 as the range, this code will do that.

Sub MaxDate()
 myMax = WorksheetFunction.Max(Range("A1:A6"))
  With Range("a1:A6")
   Set c = .Find(myMax, LookIn:=xlValues)
  End With
 ActiveSheet.Range("D" & c.Row) = Date
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 11, 2011 at 10:07:14
Im looking to have the output of the macro button always be in the same row as the button.

Right now if it's linked to output to D3 for example, when I sort the data and that data that was in row 3 now goes to row 10, the output from the button still sends to D3, I want it to now go to D10


Report •

#3
July 11, 2011 at 12:23:38
The macro you posted is hardcoded to put the Date in D2.

Your latest response says it's linked to D3.

Do you have more than one button?

Wouldn't it be just as easy to select the cell where you want the date and have the date put into that cell? That's how I do it...I select a cell and then click on a button in my tool bar that's attached to this code:

Sub UpdateDate()
 Selection = Date
End Sub

If you want it more automatic, you could use a SelectionChange macro so all you would need to do is select a cell. This code will only put the date in a cell that is within Column D and only if you select a single cell.

There is no button needed for this code, it runs as soon as the cell is selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 4 Then
    Target = Date
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 11, 2011 at 14:50:17
Ya, I have more then one button, one in each row. So there is code for D2, D3, D4, etc

Report •

#5
July 11, 2011 at 22:24:31
Why?

Did you try either of my suggestions?

You can do it with one button or even without any buttons - just select the cell where you want the current date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 12, 2011 at 08:06:31
Im making it for someone and thats what they wanted so I was trying to do it that way.

I didn't understand how to put yours in if not attatching it to a button


Report •

#7
July 12, 2011 at 18:53:56
re: "I'm making it for someone and that's what they wanted"

Maybe that's because it's the only way they know how to do it.

For this macro, right-click the sheet tab for the sheet you want the dates in.
Choose View Code and paste the macro into the pane that opens.

Whenever you select a single cell in Column D, the current date will be entered.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Target.Column = 4 Then
    Target = Date
  End If
 End If
End Sub

As written, the code will put the date in any cell in Column D.

If you want to limit the automatic date input to a specific range, replace this line:

 If Target.Column = 4 Then

with this (as an example)"

If Not Intersect(Target, Range("D2:D10")) Is Nothing Then

The date will only be entered in D2:D10.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
July 12, 2011 at 19:17:23
I had another thought:

If the user wants make sure that a date doesn't get enter via a random click in the specified range, you can add a confirmation message box:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  If Not Intersect(Target, Range("D2:D10")) Is Nothing Then
    answer = MsgBox("Enter Date In Selected Cell?", vbYesNo)
     If answer = vbYes Then
      Target = Date
    End If
  End If
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question