Paste as absolute Value

[Andrew87..]

Hi all,

i was unable to find the answer I need so I'm kindly turning for experts' help.

I simply need copy and paste a negative value as ABS value. if I have -6391 , it would be pasted as 6391. I have tried to look for workaround such as copying all characters behind minus, but haven't managed it.

Range("C" & row).Select
    Selection.Copy
Session.findById("wnd[0]/usr/txtBSEG-WRBTR").Text = ThisWorkbook.Sheets("Sheet1").Range("C" & row).Value

I cannot change all values to absolute as I need it visible in the worksheet. I could obviously use a helper column, but I am just wondering if there is any proper solution.

Many thanks!

[Glenn Kennedy]

Have you looked at this:
http://www.mrexcel.com/forum/excel-q...-can-done.html

[Andrew87..]

I have seen it, however I need to paste line by line to SAP and I don't really know how I would re-do the code from the link to my use.

[Doc.AElstein]

Hi Andrew87..
Something like this ?

Sub testie() '    http://www.excelforum.com/excel-programming-vba-macros/1161452-paste-as-absolute-value.html
Dim row As Long, strABS As String
 Let row = 25
 Let ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value = -6391
 Let strABS = Replace(ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value, "-", "", 1, 1) ' Replace(  In -6391   ,   "-"    with  ,  ""    ,   start looking at first character     ,    just do it to the first occurance    )
MsgBox prompt:=" String absolute is """ & strABS & """"
End Sub

Alan

EDIT: What is SAP ?

[Andrew87..]

Hi,
Thanks for your answer. Doesn't this just do the replacement? Could you please advise if there is a way to keep the current formatting without ABS in 1 column and do the pasting to another program (SAP) without needing additional column with the replace?

[Doc.AElstein]

Hi Andrew87..
Sorry, I do not quite follow you?
I do not quite understand what you are trying to do?
I have not changed the value or format in any cell. I just put the -6391 in an arbitrary cell to demo.

My strABS is what you would pass to whatever it is that wants the Absolute value of what is in a cell

I expect i have just not quite grasped exactly what you are trying to do, sorry

Alan

[Andrew87..]

Ah ok, I will give it a try and report here.

Thanks!

[Doc.AElstein]

Hi Andrew87..
Just another demo:
60 I copy from one cell
and
70 I paste to the adjacent one ( I maintain all Formats )

Then I

90 overwrite the Value only in the Cell that i am pasting to with the absolute Value from the original Cell, whose format and Value remains as it was

Sub testie2() '    http://www.excelforum.com/excel-programming-vba-macros/1161452-paste-as-absolute-value.html
10   Dim row As Long
20    Let row = 25
30    Let ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value = -6391
40
50   ' Copy and Paste all including all formats
60    ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Copy ' Range Copy Method
70    ThisWorkbook.Worksheets("Sheet1").Range("D" & row).PasteSpecial xlPasteAllUsingSourceTheme ' Range PasteSpecial Method with one of the many Options
80   ' Overwrite Cell value with Cell absolute value
90    Let ThisWorkbook.Worksheets("Sheet1").Range("D" & row).Value = Replace(ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value, "-", "", 1, 1) ' Replace(  In -6391   ,   "-"    with  ,  ""    ,   start looking at first character     ,    just do it to the first occurance    )
100 '
















' http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
' http://www.excelforum.com/showthread.php?t=1144116&p=4414189&highlight=#post4414189
' http://www.excelforum.com/showthread.php?t=1114014&page=3&p=4256195#post4256195
' http://www.mrexcel.com/forum/excel-questions/828241-visual-basic-applications-autofilter-specialcells-xlcelltypevisible-copy-only-values-not-formulas.html#post4043472
' http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value-3.html#post4071766
' http://www.excelforum.com/excel-programming-vba-macros/1159780-syntax-problem-with-intersect-statement.html#post4504411
' http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
' https://app.box.com/s/lts3h2ejf33yj2i2xmaq5zrq784qwwl9
' http://www.excelforum.com/showthread.php?t=1154829&page=2&p=4483347&posted=1#post4483344



End Sub

_............................


or you just want

Session.findById("wnd[0]/usr/txtBSEG-WRBTR").Text = Replace(ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value, "-", "", 1, 1)

or you just want line 90 on its own



Possibly the confusion is arising as you are using the word Paste. Maybe you are saying that you want to pass the absolute value of a cell to something. Or in English , you want to give the absolute value from a cell to something.
The word Paste tends to suggest the various Paste or Paste Special operations in VBA , which might send a VBA person off in the wrong direction of thinking. I expect that is what happened initially with Glenn


Alan

[MrShorty]

I could obviously use a helper column, but I am just wondering if there is any proper solution.

As one who looks at this question and says to himself that a helper column with the =ABS() function in it is the most obvious easy solution, I would wonder why the use of such a helper column is not considered a "proper" solution. The code given in the OP then seems like it would work without modification (except for changing the column being referenced). It seems to me like we are going to a lot of unnecessary work to avoid using the helper column.

But that's just me.

[Doc.AElstein]

I am a great fan of helper columns. Especially with Formulas. I would rather break a long complicated formula down into helper columns. I find both understanding and Debugging better then. The helper columns can be anywhere, even in a another Worksheet. I like that Approach. In the main Worksheet I just have the last Formula and keep everything tidy. The in the helper Worksheet I can see everything.
Working on the helper Worksheet is then like taking the back off an old television to get at all the workings to repair or modify it, rather than havig everything compressed in a small chip in a modern television that you can do nothing esilly with.


In the OP's case, if I have sussed out what he wants , then he just "takes"
Replace(ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value, "-", "", 1, 1)
instead of
ThisWorkbook.Worksheets("Sheet1").Range("C" & row).Value
So maybe no helper column is needed
Alan

[Andrew87..]

The last one is exactly what I needed! Many thanks and my apologies for not explaining it properly.

[Doc.AElstein]

You is welcome
Thanks for the feedback
Alan

EDIT: Thanks for the Rep.....
- Remember " take " " or " write in " the Value, not " Paste " the Value