text() function problem

Originally Posted by sunwan

Hi everybody, I have a problem when using the function of text(). I set "=TEXT(SUM(A1:A5), 0) + 5" in Cell A6. Assume the answer of SUM(A1:A5) is 10, the result in A6 will be 15.

My question is how to prohibit a string value [Text(SUM(A1:A5),0)] add a number value [5] to form the result 15.....how to do this?

Thank you any opinion for me in advnace!

Hi,

the question is why would you add 5 to a text string?

=TEXT(SUM(A1:A5) + 5, 0)

will give a text answer, otherwise you can omit the Text() altogether.
---

Originally Posted by sunwan

First of all, thx for Bryan.

Imagine there are 2 sheets in the excel workbook.

Sheet1:
A1 - A5 are also number value of 2
A6 is a formula =SUM(A1:A5) (result: 10)

Sheet2:
A1 - A5 are also number value of 1
A6 is a formula =SUM(A1:A5) (result: 5)

** Suppose the struction of Sheet1 and Sheet2 are the same
=> 1. Highlight the whole Sheet2 and copy
=> 2. Paste it to Sheet1 as specfic condition (pls ref to the attached JPG file)

The result in Sheet1 will show as below:
A1 - A5 are also number value of 3
A6 is a formula =SUM(A1:A5)+5 (result: 20)
The addition process of A1 - A5 is expected, besides I want A6 remain the =SUM(A1:A5) ONLY. The addition process for it is extremely not expected. So how can i do this...pls help....thank so much

If you do not want to add the Value of A6 then you should avoid copying this cell. If you copy a cell, and then instruct Excel to Paste Special = Add the value then you will get the +5 (etc) added to the A6 cell.

---

Originally Posted by sunwan

In fact, there are a lot of data pattern like A1-A5 & A6 in Sheets (2 sheets have exact structure). I really want to have an easy way (one copy - one paste) to paste data from sheet2 to sheet1 instead of copying those desired cells one by one. Now i had chosen the paste special to perform my task, but it will concatenate the value (+5 from A6 sheet2) to cell A6 in Sheet1 and the result is =SUM(A1:A5)+5 (20). Due to String data won't execute this addition process like Cell A6. So i try to convert the value of cell A6 to String type using =TEXT(SUM(A1:A5), 0) and then copy and paste. But the value is still concatenate value to Cell A6.... How i can instruct Excel to ignore the addition process to those "formula cell".....


Pls give me more information. Thank you anyway.

You may have 'chosen' to try to use the Copy - Paste that way, but the Paste-Values does not mean 'paste those cells which had no formula' but rather 'remove the formula and paste the answer'

If you want to Copy-PasteSpecial-Add only those cells that contain no formula then you will need a little VB code to do the copy for you, selecting only those cells that contain no formula.

Originally Posted by sunwan

Because i am a beginner for the VB code of Excel, Now a few questions in my mind:
1) How to trigger the copy action using VB code?
2) How to check cell wherther is formula cell or not?
3) How to import the finished program to other excel workbooks?

I deeply appreciated you can give me the useful information / tutorials or examples about the coding in order to complete my task?

Many Many thanks!

Hi,

in http://www.excelforum.com/showthread.php?t=584092 are many listed links to VBA for Beginners etc, of which http://www.functionx.com/vbaexcel/index.htm appears recommended for new users.

For questions relating to VB code a new thread should be opened in the Programming forum.

hth
---

sunwan

Please read forum rules and in future post your question in the correct forum

See links below for rules

Originally Posted by sunwan

This is really helpful, thanks bryan. I think i will learn it well.

np, good luck with your studies, and thanks for your response.


To comply with the rules, this post could have been made under New Users Forum (it was your first thread), or perhaps under Worksheet Function Forum (which on reflection perhaps best describes it, not that this could have been seen at the start), or maybe take the site admin's recommendation and post under the Miscellaneous Forum (noted as the 'Best forum to post!') although it did originally look like a pretty General question.

Good luck with future posts.
---