Proper code

[JimMay]

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 de Bruin]

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

 

[JimMay]

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]

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 = ""

 

[Dave Peterson]

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

 

[Norman Jones]

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
'<<=============

 

[SteveM]

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

 

[Norman Jones]

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.

 

[SteveM]

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]

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.

 

[Guest]

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