VBA Code for scanning information in an Excel Spreadsheet

steveo0707

Board Regular
Joined
Mar 4, 2013
Messages
85
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have an Excel spreadsheet which contains data displaying the thickness(US) and the Magnetic Flux(MF) of steel piping. There are 16 separate channels of piping and each channel is 100,000 centimeters long. This info is recorded every 1 cm. I need help with a VBA code to scan all the data for concern thresholds for each. The concern threshold for (US) is 9.0 and for the (MF) it is 25.0.

The code has to check for 5 different levels.

First Level is to check to see if (US) and (MF) are in range. (US) >= to 9.0 and (MF) <= 25.0
Second Level tells the user that the (US) and (MF) are in the concern range. (US) < 9.0 and (MF) > 25.0
Third Level tells the user that there are multiple level 2 events in adjacent locations
Fourth Level tells the user that both the (US) and (MF) are in the concern range for the same channel number and location
Fifth Level is to tell the user there are multiple level four events in adjacent locations

There are 200 welds in each channel and the (MF) is > 97.99 and (US) is >12.50. The code has to not count these as False Positives for the (MF) parameters, but still needs to detect corrosion at the welds.

I have the code written up to testing for the level three conditions. Not sure how to word the IF statements for Levels 3 thru 5. And not sure how to get the VBA code to scan the info on sheet one of the workbook and display the results on sheet 2 of the same workbook.

The code I have so far is below:

Sub EvaluatePigData()
'Scan Data Worksheet for 5 levels of criteria

Dim MF Channels(16) Dim US Channels(16)
Dim MF As Integer
Dim US As Integer

'Test for level 1 conditions

If MF <= 25.0 and US >= 9.0 Then
Answer = True​
Else
MF > 25.0 and US > 12.50 Then​
Goto Weld Test

Weld Test:
If MF > 97.99 and US > 12.50 Then​
Answer = True
Else​
MF <= 97.99 and US <= 12.50 Then
GoTo Level 2

'Test for level 2 conditions

Level 2:
If MF > 25.0 and MF <= 97.99 and US < 9.0 Then​
Answer = True​
Else
Goto Level 3

' Test for level 3

Level 3:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
How is your data organised on the data sheet? Can you post a sample sheet on dropbox or so?

What do you mean with Dim MF Channels(16)? VBA won't accept that
 
Upvote 0
Sorry about the Dim MF Channels(16). I was trying to show that there were 16 channels of each that's all. I have posted an image of the first six centimeters of data below


-- removed inline image ---
 
Upvote 0
Is the data on a worksheet? If so, arranged exactly how? 32 columns x 100,000 rows?
 
Upvote 0
Is the data on a worksheet? If so, arranged exactly how? 32 columns x 100,000 rows?

Yes. I have an example below.

I can not figure out to load an image to this post and make it viewable.

It is on a Excel Worksheet. It is 32 columns by 100,000 rows.

Row 3 is my header row for each column.

Data starts in row 4. A4 is the centimeter location. (0-99,999), B4 starts the readings for MF1 --> Q4 is the readings MF 16, R4 starts the readings for US1 --> AG4 is the readings for US 16.

The last row of data is in row 100,003.
 
Upvote 0
You could also put the workbook on box.net. All 100K lines is certainly not necessary, but examples that illustrate and explain each type of event are.

I'm sure we could make a silk purse out of those sow's ears. (That's a pig joke ...)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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