Parallel(?) Loop in a Range or Array

[Danboy]

Hi.
I have a worksheet containing various values and I am trying to make a sub that would loop through two specific columns at the same time, take the two adjacent values, execute a calculation within the VBA and then, put the result back in a third column. My data is on column K and column L, from rows 9 to 25. Each of the K column value, should join the corresponding value of column L as a pair and the result should go in column M.
For example, K9 is a pair with L9, K10 with L10 and so on. These "pairs" should be entered in the module as temporary entry values for performing a calculation that is already set up, then the result should be going on the respective row, on column M. Can someone help me on setting this up? I have no experience on Loops and Arrays up to now and I think that's the only way to do that. I need to find how to loop and get the values of two columns, how to refer to those valus from the module and how to send the result back to the third column. Thanks in advance and sorry if I am not too clear, It's my first post ever asking for guidance.

 

[Danboy]

Sub test()
    Dim [COLOR=rgb(184, 49, 47)]delta[/COLOR]
    Dim i&
    [COLOR=rgb(184, 49, 47)]delta [/COLOR]= Range(Cells(9, 11), Cells(9, 11).End(xlDown)).Resize(, 2)
    For i = 1 To UBound([COLOR=rgb(184, 49, 47)]delta[/COLOR])
        'a(i,1)=value temp
        'a(i,2=value dens

        ' TempC = 62#
        TempC = [COLOR=rgb(184, 49, 47)]delta[/COLOR](i, 1)

        ' Dens = 989.9
        Dens = [COLOR=rgb(184, 49, 47)]delta[/COLOR](i, 2)

        a = Round(613.9723 / Dens ^ 2, 9)
        a1 = 1
        a2 = 0.8
        a3 = Round(a, 9)
        a4 = TempC - 15

        b = 1 + 0.8 * a * (TempC - 15)
        VCF54A = Round(Exp(a * b * a4 * -1), 5)

        API = (141.5 / ((((Dens / 1000) + 0.0000383 - 0.6112) / 0.4641) * 0.0007 + (Dens / 1000) + 0.0000383)) - 131.5
        TempF = (TempC * 1.8) + 32
[/QUOTE]
[QUOTE="mohadin, post: 5915626, member: 320689"]

        [COLOR=rgb(184, 49, 47)]delta[/COLOR](i, 1) = VCF54A
    Next
    Cells(9, 13).Resize(UBound([COLOR=rgb(184, 49, 47)]delta[/COLOR])) = [COLOR=rgb(184, 49, 47)]delta[/COLOR]
End Sub

Mohadin, your solution works perfectly in my module. I only had to substitute your 'a' variable for another (I named it 'delta') since an 'a' already existed in the rest of the code.
After doing that, it worked nicely.

Here is a 'cleaned-up' version of that code

Public Sub CalculateVCF54A_v3()
  Dim r As Long
 
  For r = 9 To 25
    Dens = Range("K" & r).Value
    TempC = Range("L" & r).Value
    a = Round(613.9723 / Dens ^ 2, 9)
    b = 1 + 0.8 * a * (TempC - 15)
    Range("M" & r).Value = Round(Exp(a * b * (TempC - 15) * -1), 5)
  Next r
End Sub

If I have the K & L columns the wrong way around then just swap K & L in the two blue lines

Peter_SSs, your solution also worked like a charm. The first time, it gave the result of 0 to all the "M" column range but then I remembered the comment on your last line. I saw it was named the other way around and when I altered that, everything worked fine. I just substituted "Round(Exp(a * b * (TempC - 15) * -1), 5)" with its equivalent, VCF54A, since it's easier to recognize it in any future review of the code.

I don't know how can I express my gratitude and thanks to both of you.
I really am obliged for the assistance provided.
I wish all the best to you
Regards
Angelos

 

[Peter_SSs]

You're welcome. Glad we were able to help. Thanks for the follow-up.

 

[mohadin]

You're welcome form me as well
And thank you for the feedback
Be happy and safe