Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I extract a string from an IP address in Excel? | Excel Discussion (Misc queries) | |||
How do I extract a string from an IP address in Excel? | Excel Discussion (Misc queries) | |||
Convert text string to IP address | Excel Worksheet Functions | |||
How do I manipulate a string to collect the address from it? | Excel Discussion (Misc queries) | |||
Extracting out of a emai address string | Excel Discussion (Misc queries) |