excel, formula help

[amna77]

formula IF help
Hi i have formula in column AD and that is =IF(AC18>0, NOW()," "), trying to show current time in column AC, which it shows, but the thing is when i move to next row, there i have formula =IF(AC19>0, NOW()," "), it shows me current time, but it also chages the last row time to current as well. as I move along, it chages the pervious row time to current time as well. i don't want to change the previous row times,
Any help please.
Thanks in advance

 

[TonyJollans]

Hi amna77,

As long as you have functions in cells they will keep on getting re-evaluated. If you want a constant set when another cell is updated you must do it via code in the Worksheet_Change Event. Post back if you want help - or better, ask in the VBA Forum

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[amna77]

actually i had codes on work sheet change event. it was working fine, but problem is people insert column inbetween there. becasue in vba codes i have to define exactly what column i am talking about, it does not change reference of column by it self, like formula does, if we don't put $ in the formula. thats why i was trying to get formula, so if someone insert columns inbetween then excel going to refresh column refence byitself in the formula.
any help please
Thanks

 

[KenWright]

If it was working fine before and people inserting columns is what screws you up then then why not just name the first cell in each of 'your' columns and then find the column number of your named cells each time with your code and use that as a reference?

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[amna77]

Thanks for reply. there are my codes, how would i change to clumn numbers, please help.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Then Exit Sub
Dim r As Long
r = Target.Row
If Range("AC" & r).Value <> "" Then

Application.EnableEvents = False
Range("AE" & r).Value = Now
Range("AE" & r).NumberFormat = "hh:mm AM/PM"
Range("AD" & r).Value = Format(Date, "mm/dd/yyyy")

Application.EnableEvents = True

End If
End Sub

 

[TonyJollans]

Hi amna77,

Using Ken's suggestion, name cell AC1 as, say, "ColAC", AD1 as "ColAD" and AE1 as "ColAE" (use Insert > Name > Define from the menu on the worksheet), and then change your code to ..

[blue]Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row < 3 Then Exit Sub
    Dim r As Long
    r = Target.Row
    If Cells(r, Range("ColAC").Column).Value <> "" Then
        
                Application.EnableEvents = False
                Cells(r, Range("ColAE").Column).Value = Now
                Cells(r, Range("ColAE").Column).NumberFormat = "hh:mm AM/PM"
                Cells(r, Range("ColAD").Column).Value = Format(Date, "mm/dd/yyyy")
                
                Application.EnableEvents = True
                
                        End If
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[amna77]

Thanks man, its working fine now. Thanks every one.
You guys saved my life,
Thank you thankyou.

 

[KenWright]

Good stuff - Glad you got sorted

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------