Find Excel cell

[JailDoctor]

I am working on a routine to check assigned classrooms versus a roster.

The user chooses from an excel spreadsheet the ID numbers and classrooms. The routine checks if the rooms match those in the roster.

If the room for one element is different, I need to change it in the spreadsheet.

The question: How do I track what cells the user selected when choosing classrooms to change the value when necessary?

expandcollapse popup

#include <clipboard.au3>
#include <Array.au3>
#include <ExcelCOM_UDF.au3>
;Set a hotkey combo for exit
HotKeySet("{ESC}", "MyExit")
HotKeySet("{F9}", "TogglePause")
Global $oExcel, $XLArrayID, $XLArrayRoom
Opt("WinTitleMatchMode",2)
$message = "Choose the file containig today's list"
$var = FileOpenDialog($message, @MyDocumentsDir & "\", "All (*.xls*)", 1 + 4 )
If @error Then
    MsgBox(4096,"","No File(s) chosen")
Exit
EndIf
;Open selected file
$oExcel = _ExcelBookOpen($var)
;clear clipboard
_ClipBoard_Open (0)
_ClipBoard_Empty ()
_ClipBoard_Close ()
MsgBox(262176, "Choose", "Select ID numbers")
;User selects a group of cells
$oExcel.Application.Selection.Copy
$XLArrayID = (_ClipBoard_GetData ())
;~ Convert to Array
$XLArrayID = StringSplit($XLArrayID,@LF)
_ArrayTrim($XLArrayID, 1, 1, 0)
;clear clipboard
_ClipBoard_Open (0)
   _ClipBoard_Empty ()
   _ClipBoard_Close ()
  
   MsgBox(262176, "Locations", "Select Class Rooms")
;User selects a group of cells
$oExcel.Application.Selection.Copy
$XLArrayRoom = (_ClipBoard_GetData ())
;~ Convert to Array
$XLArrayRoom = StringSplit($XLArrayRoom,@LF)
_ArrayTrim($XLArrayRoom, 1, 1, 0)
;Remove empty elements
For $n = UBound($XLArrayID) - 1 To 0 Step - 1
If StringStripWS($XLArrayID[$n], 8) = "" Then
  _ArrayDelete($XLArrayID, $n)
  _ArrayDelete($XLArrayRoom, $n)
EndIf
Next
; This is where I check the classrooms assigned versus a roster.
;How do I get the Excel spreadsheet location of each element?

Your help is trully appreciated.

[Reg2Post]

If you are trying to return the cell location of a value you can assign a cell with the formula =Match("string",range,0). Or, you can assign the vba equivalent [cells.find("string").address] to a variable and return the variable to your function. You must create a reference to a COM object, in your case, "excel.application", to use the latter.

[JailDoctor]

Thanks Reg2Post, but this approach wont work because the "strings" may repeat several times in the same sheet.

I did look at this:

$WhatCell = $oExcel.ActiveCell.Address

And this gives me the address of the first cell the user selected. Something like this: $L$139

So now I know the first cell corresponding to the first element.

I thought:

_ExcelWriteCell($oExcel,$NewClassroom, $WhatCell + $n)

$n= element number

But it didn't work.

How do I add a row to active address?

[Reg2Post]

L139 would be (139, 11) in R1C1 format. You can add variables or integers to either the row or column number to suit your needs.

If using _ExcelWriteCell, then just use the last optional parameter to add a column location and change the $sRangeOrRow parameter to an integer for the row location.

With vba: Activesheet.Cells( R1, C1).Value =

[water]

If you need to get all cells with a certain string you can use function _ExcelFindInRange of the