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.

 

[mohadin]

Hi
And welcome
Try

Sub test()
    Dim a
    Dim i&
    a = Range(Cells(9, 11), Cells(9, 11).End(xlDown)).Resize(, 2)
    For i = 1 To UBound(a)
        a(i, 1) = a(i, 1) & a(i, 2)
    Next
    Cells(9, 13).Resize(UBound(a)) = a
End Sub

Or

Sub test()
    Dim a
    Dim i&
    a = Range(Range("K9"), Range("K9").End(xlDown)).Resize(, 2)
    For i = 1 To UBound(a)
        a(i, 1) = a(i, 1) & a(i, 2)
    Next
    Range("M9").Resize(UBound(a)) = a
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

performing a calculation that is already set up

Can you tell us more about this "calculation" that is to be performed with each pair of values?

 

[Danboy]

Mohadin,

Thank you for your quick response.
I tried running your code and it runs without problems, but do not perform the task I need it to.
First, I have to clarify that all the values I need, are included in the range K9:L25.
Then, I need to have two variables. One variable to catch the values in the range K9:K25 and one variable to catch the values in the range L9:L25.
If the loop is catching K9 as 'a', at same time I need it to catch L9 as b.
Then I need to perform a specific calculation with a and b, then put the result as c back to M9:M25.

Peter_SSs

Of course, I can give you all the info you need.

My project is on the final phase.
I have to manually enter TempC and Dens in respective Textboxes, which respectively update my spreadsheet.
Then, according to another selection (when the condition is met), I will use the value in K and L to perform a calculation as shown below, and inserting the result in the spreadsheet.
For each couple of values from Range K9:K25 and Range L9:L25, I will run the following code:

QUOTE

Option Explicit
Public VCF54A As Double
Public API, TempF, Dens, TempC, a, b, a1, a2, a3, a4 As Double

Public Sub CalculateVCF54A()

Dens = 989.9
TempC = 62#

        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

End Sub

UNQUOTE

I used to use formulas for doing these calculations, but I have found that on many occasions, either by accident or curiosity, users have been tampered with the formulas, making the spreadsheet being unreliable, therefore I decided doing it on VBA, avoiding to have the same result.

Let me know if I can provide any further information

I appreciate the help

Angelos

 

[Danboy]

Of course, I forgot to mention that a and b from the ranges will be the input for Dens and TempC and the outcome VCF54A will be the c

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags (not quote tags). My signature block below has more details. I have added the tags for you this time.

and the outcome VCF54A will be the c

There is no c

I'm also confused about the calculations for API and TempF that you have not mentioned putting anywhere.
And also variables a1, a2 and a3 the results of which don't seem to be used anywhere.

Anyway, leaving your code alone as much as possible, this should go through your rows 9:25 ranges and use the col K & L values to calculate VCF54A and put that in column M as requested.

Public Sub CalculateVCF54A_v2()
  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)
    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)
    Range("M" & r).Value = VCF54A
    API = (141.5 / ((((Dens / 1000) + 0.0000383 - 0.6112) / 0.4641) * 0.0007 + (Dens / 1000) + 0.0000383)) - 131.5
    TempF = (TempC * 1.8) + 32
  Next r
End Sub

 

[Danboy]

When posting vba code in the forum, please use the available code tags (not quote tags). My signature block below has more details. I have added the tags for you this time.


There is no c

I'm also confused about the calculations for API and TempF that you have not mentioned putting anywhere.
And also variables a1, a2 and a3 the results of which don't seem to be used anywhere.

Anyway, leaving your code alone as much as possible, this should go through your rows 9:25 ranges and use the col K & L values to calculate VCF54A and put that in column M as requested.

Public Sub CalculateVCF54A_v2()
  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)
    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)
    Range("M" & r).Value = VCF54A
    API = (141.5 / ((((Dens / 1000) + 0.0000383 - 0.6112) / 0.4641) * 0.0007 + (Dens / 1000) + 0.0000383)) - 131.5
    TempF = (TempC * 1.8) + 32
  Next r
End Sub

You're right
I confused you because when I started my response to Mohadin where I referred to a, b and c as example of the values I wanted to use, I had no intention of presenting the rest of the calculation, but then your question about the "rest of the calculation" came in, that already contains it's own a and b along other values too and everything got confusing.
I apologize for trying to mix two responses onto one.

So, in order to be clear about it, I will say it once again from the begining.
The two ranges, contain information about temperature (K9:K25) and density (L9:L25).
What I need is to create a loop, an array or anything else that would help me to catch each value_temp from first range along with its corresponding value_dens from second, use them as the input for TempC and Dens in my calculation, then run my macro for getting the result VCF54A and put that result in the respective range M9:M25, before proceeding to the next pair of value_temp & value_dens.

Thank you for cleaning up my mess with the quote too.
Angelos

 

[Peter_SSs]

a and b from the ranges will be the input for Dens and TempC

value_temp from first range along with its corresponding value_dens from second

Now I'm confused about which is Temp & which is Dens.

Did you try the code that I suggested?

 

[mohadin]

Sub test()
    Dim a
    Dim i&
    a = Range(Cells(9, 11), Cells(9, 11).End(xlDown)).Resize(, 2)
    For i = 1 To UBound(a)
        'a(i,1)=value temp
        'a(i,2=value dens

        ' TempC = 62#
        TempC = a(i, 1)

        ' Dens = 989.9
        Dens = a(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
        a(i, 1) = VCF54A
    Next
    Cells(9, 13).Resize(UBound(a)) = a
End Sub

 

[Peter_SSs]

Did you try the code that I suggested?

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