Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    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 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. #2
    Uranium Lounger
    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    5 Star Lounger
    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)

Posting Permissions

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