Questions about funtion in VBA excel - save data in calculating - save result in a cell from another sheet - open a new sheet to save data.

  • Hello,

    I'm kind of new to VBA. I'm trying to write a function in VBA excel to match names. However, I encounter several problems which i. I hope someone could explain me.


    For example: I have 10 names (from A1 to A10), and I have a very long list of names.


    While calculating the probability of matching name, I need to save the intermediate results in VBA and pull it out later for another step. However, I don't know how to save them. I think VBA would have a range or something for me to save those temporary results. However, I cannot find any so far. Anyone has any idea about this?


    Secondly, when I failed to save intermediate results in VBA, I try to save them in cells from a specific worksheet. However, function VBA doesn't work and returns "#Value!". I think VBA doesn't let me use wide reference like that. Could anyone make me clear about this issue?


    Third, I search and see that there are many ways to create a new worksheet for Sub, but it doesn't work if I apply that code to function VBA. One thing I can do it create a sub to create a new sheet then call sub in function. But I really don't like that way. Anyone does it before or you have solution for it?


    Thank you....:saint::saint::saint: I know my post is kind of long....:)