Unable to record error, long formula macro problems

[Jerhansen277]

I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.

Upon executing the formula I then want it to copy and paste special- values the result.

I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.

The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.

Thank You for any help or suggestions

[shg]

The macro recorder can't record entering formulas longer than ... I forget how many characters, maybe 255. Could change the formula:

=RANDBETWEEN(1, VLOOKUP($J$1,'Monster Manual'!$A$4:$AZ$304, INDEX({18,26,34,42,50}, MATCH($I$10, {"melee","ranged","special1","special2","special3"}, 0),FALSE) ) )

[Jerhansen277]

Wow, that's incredibly simple and worked great. That will make doing similar formulas so much easier with quicker editing. Thanks!

[Jerhansen277]

Well I spoke too soon.

It works for the first case "melee" but for the others it doesn't for example kobold, ranged. I checked the idex numbers, and made sure there is data.

I get a #Ref! error but I can't see anything wrong with the formula. any ideas?

Here is what the macro looks like in VB

Range("J12").Select
ActiveCell.FormulaR1C1 = _
"=RANDBETWEEN(1, VLOOKUP(R1C10,'Monster Manual'!R4C1:R304C52, INDEX({18,26,34,42,50}, MATCH(R10C9, {""melee"",""ranged"",""special1"",""special2"",""special3""}, 0),FALSE) ) )"
Range("J12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[shg]

Skip VBA for the moment, put the formula in the cell, and watch it evaluate using the Formula Auditing toolbar.

[Jerhansen277]

OK it's having problems at the index formula.

I've pulled that part out

INDEX({18,26,34,42,50}, MATCH($I$10, {"melee","ranged","special1","special2","special3"}, 0),FALSE)

I can't see anything that would cause it to work in the first case but the not the second, third etc?

[shg]

Me either. Type ranged into I10, no extra spaces, ...

[Jerhansen277]

Did that same result.

this is truly confusing, as there doesn't seem to be any difference between column 18 & 26, etc

[shg]

There's a misplaced paren in that formula; it should be

=RANDBETWEEN(1, VLOOKUP($J$1,'Monster Manual'!$A$4:$AZ$304, INDEX({18,26,34,42,50}, MATCH($I$10, {"melee","ranged","special1","special2","special3"}, 0) ),FALSE) )

[Jerhansen277]

Sweet! That is working great. I've tested it multiple times with different variables and no problems.


Thanks for your help and your time.