Simple Macro

[Jayden]

Hello

I am trying to create a simple macro that will allow me to paste a formula into a cell and then have it change from the formula to the value given

So this is what i want

test2.xls
	A	B	C	D
1	test		testcomplete
2
3	84	42	42
4
5
Sheet3

I want to have the formula to get the value but i only want the value to be displayed

please help

 

[Lewiy]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Formula, 1) = "=" Then
    Target.Copy
    Target.PasteSpecial xlPasteValues
End If
End Sub

 

[brian.wethington]

You could use a calculate event like so I suppose:

Private Sub Worksheet_Calculate()

UsedRange.Copy
UsedRange.PasteSpecial xlPasteValues

End Sub

This is sheet code so right click the tab of the sheet you want to use it with and choose view code and paste it there.

Hope this helps!

 

[SteveO59L]

As a Worksheet Change event

Private Sub Worksheet_Change(ByVal Target As Range)
Range("a1") = Range("b1").Value + Range("c1").Value
End Sub

 

[Jayden]

ok but what if the formula is greater than what you are giving and also i want to runthis formula for different files but the data will be in the same format for each different file

this is the formula that will be running

=IF(AND(INDEX(Sheet2!A:A,MATCH(C3,Sheet2!B:B,0))="Pinging",INDEX(Sheet2!B:B,MATCH(C3,Sheet2!B:B,0)+1)<>"timed"),"yes","")

so in the above example instead of the simple sum formula this will have to run and then the results of it will be displayed in a value only status
Can this be done

 

[stanleydgromjr]

Jayden,

If you use Lewiy's code, you will get the same result. The results of the formula will only then show/return "yes" or a "".

Test it and see.

Have a great day,
Stan

 

[Jayden]

ok but where will the formula be inputted into the code

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Formula, 1) = "=" Then
Target.Copy
Target.PasteSpecial xlPasteValues
End If
End Sub

For this to work I need tho input this formula into the code

=IF(AND(INDEX(Sheet2!A:A,MATCH(C3,Sheet2!B:B,0))="Pinging",INDEX(Sheet2!B:B,MATCH(C3,Sheet2!B:B,0)+1)<>"timed"),"yes","")

 

[brian.wethington]

ok but where will the formula be inputted into the code

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Formula, 1) = "=" Then
Target.Copy
Target.PasteSpecial xlPasteValues
End If
End Sub

For this to work I need tho input this formula into the code

=IF(AND(INDEX(Sheet2!A:A,MATCH(C3,Sheet2!B:B,0))="Pinging",INDEX(Sheet2!B:B,MATCH(C3,Sheet2!B:B,0)+1)<>"timed"),"yes","")

This is assuming you are putting the code into the sheet and once it is put into the sheet then it will execute the code

 

[Jayden]

This is true

But I want to have the code put the formula into the sheet then calculate the data then display only the value

 

[brian.wethington]

Perhaps you mean you want something like this for your code

Range("F1").Value = Evaluate("IF(AND(INDEX(Sheet2!A:A,MATCH(C3,Sheet2!B:B,0))=""Pinging"",INDEX(Sheet2!B:B,MATCH(C3,Sheet2!B:B,0)+1)<>""timed""),""yes"","""")")