I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim

Manual

Enter -1 in a empty cell
Copy this cell

Select your cells and right click on them
Choose Paste Special..Multiply
OK

You can do this with VBA also

--
Regards Ron De Bruin
http://www.rondebruin.nl



"JimMay" wrote in message news:H%Ggg.40308$fG3.11689@dukeread09...
I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim

Ron,
Yes -- it is the VBA that I need to do it in;
I tried to record a macro using the manual method
But it failed.
Appreciate any help you could provide.
Jim

"Ron de Bruin" wrote in message
:

Manual

Enter -1 in a empty cell
Copy this cell

Select your cells and right click on them
Choose Paste Special..Multiply
OK

You can do this with VBA also

--
Regards Ron De Bruin
http://www.rondebruin.nl



"JimMay" wrote in message news:H%Ggg.40308$fG3.11689@dukeread09...

I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim

Hi Jim

I use F1 in this example

Range("F1").Value = -1
Range("F1").Copy

Lrow = Range("E" & Rows.Count).End(xlUp).Row
Range("E6:E" & Lrow).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("F1").Value = ""



--
Regards Ron De Bruin
http://www.rondebruin.nl



"JimMay" wrote in message news:cuHgg.40311$fG3.14888@dukeread09...
Ron,
Yes -- it is the VBA that I need to do it in;
I tried to record a macro using the manual method
But it failed.
Appreciate any help you could provide.
Jim

"Ron de Bruin" wrote in message :

Manual

Enter -1 in a empty cell
Copy this cell

Select your cells and right click on them
Choose Paste Special..Multiply
OK

You can do this with VBA also

--
Regards Ron De Bruin
http://www.rondebruin.nl



"JimMay" wrote in message news:H%Ggg.40308$fG3.11689@dukeread09...

I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim

Option Explicit
Sub testme()

Dim LRow As Long
Dim tempCell As Range
Dim myRng As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, "E").End(xlUp).Row
Set myRng = .Range("e6:e" & LRow)

Set tempCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
tempCell.Value = -1

tempCell.Copy
myRng.PasteSpecial Paste:=xlPasteAll, _
operation:=xlPasteSpecialOperationMultiply, _
skipblanks:=False, Transpose:=False

tempCell.ClearContents
End With

End Sub


JimMay wrote:

Ron,
Yes -- it is the VBA that I need to do it in;
I tried to record a macro using the manual method
But it failed.
Appreciate any help you could provide.
Jim

"Ron de Bruin" wrote in message
:

Manual

Enter -1 in a empty cell
Copy this cell

Select your cells and right click on them
Choose Paste Special..Multiply
OK

You can do this with VBA also

--
Regards Ron De Bruin
http://www.rondebruin.nl



"JimMay" wrote in message news:H%Ggg.40308$fG3.11689@dukeread09...

I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim



--

Dave Peterson

Hi Jim,

Just another way to skin the proverbial cat:
:
'=============
Public Sub Tester()
Const sFirstCell As String = "E6"

On Error Resume Next
With ActiveSheet.Range(sFirstCell)
.Resize(Rows.Count - .Row + 1). _
SpecialCells(xlConstants, xlNumbers). _
Replace What:="-", Replacement:=""
End With
On Error GoTo 0

End Sub
'<<=============

---
Regards,
Norman

"JimMay" wrote in message
news:H%Ggg.40308$fG3.11689@dukeread09...
I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim

Here's something no quite as cryptic:

Dim rng as Range, cell as Range
Set rng = Range("E6:E" & Lrow)

For Each cell in rng
cell = cell * -1
Next

JimMay wrote:
I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim

Hi Steve,

Here's something no quite as cryptic

It is not clear whether it was one, or all three, of the suggested solutions
which engendered your pejorative description, but none of these strikes me
as cryptic and all avoid the need to loop.

Incidentally, your code would also need to assign a value to the Lrow
variable.

---
Regards,
Norman


"SteveM" wrote in message
oups.com...
Here's something no quite as cryptic:

Dim rng as Range, cell as Range
Set rng = Range("E6:E" & Lrow)

For Each cell in rng
cell = cell * -1
Next

Nothing perjorative intended. You guys are no doubt better at VBA than
I. Let's say more compact then. Are loops a bad thing?

And I assumed he had declared all of the variables in the code he
submitted.

Regards,

SteveM


Norman Jones wrote:
Hi Steve,

Here's something no quite as cryptic

It is not clear whether it was one, or all three, of the suggested solutions
which engendered your pejorative description, but none of these strikes me
as cryptic and all avoid the need to loop.

Incidentally, your code would also need to assign a value to the Lrow
variable.

---
Regards,
Norman


"SteveM" wrote in message
oups.com...
Here's something no quite as cryptic:

Dim rng as Range, cell as Range
Set rng = Range("E6:E" & Lrow)

For Each cell in rng
cell = cell * -1
Next

Hi Steve,

Let's say more compact then. Are loops a bad thing?

Per se, certainly not. However, non-looping methods will often be faster
than the looping equivalent.

And I assumed he had declared all of the variables in the code he
submitted.

The undeclared variable was only mentioned to alert the OP of the need and,
implicitly, to address the issue of compactness.


---
Regards,
Norman

I'm surprised noone suggested using the absolute value function, Abs.

If it is possible (Jim did not specifiy how the cell values are set
initially) set the cell value to Abs("cell value"). This way, there would be
no need to go back again and convert them to their positive counterparts.

If VBA code is required to convert them, then the Abs function is an
alternative to multiplying by -1.

Boog
"JimMay" wrote:

I need (In VBA code) to convert a varying range of values,
Say Range("E6:E" & Lrow) from:
-123
-234
-345
-456

To:

123
234
345
456

But can't quite get it going here;
Can someone show me how?

TIA,

Jim