# Thread: Complex Calculations in Cell (ExcelXP)

1. ## Complex Calculations in Cell (ExcelXP)

I have a sheet with some 50 rows. In columns A and B there is some text. I want to combine this text in (e.g.) column P.

The problem is that column P is formed based on what's in A and B. To figure that out, I need some VBA-code (which I have).

So basically what I need in cell in col P is a formula that refers to this VBA Function (and then the VBA function, figures out the row where the active cell is and then gets to the A and B colums).

So something like (in e.g. Row 10):

Col A: ABCDE
Col B: FGHIJ

Col P: =MAKECODE()

Then Col P should have something like (e.g.): AAFFJJEE (of course depending on the VBA code)

Of course the result in Col P should depend on the values in cols A and B IN EACH ROW.

I think I need "Application Volatile" or something like that (maybe even a 'volatizer' in the formula like =MAKECODE() & REPT(" ", 0*NOW()) )

Cannot get this to work however... any suggestions?

Erik Jan

2. ## Re: Complex Calculations in Cell (ExcelXP)

Pass the cells as parameters to the function. In cell P1, you would put something like:

=MAKECODE(A1,B1)

Then use the parameters in the function.

3. ## Re: Complex Calculations in Cell (ExcelXP)

You should create a function with two arguments:

Function MakeCode(Val1, Val2)

and calculate the return value from the values of Val1 and Val2.

The formula in P10 would become

=MakeCode(A10,B10)

The result will be updated automatically when A10 or B10 changes.

You can fill down or copy this formula to other cells in column P, it will adapt itself automatically.

4. ## Re: Complex Calculations in Cell (ExcelXP)

Got it.... thanks (it's sooo simple)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•