Is this possible with an Array?

[jonathann3891]

Hey everybody!

Let me explain what I'm trying to do;
I have a column on a hidden sheet (import) that contains a number, either 50 or 75. That number represents the length of an object that is defined by their length as a type, 50 = 1 and 75 = 2.

The column range on the hidden sheet starts at E1, but can vary in length depending on how many items where in the text file that was imported, but the range on sheet 1 through sheet 15 is fixed (D3:D62).

I have a formula that works

Please Login or Register  to view this content.

but I would like to accomplish this task with VBA to save time.
Esp if the lengths change, I dont want to have to go through every sheet and update the formula.

I looked into array's but I didnt see anything similar to this in the examples I found.

How do I accomplish this task?

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[thatandyward]

could you use a helper cell for the length values you want to test against? this way your formulas can reference the helper cell rather than have the length hard-coded into them. this would allow you to adjust the lengths in the helper cell and all the formulas still be correct.

assuming your helper cell was in H1 on the import sheet your formula could be =IF(Import!$E1=Import!$H$1,1,2).

with respect to updating the formulas for cells D3:D62 on sheets 1 thru 15 you can actually edit the same cell(s) on multiple sheets at the same time. if you shift-select sheets 1 > 15 any input/changes you make on sheet 1 will be replicated on all the other sheets. so you can write/paste the formula into D3, then drag it down 60 rows and those edits will also have also been done on sheets 2 > 14. just remember to deselect the multiple sheets (by clicking an alternate sheet) once your done. it's very easy to forget you have multiple sheets selected and make other changes which overwrites data on the other sheets!

if you did want to take the VBA approach then something like the below should work

Please Login or Register  to view this content.

essentially looping through sheets 1 > 15 and writing the formula into the desired cells. by using R1C1 notation you can set the formulas for all cells in one hit.