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
> .find Using An Instr, Any Version    
 
   
ccman69
post Jun 30 2020, 08:34 AM
Post#1



Posts: 466
Joined: 5-July 07



I am trying to locate and make active a cell containing part of a string in a worksheet. Currently I am using the code below to locate the cell containing "31001" and to highlight the active cell plus three cells to the right of the selected cell.
Dim cl As Range

With Worksheets("KXSLSINV").Cells
Set cl = .Find("31001", After:=.Range("A2"), LookIn:=xlValues)
If Not cl Is Nothing Then
cl.Select
Range(Selection, Selection.Offset(0, 3)).Select
Selection.Interior.Color = vbYellow
End If

I need to find and select a cell that has a string within a string to select and highlight. The value I am looking for in the string is "AVG. G/M %". If my thinking is correct the above code searches for whole words only in a cell.

Is it possible to find the "AVG. G/M %" using the above code with instr added to the code? If so, how?

Thank you.

--------------------
ccman69
Go to the top of the page
 
June7
post Jun 30 2020, 01:49 PM
Post#2



Posts: 1,519
Joined: 25-January 16
From: The Great Land


Can use wildcard: "*AVG. G/M %*"

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
boobigbam@gmail....
post Jun 30 2020, 02:26 PM
Post#3



Posts: 13
Joined: 24-June 20



Not sure if im missunderstanding the question, but if i do:

Range("A1") = "Test of AVG. G/M % in cell"

and then tries to find the string: AVG. G/M %

it locates it in A1.
So it looks like you simply could replace "30001" with "AVG. G/M %"

CODE
Range("A1") = "Test of AVG. G/M % in cell"
With ActiveSheet.Cells
    Set cl = .Find("AVG. G/M %", , LookIn:=xlValues)
    If Not cl Is Nothing Then
        cl.Select
        Debug.Print cl.Value
    End If
End With

This post has been edited by boobigbam@gmail.com: Jun 30 2020, 02:27 PM
Go to the top of the page
 
ccman69
post Jul 2 2020, 09:20 AM
Post#4



Posts: 466
Joined: 5-July 07



boobigbam,

Your code worked great.

Thank you.

--------------------
ccman69
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th July 2020 - 11:30 AM