finding a string in a cell

  • Thread starter Thread starter thomas donino
  • Start date Start date
T

thomas donino

I am trying to find the cell where the word "show" is in row 1. I tried using
cells.find but that keeps returning show instead of the cell reference
 
showaddr = Rows("1:1").Find(What:="show", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

Regards,
Stefi

„thomas donino†ezt írta:
 
Hi Thomas

Find returns a range object..By default it returns the value.

Dim varFound As Variant, varRange As Range

Set varRange = ActiveSheet.Rows(1)
Set varFound = varRange.Find("Show")

If Not varFound Is Nothing Then
MsgBox varFound.Address(False, False)
MsgBox varFound.Text
End If

If this post helps click Yes
 
Steffi,

That is not working, here is the current code

Sub CopyQueryOutput2()

Dim ranDataStartCell As Range
Dim ranData As Range
Dim ranshowAddress As Range
Dim ranQrySyn As Range
Dim ranPerfInfoStartCell As Range
Dim ranPerfInfo As Range
Set ranDataStartCell = Worksheets("SampleQueryOutput").Range("A5")

ranshowAddress = Rows("1:1").Find(What:="show", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

MsgBox ranshowAddress
End Sub

Stefi said:
showaddr = Rows("1:1").Find(What:="show", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

Regards,
Stefi

„thomas donino†ezt írta:
I am trying to find the cell where the word "show" is in row 1. I tried using
cells.find but that keeps returning show instead of the cell reference
Click to expand...
Click to expand...
 
Your code produced the proper msgbox but the following barfs on the last line

Sub CopyQueryOutput2()
Dim varFound As Variant, varRange As Range
Dim ranDataStartCell As Range
Dim ranData As Range
Dim ranQrySynStartCell As Range
Dim ranQrySyn As Range
Dim ranPerfInfoStartCell As Range
Dim ranPerfInfo As Range

'get cell with the word show in it, to set the start point where
'the query syntax is located
Set varRange = ActiveSheet.Rows(1) 'dont forget to change active sheet
reference later
Set varFound = varRange.Find("Show")
Set ranQrySynStartCell = varFound.Address

End Sub
 
Hi Thomas

Didnt you read the earlier post..Find returns a ** range ** object. .Address
returns a string which you are trying to set it to a variable declared as
Range and hence the error. The below will do

Set varRange = ActiveSheet.Rows(1)
Set ranQrySynStartCell = varRange.Find("Show")

If this post helps click Yes
 
You are not fully qualifying the search code; if the sheet is not active for
example.

.....

With Worksheets("SampleQueryOutput")
Set ranDataStartCell = .Range("A5")

ranshowAddress = .Rows("1:1").Find(What:="show", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

End With

MsgBox ranshowAddress

End Sub


--

Regards,
Nigel
(e-mail address removed)



thomas donino said:
Steffi,

That is not working, here is the current code

Sub CopyQueryOutput2()

Dim ranDataStartCell As Range
Dim ranData As Range
Dim ranshowAddress As Range
Dim ranQrySyn As Range
Dim ranPerfInfoStartCell As Range
Dim ranPerfInfo As Range
Set ranDataStartCell = Worksheets("SampleQueryOutput").Range("A5")

ranshowAddress = Rows("1:1").Find(What:="show", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

MsgBox ranshowAddress
End Sub

Stefi said:
showaddr = Rows("1:1").Find(What:="show", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

Regards,
Stefi

„thomas donino†ezt írta:
I am trying to find the cell where the word "show" is in row 1. I tried
using
cells.find but that keeps returning show instead of the cell reference
Click to expand...
Click to expand...
Click to expand...
 
I sorted that out after I posted, thank you

Jacob Skaria said:
Hi Thomas

Didnt you read the earlier post..Find returns a ** range ** object. .Address
returns a string which you are trying to set it to a variable declared as
Range and hence the error. The below will do

Set varRange = ActiveSheet.Rows(1)
Set ranQrySynStartCell = varRange.Find("Show")

If this post helps click Yes
Click to expand...
 
My last problem on this I hope
How do I set the cell reference after i calculate what cell is the last cell
in the range I want? it breaks on last line

Option Explicit

Sub CopyQueryOutput2()
Dim Rw As Integer, Col As Integer
Dim varFound As Variant, varRange As Range
Dim ranDataStartCell As Range
Dim ranDataEndCell As Range
Dim ranData As Range
Dim ranQrySynStartCell As Range
Dim ranQrySyn As Range
Dim ranPerfInfoStartCell As Range
Dim ranPerfInfo As Range

'get the cell where the data starts, in case its not A1
Set varRange = ActiveSheet.Range("A1:B4") 'dont forget to change active
sheet reference later
Set ranDataStartCell = varRange.Find("theSec")

'get cell with the word show in it, to set the start point where
'the query syntax is located
Set varRange = ActiveSheet.Rows(1) 'dont forget to change active sheet
reference later
Set ranQrySynStartCell = varRange.Find("Show")

'get the cell with the word occs in it, its always in column 2
Set varRange = ActiveSheet.Columns(2) 'dont forget to change active sheet
reference later
Set ranPerfInfoStartCell = varRange.Find("Occs")
'get the cell where the data ends, its found by locating the interesction of
the column "show" is in
' and the row that "Occs" is in and moving up 2 and left 1
Col = ranQrySynStartCell.Column - 1
Rw = ranPerfInfoStartCell.Row - 2
'the line below is where code breaks
ranDataEndCell = Cells([Rw], [Col])
' ranData will then equal Range(ranDataStartCell:ranDataEndCell)
End Sub
 
Thomas, since the variable 'ranDataEndCell' is declared as Range change the
last line to

Set ranDataEndCell = Cells([Rw], [Col])

If this post helps click Yes
 
Back
Top