Type mismatch in AutoHotkey Excel

Question

I am trying to get input from a user, lookup the input value in an Excel column and return the value of the cell on the right side of the matching value.

This is what I came up with. As long as I replace %index% with a number, it will return a value from the Excel file.

The error I receive tells me there is a 'type mismatch' where I use %index% in

value := workbook.Sheets("Sheet1").Cells(%index%, 1).Value

Any ideas how to fix the type mismatch?

#a::
workbook := ComObjGet("somepath\tester.xlsx")

InputBox, OutputVar, Question 1, What are you looking for?
if (OutputVar)
   MsgBox, Let me do this for you.
   intent = OutputVar

index = 1
value = ""

Loop {
    index := %index% + 1
    value := workbook.Sheets("Sheet1").Cells(%index%, 1).Value
}
Until %intent% = %value%

SendInput, workbook.Sheets("Sheet1").Cells(%index%, 2).Value

Return

Answer 1

Use index, not %index%, in expressions. Also, you can use the built-in A_INDEX variable inside of loops

Here's your corrected code:

#a::
  workbook := ComObjGet("somepath\tester.xlsx")
  MAX_ROWS := 10

  InputBox intent, Question 1, What are you looking for?

  if ( ErrorLevel == 0 && intent ) {
    Loop %MAX_ROWS% {
      if ( intent == workbook.Sheets("Sheet1").Cells(A_Index, 1).Value ) {
         SendInput % workbook.Sheets("Sheet1").Cells(A_Index, 2).Value
         return
      }
    }
    MsgBox 48, Not Found, "%intent%" not found in column a
  }
  return

Notes:

• You cannot use substitution when a command takes an expression
• ErrorLevel == 0 means OK was pressed. See InputBox
• SendInput % makes the line use expression mode; everything following "% " is evaluated as an expression
• Your loop never exits if intent is not found in the spreadsheet