Visual Basic Excel Macro Q

[Maitre]

I'm getting so frustrated because my program has 7 outputs based on a random selection of cells containing my data - I wrote a function they all use to make this determination - and 6 of the outputs work without any issues, however one of the outputs says #VALUE! about 20% of the time and I am 100% sure it has the correct cells to look-up a random value for! I'm also sure its outputting all of the random cells correctly. And I'm also sure I set the Cell properties appropriately so half aren't classified as numerical and the others as text. So... I'm trying to figure out how to make VB display the current Cell of my Dim TRAP, but all it will give me is TRAP's value, not it's cell location. And I can't find any information on how to get a values cell location anywhere. I feel like I'm working backwards... once I figure out how to extract the values cell location, then I can see where TRAP is pointing too when giving me the #VALUE! error, and then I can correct it, assuming it is an error with a cell and not the program itself. But it can't be the program because all the outputs use the same function without issue. Fun stuff. I suppose I could just use the program as is and "re-roll" the results when they come up with error, but that just looks sloppy.

Lets see, I tried "MsgBox mc.Addressof("TRAP")", "Application.Goto Reference:=Worksheets("Sheet1").Range("TRAP")", but those don't work. They want an actual cell reference, but so do I, lol!

It seems like what you want is that .address property of .range: I think you can write this to a varriable using v=sheets().range().address
I can't remember what the format is (r1c1 vs a1) but you should be able to get that from the helpfile for the .address property. So you may be able to just update to "Application.Goto Reference:=Worksheets("Sheet1").Range("TRAP").Address" and see the reults you want.

The #value! error tends to come up when excel get a data type it doesn't expect like a formula that wants to multiply by a cell value and the value is text instead of a number. I often find the =value() function to be helpful if I'm creating a value, or extracting a value from a text string (like pulling the number of wins from a cell that displays a teams win loss record I would use =value(left(c10,find("-"))) to pull the value before the dash in a cell containing 20-19. Using =value() ensures that the text will convert to a number rather than remaining a text value. Alternatively, depending on how you've structured your random draw, the #value! error can come up because Excel can't evaluate the distribution properly: for example, Excel can't handle the =gammainv() function for distributions with a very small ratio of SD to mean. without knowing more about what you're trying to do, I don't think I can offer any more ideas, but hopefully you'll be able to troubleshoot your file with or without my ideas.

Best of luck.