Jump to content
Sign in to follow this  
nooneclose

[SOLVED] How to search through a column and delete certain names.

Recommended Posts

My program has to first search for names in Column D that do not match up with column C. I got that search to work using arrays. It was slow and I could not figure out how to delete them so I just manually put coded the names that do not belong. I found their cell location but I do not know how to store that location and delete it.

This is what I have so far.

Local $NameToDelete1[6]  = _Excel_RangeFind($OpenWorkbook, "Smith, Bill")
_ArrayDisplay($NameToDelete1, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $NameToDelete1[2], $xlShiftUp)

 

Please help, I wanted to have this program done yesterday but I did not see this problem until yesterday. 

Edited by nooneclose

Share this post


Link to post
Share on other sites

Just to make sure I understand your problem:

You have an Excel Workbook where you want to delete all rows where the value in column D doe not match the value in column C of the same row?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-03-26 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-03-21 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEXGUI (NEW 2020-03-27 - Version 1.0.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@water I was actually able to figure this one out on my own. But if you have a better way please share. 

As of now, this is what I use:

Local $NameToDelete1  = _Excel_RangeFind($OpenWorkbook, "Smith, Bob")
Local $Name           = $NameToDelete1[0][3]
Local $NameCell       = $NameToDelete1[0][2]
Local $CellNumber     = StringSplit($NameCell, "A, B, C, D, E")
Local $CellRange      = $NameCell & ":E" & $CellNumber[2]
_Excel_RangeDelete($OpenWorkbook.ActiveSheet, $CellRange, $xlShiftUp)

I have to manually make the code for every name, (that I know of) that should not be on the roster. I would greatly appreciate it if the program would search for me for any name in column D that is not found in column C and then delete that cell (upwards) which holds the name in column D and their time in column E. 

Edited by nooneclose

Share this post


Link to post
Share on other sites

@IAMK Yes, it has to be done in AutoIt. That is the whole point of creating an automation script, to make the program do something that I do not want to.  Like I said earlier though I have already figured out how to accomplish this task plus I have refined the process since then. 

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By FUD
      hello 
      please i need help 
      i need to open link in default browser only one windows without duplicate if i try to open the same link 
       
      thanks 
    • By YogendraAtluri
      Hi, 
      I am new to AutoIT scripting and I am still learning. I am trying to communicate with a Labview application that acts like a server. it basically takes commands from the client. But for some commands, it also send back some data. 
      When i am sending commands from my script, i can see that the labview is getting them. But i am not able to get anything back. I tried different code pieces that are available online in the forum.
      This is the working piece of code which i been using to send data.
      #cs This module is used to establish tcp connection with lab view #ce #include <File.au3> Func SendCmd($cmd) TCPStartup() Local $IpAddress="192.168.10.101" Local $Port="5353" $Labview = TCPConnect($IpAddress,$Port) If @error Then ConsoleWrite('!--> TCPConnect error number ( ' & @error & ' ).' & @CRLF) TCPCloseSocket($Labview) TCPShutdown() Exit EndIf TCPSend($Labview, $cmd & @CRLF) TCPCloseSocket($Labview) TCPShutdown() EndFunc SendCmd("wt42d")  
      This is slightly modified code to send and receive data, which is not working. I am not getting any response back
      SendCmd("galil") Func SendCmd($cmd) TCPStartup() Local $IpAddress="192.168.10.101" Local $Port="5353" $Labview = TCPConnect($IpAddress,$Port) If @error Then ConsoleWrite('!--> TCPConnect error number ( ' & @error & ' ).' & @CRLF) TCPCloseSocket($Labview) TCPShutdown() Exit EndIf TCPSend($Labview, $cmd & @CRLF) $ip = @IPAddress1 ;create listening socket $Listensocket = TCPListen($ip, $Port) ConsoleWrite("Listening to Socket - " & $Listensocket & @CRLF) If $Listensocket = -1 Then ConsoleWrite("Exiting..." & @CRLF) Exit EndIf ;Accept incoming clients and recieve info While 1 $connectedsocket = TCPAccept($Listensocket) ConsoleWrite("Connecting to Socket - " & $connectedsocket & "Error -" & @error & @CRLF) If $ConnectedSocket >= 0 Then $ip2 = TCPRecv($connectedsocket,1000000) EndIf WEnd TCPCloseSocket($connectedsocket) TCPCloseSocket($Labview) TCPShutdown() EndFunc I am not getting anything back. I am getting the following output in the console
      +>Setting Hotkeys...--> Press Ctrl+Alt+Break to Restart or Ctrl+BREAK to Stop. Listening to Socket - 544 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 Connecting to Socket - -1Error -0 its going through that loop forever. i need to force stop it.
      But when i open putty and send the same command, i am getting response right away. 
      Can someone please help me with that.
      Thanks in advance
      Regards
      Yogendra
    • By Bhooshan
      I need to mute an ongoing call on Microsoft teams without activating the window. Teams allows us to mute using shortcut key Ctrl+Shift+m but only when the window is active.
      # Used set option as ongoing call can be with any person which leads to change in Title Name.
      AutoItSetOption ( "WinTitleMatchMode", 2 ) 
      # I am not clear with the control ID which will be good to use here and also the key combination of ^M       
      ControlSend ( "Microsoft Teams", "", "[CLASS:Intermediate D3D Window; INSTANCE:1]", "{ctrl down}")
       
      Can anyone help...!!! 
       

    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Sayed
      Hi there, 
      I'm new in AutoIt forms and using AutoIt to automate desktop application (able to automate the application normally but facing issue when I've to re-run the application twice within the same script...so need help in this please)
      here is the steps then followed by the issue in a brief : 
      1- run application . 
      2- do some actions (click menus,activate windows,set texts..)
      3- close the application. 
      4- run the application again & access the same controls.
      5- open the same windows again (like step 2)
      6- perform some validations (by getting texts from some text boxes)
      7- close the application again (and repeat 1-7 for 15 times in average )
      The issue 
      * all controls are accessible in the first run and actions done successfully on controls (for steps 1-3) BUT from the second run of the application from step-4 it's able to set focus only the main application window.
      Note: only unique properties used to while mapping the controls. 
      Error that appear in the console :
      UIAWrappers.au3" (1673) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: $x = Int($t[1] + ($t[3] / 2)) $x = Int($t[1] + (^ ERROR  
      Simple spy code  of one of the controls that has this strange issue(menubar&view menu Item): 
      ;~ *** Standard code maintainable *** #include "UIAWrappers.au3" AutoItSetOption("MustDeclareVars", 1) _UIA_setVar("oP1","Title:=XXX;controltype:=UIA_WindowControlTypeId;class:=WindowsForms10.Window.8.app") ;main app form xxx _UIA_setVar("oP2","Title:=menuStrip1;controltype:=UIA_MenuBarControlTypeId;class:=WindowsForms10.Window.8.app") ;menuStrip1 ;~ $oUIElement=_UIA_getObjectByFindAll("View.mainwindow", "title:=View;ControlType:=UIA_MenuItemControlTypeId", $treescope_subtree) _UIA_setVar("oUIElement","Title:=View;controltype:=UIA_MenuItemControlTypeId;class:=") ;ControlType:=UIA_MenuItemControlTypeId;classname:=") ;~ Actions split away from logical/technical definition above can come from configfiles ;~_UIA_Action("oP1","highlight") _UIA_Action("oP1","setfocus") ;~_UIA_Action("oP2","highlight") _UIA_Action("oP2","setfocus") _UIA_action("oUIElement","highlight") ;~_UIA_action("oUIElement","click")  
       
×
×
  • Create New...