title: Evaluate Using Wrong Worksheet and Errors
body: <p>First, a little preface.</p>
  
  <p>Im still a little new to VBA. I was attempting to write code for a random number generator. 8 digit, alphanumeric. I accomplished this by having one sheet where the number would be pasted (List) and another sheet that is hidden where the number is created (Generator). On the Generator sheet in column B I put A-Z and 0-9 and then in A1 had this formula concatenated together 8 times: <code>INDEX(B:B,RANDBETWEEN(1,36))</code></p>
  
  <p>This worked very well. I then wrote a little code to handle repeats and then paste the result in the first empty cell in B:B on the List sheet. </p>
  
  <p>But I wanted to get a little more creative, and I didn't want to have to change the index part of the formula 8 times if I wanted to add lowercase or special characters later on. I wanted to be able to change it in one spot and then the rest of them follow suit. So with concatenate I had a cell which has this text as the value: CONCATENATE(INDEX(B:B,RANDBETWEEN(1,36)),etc...</p>
  
  <p>I then used <code>Evaluate</code> in VBA in order to process this as a formula. When I printed this in the immediate window it worked fine. So I ran the code. But it would not work. Finally realized that it was looking on the List sheet for <code>INDEX(B:B</code> since it is the first tab. So then I added <code>Worksheets("Generator").Activate</code> in the hopes it would look at the proper sheet. Didn't change anything.</p>
  
  <p>Then I researched a little and tried using <code>Worksheet.Evaluate()</code> thinking that would do the trick. But then I got either #VALUE! errors or type mismatch errors.</p>
  
  <p>Even still, when trying either <code>Application.Evaluate()</code> and specifying the worksheet, or <code>Worksheet.Evaluate()</code> in the immediate window before debugging, both options will work how I want them to. But when running the code, or trying the immediate window after starting debugging, the code won't run properly.</p>
  
  <p>Here is the actual code:</p>
  
  <pre><code>Dim strRandNum As String
  Dim lgLastRow As Long
  Dim intCounter As Integer
  
  lgLastRow = WorksheetFunction.CountA(Worksheets("List").Range("B:B"))
  intCounter = 0
  
  TryAgain:
  
  Worksheets("Generator").Activate
  ' Original: strRandNum = Evaluate(Worksheets("Generator").Range("C1").Value)
  strRandNum = Worksheets("Generator").Evaluate(Range("C1").Value)
  If WorksheetFunction.CountIf(Worksheets("List").Range("B:B"), strRandNum) &gt; 0 Then
      Calculate
      intCounter = intCounter + 1
      If intCounter = 5 Then
          MsgBox ("An error has ocurred. Contact your developer.")
         Exit Sub
      Else
      GoTo TryAgain
      End If
  Else
  End If
  
  Worksheets("List").Range("B" &amp; lgLastRow + 1).Value = strRandNum
  Worksheets("List").Range("B1").Select
  </code></pre>