Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Has =TEXT(. . .,0) been applied?

Is there a way to tell if a value in a cell has been treated with the
=TEXT formula, and then pasted back as values?

Pete
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Has =TEXT(. . .,0) been applied?

On Jun 1, 8:25*am, Pete wrote:
Is there a way to tell if a value in a cell has been
treated with the =TEXT formula, and then pasted back
as values?


Certainly not literally. You cannot know what was in the cell before
it was changed.

However, you can write a macro that determines if a cell contains a
formula or not.

Function cellHasFormula(r As Range) As Boolean
cellHasFormula = r.hasFormula
End Function

AFAIK, there is no way to accomplish the same thing using just Excel
functions.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Has =TEXT(. . .,0) been applied?

Hi Pete,

Am Wed, 1 Jun 2011 08:25:16 -0700 (PDT) schrieb Pete:

Is there a way to tell if a value in a cell has been treated with the
=TEXT formula, and then pasted back as values?


for TEXT formula in range("C1:C10"):
Sub TextFormula()
Dim rngCell As Range

For Each rngCell In Range("C1:C10")
With rngCell
If Left(.Formula, 5) = "=TEXT" Then
.Value = .Value
End If
End With
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Has =TEXT(. . .,0) been applied?

On Jun 1, 12:13*pm, Claus Busch wrote:
Hi Pete,

Am Wed, 1 Jun 2011 08:25:16 -0700 (PDT) schrieb Pete:

Is there a way to tell if a value in a cell has been treated with the
=TEXT formula, and then pasted back as values?


for TEXT formula in range("C1:C10"):
Sub TextFormula()
Dim rngCell As Range

For Each rngCell In Range("C1:C10")
With rngCell
* * If Left(.Formula, 5) = "=TEXT" Then
* * * * .Value = .Value
* * End If
End With
Next
End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


Thank you both joeu2004 and Claus.

Pete
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CF Row after CF applied to Column Dax Arroway Excel Worksheet Functions 4 December 1st 09 06:24 PM
CF to Row after CF is applied to Column Dax Arroway Excel Worksheet Functions 2 November 17th 09 06:58 PM
CF for Row when CF is applied to Column Dax Arroway Excel Worksheet Functions 2 November 17th 09 06:56 PM
Need help on my Jobs applied too SS John the Baptist Jr. Excel Discussion (Misc queries) 1 August 31st 06 12:06 AM
multiple numeric values applied to single text Fam via OfficeKB.com Excel Discussion (Misc queries) 5 February 2nd 06 09:17 PM


All times are GMT +1. The time now is 04:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"