Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2012, 03:56 AM
gsrikanth gsrikanth is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office XP
Competent Performer
Is it correct (PasteSpecial function)
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default Is it correct (PasteSpecial function)

Public Function sss(strCell) As String
sss=strCell.PasteSpecial
End Function

Selection.PasteSpecial Paste:=xlPasteValues




I want to merge above two make pastespecial function?

my idea is to get data with out formula
Reply With Quote
  #2  
Old 01-07-2012, 03:36 PM
JBeaucaire JBeaucaire is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

A FUNCTION() is by definition a formula. If it is used in a cell, that is.

However a macro can be invoked to run on the activecell. This macro will convert the current selected cells into flat values:

Code:
Sub RemoveFormulas()
    Selection.Value = Selection.Value
End Sub
Reply With Quote
  #3  
Old 01-08-2012, 09:08 PM
gsrikanth gsrikanth is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office XP
Competent Performer
Is it correct (PasteSpecial function)
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by JBeaucaire View Post
A FUNCTION() is by definition a formula. If it is used in a cell, that is.

However a macro can be invoked to run on the activecell. This macro will convert the current selected cells into flat values:

Code:
Sub RemoveFormulas()
    Selection.Value = Selection.Value
End Sub
in below function by giving =sss(a1) in formula bar it well get font size of user

Public Function sss(strCell) As String
sss=strCell.Font.Size
End Function

like that only i need, if i give =sss(a1) remove formula and give flat value
Reply With Quote
  #4  
Old 01-09-2012, 01:12 AM
JBeaucaire JBeaucaire is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

The normal formula =A1 will give you the "value" from cell A1.

There is not formula (or even User Defined Function) that causes another cell to lose it's formula. Only a regular macro can do that, not a function.
Reply With Quote
  #5  
Old 01-09-2012, 01:27 AM
gsrikanth gsrikanth is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office XP
Competent Performer
Is it correct (PasteSpecial function)
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by JBeaucaire View Post
The normal formula =A1 will give you the "value" from cell A1.

There is not formula (or even User Defined Function) that causes another cell to lose it's formula. Only a regular macro can do that, not a function.
we can make macro to function now?
i need that function
Reply With Quote
  #6  
Old 01-09-2012, 09:06 AM
JBeaucaire JBeaucaire is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

Quote:
From your PM:

A1: 1
B1: 2
C1: =A1+B1
D1: =getflatvalue(C1)

The result would be 3.

If we're going to enter a formula into D1, then we might as well enter: =C1
Reply With Quote
  #7  
Old 01-17-2012, 11:07 PM
gsrikanth gsrikanth is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office XP
Competent Performer
Is it correct (PasteSpecial function)
 
Join Date: Dec 2011
Posts: 133
gsrikanth is on a distinguished road
Default

Quote:
Originally Posted by JBeaucaire View Post
If we're going to enter a formula into D1, then we might as well enter: =C1
there is no such function =getflatvalue(c1)
Reply With Quote
  #8  
Old 01-17-2012, 11:23 PM
JBeaucaire JBeaucaire is offline Is it correct (PasteSpecial function) Windows XP Is it correct (PasteSpecial function) Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

I know we are in a round robin here, you don't understand what I'm saying and I don't understand what you're trying to accomplish.

Based on everything you've said, if you want to enter a formula into D1 to get the "value" to appear that is the result of the formula in C1, then the formula to enter into D1 would be:

=C1


There is no formula you can enter into D1 that will then delete itself leaving only the flat value behind. Formulas can only display values, they can't delete themselves. Only VBA can do that.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Backup PST with correct folders dmarques Outlook 2 06-04-2011 09:59 AM
Is it correct (PasteSpecial function) No scroll bar even though settings are correct Lorretta Word 2 05-06-2011 07:31 AM
Is it correct (PasteSpecial function) Correct Format needed TonyB Mail Merge 3 05-03-2011 01:10 AM
So what is the correct value of PONT_STRING anyway? DrDOS Outlook 0 11-21-2010 04:30 AM
Is it correct (PasteSpecial function) Auto Correct to Stay On sanzlink Word 1 01-16-2009 06:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft