VBA to calculate corrosion defects in subsea pipelines

**Affan Khan** · 11-12-2012, 01:00 PM

Hi all,

I am a junior Mechanical Engineer with almost no programming experience. I did some MATLAB a few years ago. I have been given the task of writing a VBA Program in Microsoft Excel to calculate corrosion defects in subsea pipelines. The mathematical calculations themselvs are quite simple however we often have to analyse tens of thousands of data points and this is where excel comes in.

I believe that I am having trouble grasping the "if" function. I understand the concept myself but it is proving difficult to get excel to do what i want it to do. There is a mathematical formula in the ASME standards that I am using known as the "Z Criteria". And if the "Z value" is greater than 50then a certain equation is used and if the Z criteria is less than 50 another equation is used in the calculation. I have copy/pasted the relevant section of the code below.

Please Login or Register  to view this content.

The intention was that if the calculated "Z-Value" is less than 50 then we use equation 1 and if it is less than 50 then we use equation 2. However when I try to execute this code then I get an error called "Next without for"? I checked all of the "For" definitions and I found that they were all defined. Then I proceeded to check all of the (i,j) definitions for the array sizes and they seem to be ok (as far as I can tell)

Does anyone have any ideas on how to solve this problem. Any help is much appreciated.

I tried to look at the error in the debugging window and the equation that was highligted was due to an incorrect evaluation of the Z criteria and so I think my "if function" could be the culprit.

Thank you very much for your help,

Affan

**Bishonen** · 11-12-2012, 01:03 PM

you have to include

Please Login or Register  to view this content.

at the end of the if statement.

For example:

Please Login or Register  to view this content.

so your case:

Please Login or Register  to view this content.

**Affan Khan** · 11-12-2012, 01:06 PM

Please Login or Register  to view this content.

I forgot to add this bit of code. This is just to show that I closed the "if" loop and put the End If Function (or so it seems to me).

I have been doing the same thing over and over again and I am not sure what new strategy I can use to solve this problem. Any help is appreciated.

**haripopuri** · 11-12-2012, 01:07 PM

Originally Posted by Affan Khan

'if function for z criteria

ReDim Marray(1 To RowCount, 1 To ColCount)
For i = 1 To RowCount
For j = 1 To ColCount
If Zarray(i, j) <= 50 Then
Marray(i, j) = Marray1(i, j)
Next j
Next i

ReDim Marray(1 To RowCount, 1 To ColCount)
For i = 1 To RowCount
For j = 1 To ColCount
ElseIf Zarray(i, j) > 50 Then
Marray(i, j) = Marray2(i, j)
Next j
Next i

This should be

Please Login or Register  to view this content.

**zbor** · 11-12-2012, 01:07 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**zbor** · 11-12-2012, 01:07 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**abousetta** · 11-12-2012, 01:16 PM

Hi,

If I understood the problem correctly then this might help:

Please Login or Register  to view this content.

abousetta

**zbor** · 11-12-2012, 01:19 PM

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

**abousetta** · 11-12-2012, 01:24 PM

Sorry zbor, didn't see your post. We must have posted at the same time.

**zbor** · 11-13-2012, 08:59 AM

Affan Khan , you should also do that in 3rd post (actually code tags are obligated in every post you write).
But I did it for you now.

Please check now abousetta solution in post #7