Results 1 to 4 of 4

20060315, 14:18 #1
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 VBAcode (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

20060315, 14:25 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Legare Coleman

20060315, 14:28 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20060315, 14:32 #4
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complex Calculations in Cell (ExcelXP)
Got it.... thanks (it's sooo simple)