UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Automation In Excel - Search Move Cursor Macro, Any Version    
 
   
bakersburg9
post Aug 20 2019, 04:33 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I can't believe I couldn't find out how to do this - I found nothing close - what I want to do is run my macro, which will highlight Column A, pop up a message box asking me to type in a string of 5 numeric characters - it will be in Col A - when it finds it - move to the right 3 columns

So:
1. Run macro
2. Column A highligted
3. Message box pops up, asking for search parameters
4. Type in parameters in message box
5. When it's found, move over 3 columns
Go to the top of the page
 
DanielPineault
post Aug 20 2019, 06:19 PM
Post#2


UtterAccess VIP
Posts: 6,899
Joined: 30-June 11



Why don't you record a macro and see what code gets generated and then go from there? Typically how I attack any automation I need to perform.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ADezii
post Aug 20 2019, 06:30 PM
Post#3



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Assuming that the Data in Column 'A' on any Worksheet starts at Row 1 AND the Data in Column 'A' is continuous, then the general idea would be as follows.
  2. Code Definition (any Worksheet meeting the above two conditions):
    CODE
    Public Sub HighlightAndSearch(strWorksheet As String)
    Dim sht As Excel.Worksheet
    Dim strResponse As String
    Dim lngRow As Long

    Set sht = ActiveWorkbook.Worksheets(strWorksheet)

    sht.Range("A:A").Select     'Highlight Column 'A' in Sheet strWorksheet

    'Prompt for Search Parameter
       strResponse = InputBox$("Enter Search Parameter", "Parameter Search")
    If strResponse = "" Then Exit Sub       'No Parameter entered, get outta town!

    lngRow = 1      'Start Row with Data in Column 'A' in Sheet strWorksheet

    With sht
       Do While .Cells(lngRow, "A") <> ""
         'If Match is found Move 3 Columns to the Right ('D')
         If UCase(.Cells(lngRow, "A")) = UCase(strResponse) Then      'Match NOT case-sensitive
           .Cells(lngRow, "D").Select
             Exit Sub
         End If
           lngRow = lngRow + 1
       Loop
    End With

    'If you get to this point, the Search Parameter was not found
    MsgBox "The Search Parameter [" & strResponse & "] was not found!", vbExclamation, "Parameter Not Found"
    End Sub
  3. Sample Macro Call for 'Sheet1':
    CODE
    Call HighlightAndSearch("Sheet1")
  4. If the Worksheet never changes, then you do not need an Argument in the Macro for the Sheet Name.
  5. Hope this is what you are looking for.

P.S. - Just realized that this needs to be Automation Code. Same sequence would apply, you kust need to reference Object Variables accordingly.
This post has been edited by ADezii: Aug 20 2019, 06:33 PM
Go to the top of the page
 
bakersburg9
post Aug 21 2019, 09:35 AM
Post#4



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Adez,
That worked only if it was an exact match - for example, "AK7-A7-19154 | Bunn, SGT Harry" was one of the values - if I type that in when prompted, it worked like a charm - but I want to just put in the last 5 in this case '19154' - and it WILL be found, so no need for handling condition where if not found, upper case, lower case - sorry I wasn't clear

Here's what I need to incorporate - something like:
CODE
LookAt:=xlPart
, but I haven't got a clue where I would put it, and it's probably more that just sticking LookAt:=xlPart in the "right spot"
Go to the top of the page
 
ADezii
post Aug 21 2019, 10:08 AM
Post#5



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


My apologies, since I am a little confused as to what exactly you are trying to accomplish. Would not the following Revision to the Code work?
CODE
'********************** CODE INTENTIONALLY OMITTED '**********************
Do While .Cells(lngRow, "A") <> ""
     'If Match is found Move 3 Columns to the Right ('D')
     If Right$(UCase(.Cells(lngRow, "A")), 5) = UCase(strResponse) Then
       blnFound = True
         .Cells(lngRow, "D").Select
           Exit Do
     End If
       lngRow = lngRow + 1
Loop
'********************** CODE INTENTIONALLY OMITTED '**********************

This post has been edited by ADezii: Aug 21 2019, 10:10 AM
Go to the top of the page
 
bakersburg9
post Aug 21 2019, 10:24 AM
Post#6



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Thanks, but blew up on line 90
Compile error: Invalid or qualified reference on .Cells

CODE
Public Sub HighlightAndSearch(strWorksheet As String)

10  Dim sht As Excel.Worksheet
20 Dim strResponse As String
30 Dim lngRow As Long

Set sht = ActiveWorkbook.Worksheets(strWorksheet)

sht.Range("A:A").Select     'Highlight Column 'A' in Sheet strWorksheet

'Prompt for Search Parameter
   strResponse = InputBox$("enter last 5 of job#", "Parameter Search")
If strResponse = "" Then Exit Sub       'No Parameter entered, get outta town!

lngRow = 1      'Start Row with Data in Column 'A' in Sheet strWorksheet

'******** EDIT 8/21
90 Do While .Cells(lngRow, "A") <> ""
     'If Match is found Move 7 Columns to the Right ('H')
     If Right$(UCase(.Cells(lngRow, "A")), 5) = UCase(strResponse) Then
       blnFound = True
         .Cells(lngRow, "H").Select
           Exit Do
     End If
       lngRow = lngRow + 1
Loop

'If you get to this point, the Search Parameter was not found

MsgBox "The Search Parameter [" & strResponse & "] was not found!", vbExclamation, "Parameter Not Found"
End Sub
I made some very minor edits - I'm sure what's wrong will jump out at you, because it compiled before - very possible operator error...
Go to the top of the page
 
ADezii
post Aug 21 2019, 11:35 AM
Post#7



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


Let's try another approach.
  1. Create a Folder named Test on the C:\Drive (C:\Test\).
  2. Copy the Attached Test.xlsx to the C:\Test Folder (C:\Test\Test.xlsx).
  3. Open Excel Automation.xlsm and click on the only Command Button.
  4. When prompted, enter 19154.
  5. You should get a match on Test.xlsm on Row# 3 and the Cursor will be moved to Column D/same Row.
  6. Test.xlsx will remain Opened and may/may not be Minimized on the Task Bar.

Attached File(s)
Attached File  Excel_Automation.zip ( 666.17K )Number of downloads: 1
 
Go to the top of the page
 
bakersburg9
post Aug 21 2019, 11:48 AM
Post#8



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Worked great ! Thanks ! cool.gif

So I'm confused - why does it work with the opening the workbook, but not on the active workbook ?

Makes me want to write a macro to close and save, then open again, just because it works
This post has been edited by bakersburg9: Aug 21 2019, 11:58 AM
Go to the top of the page
 
ADezii
post Aug 22 2019, 06:56 AM
Post#9



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I'm not really sure what you mean, but when you Open an External Workbook via Automation Code, it automatically becomes the ActiveWorkbook
    CODE
    Set wkb = appExcel.Workbooks.Open("C:\Test\Test.xlsx")
    'wkb now becomes the ActiveWorkbook

    'If you type Debug.Print appExcel.ActiveWorkbook.Name, it will return
    Test.xlsx
  2. Does this clarify anything?
Go to the top of the page
 
bakersburg9
post Aug 22 2019, 09:11 AM
Post#10



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Yes, that does, thanks ! cool.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th October 2019 - 02:38 AM