how to paste number and value formats of a cell containing a formula

[DeWaal Le Grange]

hi therer excel experts

i really need help with something, i have got a whole bunch of names in A43:A1100, now in B13 i have a dropdown list of all these names. in B5 i have the simple NOW() formula witch displays the current date and time.

what i need is when B13 is = to any one of the names in A43:A1100 it must do a paste special of the date and time displayed in B5 in I43, it must only copy the Values and number not the formula.

is there some sort of fba code i can attach to a button or formula i can use for this.

thanks a million

DeWaal

 

[Domski]

Right click on the sheet tab and select View Code and paste this onto the code page that comes up:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim findRange As Range
If Intersect(Target, Range("B13")) Is Nothing Then Exit Sub
Set findRange = Range("A43:A1100").Find(Range("B13").Value, LookIn:=xlValues)
If Not findRange Is Nothing Then Range("I43") = Now
End Sub

It doesn't need the =NOW() formula in B5.

Dom

 

[DeWaal Le Grange]

Hi Dom

Thank you so much, it works 100%, there is one thing i did not mention. i need the date to be placed next to the name, B13 is = to in column I... sorry my bad, can you help me out?

So if A100 is = to B13 the date must be placed in I100
A101 is = to B13 the date must be placed in I101

thanks

DeWaal

 

[Domski]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim findRange As Range
If Intersect(Target, Range("B13")) Is Nothing Then Exit Sub
Set findRange = Range("A43:A1100").Find(Range("B13").Value, LookIn:=xlValues)
If Not findRange Is Nothing Then findRange.Offset(0, 9) = Now
End Sub

Dom

 

[DeWaal Le Grange]

Dom you are the best, thank you so much, you have been a great help, now let me go and impress some people.

have a great day.

DeWaal