MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro mania - needs to be progressive

Posted by George J on January 10, 2002 9:17 AM

This is a follow on from a previous post.
The spreadsheet I am working on will be a list of addresses (constantly updated) in column A. I will enter a date in column F and the macro will give me the corresponding date in column C.

The problem is I do not want the entire spreadsheet influenced by the macro, just the row in which the macro is activated - this will have an active cell. Also my current macro is only for row 2. What do I change so that it affects any row, but with an active cell?

Sub Lothian()
'
' Lothian Macro
' Macro recorded 07/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+l
'
If WeekDay(Range("F2")) = 7 Then
Range("C2").Value = Range("F2").Value - 15
ElseIf WeekDay(Range("F2")) = 1 Then
Range("C2").Value = Range("F2").Value - 16
Else
Range("C2").Value = Range("F2").Value - 14
End If


End Sub

I am totally lost.
Help appreciated.

George

Posted by Juan Pablo G. on January 10, 2002 9:44 AM

Is this what you want ?

Sub Lothian()
If WeekDay(Cells(ActiveCell.Row,6)) = 7 Then
Cells(ActiveCell.Row,3).Value = Cells(ActiveCell.Row,6).Value - 15
ElseIf WeekDay(Cells(ActiveCell.Row,6)) = 1 Then
Cells(ActiveCell.Row,3).Value = Cells(ActiveCell.Row,6).Value - 16
Else
Cells(ActiveCell.Row,3).Value = Cells(ActiveCell.Row,6).Value - 14
End If

Juan Pablo G.
End Sub

Lothian Macro Macro recorded 07/01/2002 by gj Keyboard Shortcut: Ctrl+l

Posted by George J on January 11, 2002 1:33 AM

Not working

It sounds right, but when I run the macro no dates are coming up. Any suggestions?

George Is this what you want ? Lothian Macro Macro recorded 07/01/2002 by gj Keyboard Shortcut: Ctrl+l

Posted by George J on January 11, 2002 1:36 AM

Sorry - It is working THANKS

Lothian Macro Macro recorded 07/01/2002 by gj Keyboard Shortcut: Ctrl+l
I edited the macro and tried to run it. After I closed down the VBA screen it ran ok.

Thanks for this
George