Simple Macro

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
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
ABCD
1testtestcomplete
2
3844242
4
5
Sheet3


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

please help
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Give this a try:
Code:
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
You could use a calculate event like so I suppose:

Code:
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

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
As a Worksheet Change event

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

Jayden

Board Regular
Joined
Mar 28, 2003
Messages
141
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
 
Joined
Jul 30, 2006
Messages
3,656
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

Board Regular
Joined
Mar 28, 2003
Messages
141
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

Board Regular
Joined
Mar 28, 2003
Messages
141
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Perhaps you mean you want something like this for your code

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"","""")")
 

Forum statistics

Threads
1,181,055
Messages
5,927,861
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top