Creating conditional calculations

**visionwindowfilms** · 12-06-2010, 05:32 AM

I have a project (spreadsheet attached). Im going to be adding lots of data with a similar theme.

In cell X there will be a number for the width and in cell Y a number for the length. On the sample sheet cell X is 508 and Y is 1.

You will then see in cells F, H, I, S, AF, AG and BK it references the number 508 and 1. I want to create a formula or function whereby I can automatically input whatevers in cell X and Y.

My initial thought is to replace 508 in F, H, I, S, AF, AG and BK with PRODUCTWIDTH and 1 in 508 in F, H, I, S, AF, AG and BK with PRODUCTLENGTH. Then make a formula that will automaticlaly change the word PRODUCTWIDTH to whats in cell X and PRODUCTLENGTH to whats in cell Y.

Whats the best way to do this?

**DonkeyOte** · 12-06-2010, 05:40 AM

As you describe... you have a few examples of this technique already in your prior threads I think, no ?

In general terms:

=SUBSTITUTE("yadda yadd PRODUCT WIDTH","PRODUCT WIDTH",X2)

**visionwindowfilms** · 12-06-2010, 09:32 AM

Just to try and explain a little more. I would need something that I would paste over the top of a cell to say substitute PRODUCTWIDTH with cell X2 and substitute PRODUCTLENGTH with cell Y2. Ive adjusted and re attached the spreadsheet to show what I mean, all the relevant areas say either PRODUCTWIDTH or PRODUCTLENGTH and I want be be able to easily change these words to whatever it says in X2 or Y2

**DonkeyOte** · 12-07-2010, 05:11 AM

Apologies for belated response - man flu has struck for the upteenth time this year.

To overwrite the original constants you would be looking at VBA, eg:

Sub Example()
    Dim vWhat As Variant, vWith As Variant
    Dim rngRow As Range
    Dim lngWhat As Long
    vWhat = Array("PRODUCTWIDTH", "PRODUCTLENGTH")
    vWith = Array("X", "Y")
    With Sheets("export_all_productsTEST")
        For Each rngRow In .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Rows
            With rngRow.EntireRow
                For lngWhat = LBound(vWhat) To UBound(vWhat) Step 1
                    .Replace vWhat(lngWhat), .Cells(1, vWith(lngWhat)), xlPart, , False
                Next lngWhat
            End With
        Next rngRow
    End With
End Sub

Caveat: I'm up to my eyeballs on meds so I've little doubt the above can be improved upon.

**visionwindowfilms** · 12-06-2010, 09:30 AM

Hi

On the previous threads I've pulled the text description from a seperate sheet, whereas all the descriptions are in there so Ill need to overwrite the actual cells now so didnt think that would work. If that makes any sense?

**visionwindowfilms** · 12-09-2010, 01:07 PM

Great, so would I paste this code over each cell

**visionwindowfilms** · 12-14-2010, 04:08 PM

Ive tried pasting this over the cells and it doesnt seem to work, am I doing something wrong?

**DonkeyOte** · 12-15-2010, 10:14 AM

The "code" is VBA - to be stored within the VBE.

Attached is your earlier sample with Code and trigger in place - click the Button to invoke the code which will update the various records per placeholders.

In this instance (updating constants) VBA is required.

note: file type is now .xlsm (macro enabled)

For info. re: macro security in XL2007 etc see: http://office.microsoft.com/en-us/he...1071.aspx#BM12