Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 12th 15, 09:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 3
Default VBA question When I search to replace parentheses in excel it givesme "-" sign

To whom it may concern,

I have this issue with one of my VBA files and I just can't figure it out. I have this search and replace macro that does a good job but I can't figure out why it places a negative (-) sign on values without a space after the parentheses. Your help is greatly appreciated.

Start example
https://docs.google.com/spreadsheets...it?usp=sharing

Finished example
https://docs.google.com/spreadsheets...it?usp=sharing

Sub Discus_Fix()
'
' Discus_Fix Macro
' Replaces redundant information on Discus AS9102 Report

Cells.Replace What:="note: ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


End Sub

  #2   Report Post  
Old June 13th 15, 01:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default VBA question When I search to replace parentheses in excel it gives me "-" sign

To whom it may concern,

I have this issue with one of my VBA files and I just can't figure it
out. I have this search and replace macro that does a good job but I
can't figure out why it places a negative (-) sign on values without
a space after the parentheses. Your help is greatly appreciated.

Start example
https://docs.google.com/spreadsheets...it?usp=sharing

Finished example
https://docs.google.com/spreadsheets...it?usp=sharing

Sub Discus_Fix()
'
' Discus_Fix Macro
' Replaces redundant information on Discus AS9102 Report

Cells.Replace What:="note: ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_ ReplaceFormat:=False


End Sub


Your data cells contain text, but are not formatted as 'Text'. Thus,
Excel is evaluating (.140) as a number where the parenthesis indicate a
negative value. Thus, your data alignment is changed to numeric and
displays in your default format for a negative value.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #3   Report Post  
Old June 15th 15, 04:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 3
Default VBA question When I search to replace parentheses in excel itgives me "-" sign

Hello Garry,

I appreciate your support. My entire workbook is formatted as "Text" and when I run the macro it converts (.140) to -0.14 and I see the error telling me "Number Stored as Text". Is there a way I can work around this issue?

Thanks
  #4   Report Post  
Old June 15th 15, 06:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default VBA question When I search to replace parentheses in excel it gives me "-" sign

Hello Garry,

I appreciate your support. My entire workbook is formatted as "Text"
and when I run the macro it converts (.140) to -0.14 and I see the
error telling me "Number Stored as Text". Is there a way I can work
around this issue?

Thanks


I don't get this conversion behavior when I 'set' the cells to 'Text'
on the 'Number' tab of the FormatCells dialog. Perhaps you could
'deliberately' format all cells before replacing...

Cells.NumberFormat = "@"
Cells.Replace...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #5   Report Post  
Old June 16th 15, 02:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 3
Default VBA question When I search to replace parentheses in excel itgives me "-" sign

Hello Garry,

I'd like to thank you for your input and ideas. It finally worked. This is a reference dimension so by putting "REF" at the end it finally solve my issue. It finally sees it as text only :-)

Thanks again


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
How do I replace decimals starting with "." to "0." in excel? Julio Excel Discussion (Misc queries) 2 November 1st 08 07:48 AM
How to perform Search and Replace on "Tilde", CHAR(126), ~ [email protected] Excel Worksheet Functions 2 March 28th 07 06:57 PM
How do display a "+" or "-" sign when hiding columns? DTI Tustin Setting up and Configuration of Excel 1 July 13th 06 01:21 PM
turn off the " replace file?" question when saving excel files Dave Parkes Excel Programming 1 December 16th 04 05:08 PM
Is there a "replace" or "Substitute" command in Excel's VB? [email protected] Excel Programming 2 December 5th 03 07:56 AM


All times are GMT +1. The time now is 05:14 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017