Using a string to reference an address
 

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

Using a string to reference an address
 

post all of your code pertaining to this. Maybe?

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


--
Don Guillett
SalesAid Software

"anna" wrote in message
...
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

Using a string to reference an address
 

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.

"Don Guillett" wrote:

post all of your code pertaining to this. Maybe?

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


--
Don Guillett
SalesAid Software

"anna" wrote in message
...
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

Using a string to reference an address
 

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?

"Don Guillett" wrote:

post all of your code pertaining to this. Maybe?

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


--
Don Guillett
SalesAid Software

"anna" wrote in message
...
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

Using a string to reference an address
 

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

"anna" wrote:

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

Using a string to reference an address
 

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

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

"anna" wrote:

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

"anna" wrote:

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

Using a string to reference an address
 

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" wrote in message
...
post all of your code pertaining to this. Maybe?

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


--
Don Guillett
SalesAid Software

"anna" wrote in message
...
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

Using a string to reference an address
 

try

Sub copyfoundcell()
On Error GoTo nofind
With Sheets("sheet17")
..Rows(.Columns(1).Find(Date).Row).Find("fto").Cop y _
Sheets("sheet15").Range("b2")
End With
Exit Sub
nofind: MsgBox "Not Found"
End Sub
--
Don Guillett
SalesAid Software

"anna" wrote in message
...
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

"anna" wrote:

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

Using a string to reference an address
 

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


"Don Guillett" wrote:

try

Sub copyfoundcell()
On Error GoTo nofind
With Sheets("sheet17")
..Rows(.Columns(1).Find(Date).Row).Find("fto").Cop y _
Sheets("sheet15").Range("b2")
End With
Exit Sub
nofind: MsgBox "Not Found"
End Sub
--
Don Guillett
SalesAid Software

"anna" wrote in message
...
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

"anna" wrote:

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