VBA Randomizing Function to change cell formula using variables, named ranges, and indirect functions

I've been searching and searching and can't find a proper way to do this. I'm using multiple variables and values to set the value of another cell.

I use this formula to accomplish what I need right now in cell "E4" on Sheet "Turn_Travel";

=LOOKUP(RAND(),INDEX(INDIRECT("Region"&VLOOKUP($B$4,RegionListTable,MATCH("Region #",RegionListHeader,0),FALSE)&$C$4&"Info"),0,MATCH("Perct.",INDIRECT($C$4&"Header"),0)),INDEX(INDIRECT("Region"&VLOOKUP($B$4,RegionListTable,MATCH("Region #",RegionListHeader,0),FALSE)&$C$4&"Info"),0,MATCH("Weather",INDIRECT($C$4&"Header"),0)))

Right now I just hit F9 and it refreshes my worksheet and generates a new Random number which changes my result.

I'm trying to use VBA to generate the Random number and then have the cell "E4" on Sheet "Turn_Travel" contain the above formula with the VBA variable for the Random number.

This was my attempt but I'm getting an error. Can someone spot where I'm going wrong?

Sub TravelRoll()

Randomize
Dim TheRoll As Long
TheRoll = ((1 - 0 + 1) * Rnd + 0)
Worksheets("Turn_Travel").Range("E4").Formula = "=LOOKUP("&TheRoll&",INDEX(INDIRECT(""Region""&VLOOKUP($B$4,RegionListTable,MATCH(""Region #"",RegionListHeader,0),FALSE)&$C$4&""Info""),0,MATCH(""Perct."",INDIRECT($C$4&""Header""),0)),INDEX(INDIRECT(""Region""&VLOOKUP($B$4,RegionListTable,MATCH(""Region #"",RegionListHeader,0),FALSE)&$C$4&""Info""),0,MATCH(""Weather"",INDIRECT($C$4&""Header""),0)))"
End Sub

Thanks.