Parallel(?) Loop in a Range or Array

Danboy

New Member
Joined
Aug 14, 2013
Messages
5
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi
And welcome
Try
VBA Code:
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
VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
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

VBA Code:
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
 
Last edited by a moderator:
Upvote 0
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
 
Upvote 0
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
:confused: 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.

VBA Code:
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
 
Upvote 0
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.


:confused: 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.

VBA Code:
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
 
Upvote 0
VBA Code:
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
 
Upvote 0
Did you try the code that I suggested?
Here is a 'cleaned-up' version of that code

Rich (BB 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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top