VBA Abs - Absolute

[Trying2learnVBA]

Hello,

I am unable to figure out a way to convert a range to absolutes using VBA.

This is the code I am trying:

Sub Convert_Range_to_Absolute()

End Sub

Dim Ws As Worksheet
Dim LastRow As Long
Dim iValue As Integer
Dim vResult As Variant
iValue = .Range("D:D" & LastRow)
vResult = Abs(iValue)

Set Ws = ActiveWorkbook.Sheets("Sheet1")

.Range("J:J" & LastRow).Value = vResult

End With

End Sub

Does anybody know of a method that works? Am I far off?

I would like to convert range D:D and last row to Abs.

Thank you!!!

 

[kevin9999]

Try this (assumes your data starts in row 2 - let me know if it doesn't)

Option Explicit
Sub Trying2learnVBA()
    Dim ws As Worksheet, lRow As Long
    Set ws = Worksheets("Sheet1")
    lRow = ws.Cells(Rows.Count, "D").End(xlUp).Row
    
    ws.Range("J2:J" & lRow).Value = ws.Evaluate("ABS(D2:D" & lRow & ")")

End Sub

 

[davesexcel]

Sub UseABS()
    Dim r$: r = Range("D2", Range("D" & Rows.Count).End(xlUp)).Address
    Range(r) = Evaluate("=abs(" & r & ")")
End Sub

 

[Trying2learnVBA]

Try this (assumes your data starts in row 2 - let me know if it doesn't)

Option Explicit
Sub Trying2learnVBA()
    Dim ws As Worksheet, lRow As Long
    Set ws = Worksheets("Sheet1")
    lRow = ws.Cells(Rows.Count, "D").End(xlUp).Row
   
    ws.Range("J2:J" & lRow).Value = ws.Evaluate("ABS(D2:D" & lRow & ")")

End Sub

This works. Thank you!

 

[Trying2learnVBA]

Sub UseABS()
    Dim r$: r = Range("D2", Range("D" & Rows.Count).End(xlUp)).Address
    Range(r) = Evaluate("=abs(" & r & ")")
End Sub

I tried this as well, this did not work for me. I am not clear as to how this code tells excel to put the result in column J.

 

[kevin9999]

This works. Thank you!

You're welcome, and thanks for the feedback.

 

[davesexcel]

I tried this as well, this did not work for me. I am not clear as to how this code tells excel to put the result in column J.

I would like to convert range D:D and last row to Abs.

Is what you asked for.

 

[Trying2learnVBA]

Is what you asked for.

Oh your code does work - it converted D:D to absolutes. Pretty cool. New trick for me. Now I'll play around with my report to see if I can use this method without the abs value going to col. J.