# How do I lookup values in a column, then change an equation in another column from array to normal?

#### KuhnerMK

##### New Member
I have a very complex spread sheet that takes different kinds of steel (plate, wide flange, pipe, angle, etc...) and calculates the weight. Given the various types of steel I want to focus on just plate (PL) and Angle to help simplify my equation. I am not well versed in the language for VBA, but I am capable of manipulating existing code to do what I need. Below is a sample of my table:
 Row/Columns A B C D E 1 TYPE THK WIDTH LENGTH WEIGHT 2 PL 4.5 11.5 105.5 1550.53 3 PL .75 9.25 164 323.12 4 ANGLE .25 2+2 44 35.93 5 ANGLE .5 3+6 25 40.83

<tbody>
</tbody>

Here are some of the details of my table:
• E4 should equal 12.50
• E5 should equal 31.95
• I have C2:C5 defined as a named range as WIDTH=EVALUATE(C2:C5).
• Other Named ranges are as follows:
• TYPE=A2:A5
• THK=B2:B5
• LENGTH=D2:D5
• WEIGHT=E2:E5
• My simplified equation for each weight cell is as follows as an array formula:
• {=IF(A2="","",IF(A2="PL",B2*C2*D2*.284,IF(A2="ANGLE",B2*WIDTH*C2*D2*.284,"")))}
• I know in the above formula for weight, it would be unnecessary to use the array formula, but this equation is very simplified for the purpose of presenting my question.

Here is what I would like to solve:
1. How can I use VBA to look for all "ANLGE" in the range A2:A5 (or named range "TYPE") and change the corresponding weight formula from array to normal?
2. I am definitely open to alternative suggestions.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Replies
24
Views
981
Replies
9
Views
512
Replies
1
Views
2K
Replies
18
Views
1K
Replies
3
Views
348

1,190,559
Messages
5,981,691
Members
439,730
Latest member
gjvv

### 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.

### Which adblocker are you using?

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

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