Sign in to follow this  
Followers 0
JailDoctor

Find Excel cell

5 posts in this topic

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?

#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.

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 =

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0