[-VBA-] Absolute value, how to get it in this loop

[montecarlo2012]

Hello, I hope a good weekend for all
I am working on something that really need a loop and also I am looking how to get the ABSOLUTE value here, this is what I have been done already

Sub s()
[H2:H8923] = [B3:B8923 -H1]
[I2:I8923] = [C3:C8923 -H1]
[J2:J8923] = [D3:D8923 -H1]
[K2:K8923] = [E3:E8923 -H1]
[L2:L8923] = [F3:F8923 -H1]

[N2:N8923] = [B3:B8923 -N1]
[O2:O8923] = [C3:C8923 -N1]
[P2:P8923] = [D3:D8923 -N1]
[Q2:Q8923] = [E3:E8923 -N1]
[R2:R8923] = [F3:F8923 -N1]

[T2:T8923] = [B3:B8923 -T1]
[U2:U8923] = [C3:C8923 -T1]
[V2:V8923] = [D3:D8923 -T1]
[W2:W8923] = [V3:E8923 -T1]
[X2:X8923] = [F3:F8923 -T1]

[Z2:Z8923] = [B3:B8923 -Z1]
[AA2:AA8923] = [C3:C8923 -Z1]
[AB2:AB8923] = [D3:D8923 -Z1]
[AC2:AC8923] = [V3:E8923 -Z1]
[AD2:AD8923] = [F3:F8923 -Z1]

[AF2:AF8923] = [B3:B8923 -AF1]
[AG2:AG8923] = [C3:C8923 -AF1]
[AH2:AH8923] = [D3:D8923 -AF1]
[AI2:AI8923] = [V3:E8923 -AF1]
[AJ2:AJ8923] = [F3:F8923 -AF1]
End Sub

but still I have more arrays to work on it
AL TO AP and the cell AL1 etc.
AR TO AV
AZ TO BB
AND BD TO BH
thanks.

 

[jasonb75]

See if this does what you need,

Sub test()
Dim rCol As Long
For rCol = 8 To 56 Step 6
    Cells(2, rCol).Resize(8922, 5).Value = Evaluate("ABS(" & Range("B3:F8923").Address & "-" & Cells(1, rCol).Address & ")")
Next
End Sub

 

[montecarlo2012]

Hi, Sir. jasonb75.
Your code run but don't give me the right results.
Thank you for your time.

 

[Rick Rothstein]

Can you explain what this line of code is supposed to be doing...

[H2:H8923] = [B3:B8923 -H1]

It looks like you might be trying to subtract H1 from each cell in the range B3:B8923, taking the absolute value of that subtraction and then assigning each generated value to the range H2:H8923. If that is what you are trying to do, I would point out that the range H2:H8923 has one more cell in it than the range B3:B8923 has. So which cell in H2:H8923 should be left blank?

 

[montecarlo2012]

Rick Rothstein, the great. Thank you so much for reading my post.
well, I workout a different approach.
A code is worth a thousand words:

Sub RR()
Dim Rw As Long, Cl As Long


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    For Rw = 2 To Cells(Rows.Count, "B").End(xlUp).Row
           For Cl = 2 To 6
         
                   Cells(Rw, 6 + Cl) = Abs(Cells(Rw, Cl) + Range("H1"))
                   Cells(Rw, 12 + Cl) = Abs(Cells(Rw, Cl) + Range("N1"))
                  Cells(Rw, 18 + Cl) = Abs(Cells(Rw, Cl) + Range("T1"))
                  Cells(Rw, 24 + Cl) = Abs(Cells(Rw, Cl) + Range("Z1"))
                  Cells(Rw, 30 + Cl) = Abs(Cells(Rw, Cl) + Range("AF1"))
                  Cells(Rw, 36 + Cl) = Abs(Cells(Rw, Cl) + Range("AL1"))
                  Cells(Rw, 42 + Cl) = Abs(Cells(Rw, Cl) + Range("AR1"))
                  Cells(Rw, 48 + Cl) = Abs(Cells(Rw, Cl) + Range("AX1"))
                  Cells(Rw, 54 + Cl) = Abs(Cells(Rw, Cl) + Range("BD1"))  
   
         Next Cl
      
    Next Rw
    
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

This basic code take to long.
Thanks.

 

[montecarlo2012]

And with the notation
[H2:H8923] = [B3:B8923 -H1]
(I learned from you in another post,) I couldn't found how to used with ABS.
Anyway, I am looking to resolve this with a code.
thanks.

 

[montecarlo2012]

Can you explain what this line of code is supposed to be doing...

[H2:H8923] = [B3:B8923 -H1]

It looks like you might be trying to subtract H1 from each cell in the range B3:B8923, taking the absolute value of that subtraction and then assigning each generated value to the range H2:H8923. If that is what you are trying to do, I would point out that the range H2:H8923 has one more cell in it than the range B3:B8923 has. So which cell in H2:H8923 should be left blank?

I write a code trying to explain. thanks