Visual Basic Excel Macro Q

[Taem]

What I want to do is so easy, it's downright silly, yet I cannot figure out how to do it...

Does anybody here know how to make an If-Then statement which basically says:

If cell A1 = "word" then B1 = "message"

All my attempts have failed. I've tried .select, .range, activecell, varient, everything for the darn program to acknowledge cell A1 as a text based string, and once the code goes off without any debug problems... it still does not work. Even if it somehow sees A1 the way it's suppose too, it does not seem to recognize "word" as the string to compare it too. In Excel, the code would be so simple, in Cell B1:"=IF(A1="word","message","")", but I don't know what I'm doing wrong in VB.

EDIT: Whee look at me, I'm an idiot... I forgot to Dim my values as Range and I was wondering why they wouldn't work when I tried to set them. Never-mind; nothing to see here.

[kandrathe]

EDIT: Whee look at me, I'm an idiot... I forgot to Dim my values as Range and I was wondering why they wouldn't work when I tried to set them. Never-mind; nothing to see here.

I often learn the most trying to explain things to others.

[Taem]

EDIT: Whee look at me, I'm an idiot... I forgot to Dim my values as Range and I was wondering why they wouldn't work when I tried to set them. Never-mind; nothing to see here.

I often learn the most trying to explain things to others.

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!

[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.