Using a string to reference an address

[Guest]

I want to use my strCell to reference an address, such as "A1" - how would I
copy this cell? strCell.Copy does not work - I'm assuming it is because I am
using a string and excel can't read my mind

If rngCodeFound Is Nothing Then
MsgBox "No FTO"
'Code is found
Else
strCell = rngCodeFound.Address
strCell = Right(strCell, 2)
strCell = ("A" + strCell)
' copy the cell that strCell variable references
' paste the copied cell to a new wks
End If

 

[Don Guillett]

post all of your code pertaining to this. Maybe?

strCell.value ="A"& right( rngCodeFound,2)

 

[Guest]

Here's the rest of the code:

Private Sub SelectColumn()

Dim rngDateFound As Range
Dim rngCodeFound As Range
Dim rngToSearch As Range
Dim rngFinal As Range
Dim wks As Worksheet
Dim wksreport As Worksheet
Dim strCell As String

Set wks = Sheets("worksheet")
Set wksreport = Sheets("report")
Set rngToSearch = wks.Cells
Set rngDateFound = rngToSearch.Find(What:=Date, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
'Current date is not found
If rngDateFound Is Nothing Then
MsgBox "Sorry, the current date is not a valid parameter."
'Current date is found
Else
Set rngToSearch = rngDateFound.EntireColumn
Set rngCodeFound = rngToSearch.Find(What:="FTO", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
'Code is not found
If rngCodeFound Is Nothing Then
MsgBox "No FTO"
'Code is found
Else
strCell = rngCodeFound.Address
strCell = Right(strCell, 2)
strCell = ("A" + strCell)
MsgBox strCell
End If
End If

End Sub

I did try inserting your statement, instead of my long path of getting the
cell's address, but I got an "Invalid QUalifier" error.

 

[Guest]

This works:

strCell = "A" & right(rngCodeFound.address, 2)

Which is much nicer than my lengthly way! But I still need to be able to
treat strCell as an address... sorry if I'm not explaining it right. I need
to be able to do something like this:

rngCodeFound.Copy ---> where I am coping cell A1

but because I'm using strCell and it is a string, I can't treat it as an
address... make sense?

 

[Guest]

Okay, I think I solved my own problem, BUT if anyone has a better, more
efficient method - please share!

Here's what I used: Cells(strCell, 1).Copy

 

[Guest]

Instead of
strCell = rngCodeFound.Address
strCell = Right(strCell, 2)
Cells(strCell, 1).Copy

why not
Cells(rngCodeFound.Row, 1).Copy

 

[RB Smissaert]

Not sure why you need to put the found range into a string variable.
Can't you work directly with the range object like:
rngCodeFound.Copy

RBS

 

[Don Guillett]

try

Sub copyfoundcell()
On Error GoTo nofind
With Sheets("sheet17")
..Rows(.Columns(1).Find(Date).Row).Find("fto").Copy _
Sheets("sheet15").Range("b2")
End With
Exit Sub
nofind: MsgBox "Not Found"
End Sub

 

[Guest]

Tried your routine and it does work, but maybe my code was a little too
scrambled because I still am unable to reference a different cell in the row
(i.e. I find "B3" because it is a "fto" cell and is in the Jul 31 column, but
I don't want to copy the "fto" text, but rather the name at the beginning of
that row, or "B1" - does that make sense?)

This is why I was attempting to use a string: to hold the value of the
column, while using my own value for the row. Any suggestions?

Thx!

Here's a sample (I want to copy cell A3, or "Andrew Perez")
V

A B
1 31-Jul 1-Aug
2 Anna Perez
3 Andrew Perez fto
4 Amy Gsjergvig fto
5 Linda Ueland