Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell on a workbook that I have opened
Hi there I am trying to get my code to let them decide if they want to change
the control (which is linked from another workbook) if they say yes it will open up the other workbook (which works fine) but then I want it to copy the information from the previous workbook and locate it on the one that has opened the code I am using is this - does anyone have any pointers? Sub goToSoa() Dim soaValue As String frmYesNo2.Show If frmYesNo2.rdNo = True Then Exit Sub If frmYesNo2.rdYes = True Then soaValue = ActiveCell.Copy Workbooks.Open Filename:= _ "http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement of Applicability.xls" Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell on a workbook that I have opened
Rather than activate through the Find, I find it better to set a range
variable, like so Set oCell = Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False and then test if it was found If Not oCell Is Nothing If you don't find it, then you don't do the FindNext. If you do, you have to make sure that you don't then just find the same one, VBA Help on Find shows how. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pasty" wrote in message ... Hi there I am trying to get my code to let them decide if they want to change the control (which is linked from another workbook) if they say yes it will open up the other workbook (which works fine) but then I want it to copy the information from the previous workbook and locate it on the one that has opened the code I am using is this - does anyone have any pointers? Sub goToSoa() Dim soaValue As String frmYesNo2.Show If frmYesNo2.rdNo = True Then Exit Sub If frmYesNo2.rdYes = True Then soaValue = ActiveCell.Copy Workbooks.Open Filename:= _ "http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement of Applicability.xls" Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell on a workbook that I have opened
Hi,
I tried putting this bit in but it comes up with "Compile Area: Variable not defined and goes to the Set oCell bit so i set the variable as a range which worked but it is not finding the relevant cell. "Bob Phillips" wrote: Rather than activate through the Find, I find it better to set a range variable, like so Set oCell = Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False and then test if it was found If Not oCell Is Nothing If you don't find it, then you don't do the FindNext. If you do, you have to make sure that you don't then just find the same one, VBA Help on Find shows how. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pasty" wrote in message ... Hi there I am trying to get my code to let them decide if they want to change the control (which is linked from another workbook) if they say yes it will open up the other workbook (which works fine) but then I want it to copy the information from the previous workbook and locate it on the one that has opened the code I am using is this - does anyone have any pointers? Sub goToSoa() Dim soaValue As String frmYesNo2.Show If frmYesNo2.rdNo = True Then Exit Sub If frmYesNo2.rdYes = True Then soaValue = ActiveCell.Copy Workbooks.Open Filename:= _ "http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement of Applicability.xls" Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell on a workbook that I have opened
So that suggests that the value is not there. Check what is in soaValue, and
the target worksheet. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pasty" wrote in message ... Hi, I tried putting this bit in but it comes up with "Compile Area: Variable not defined and goes to the Set oCell bit so i set the variable as a range which worked but it is not finding the relevant cell. "Bob Phillips" wrote: Rather than activate through the Find, I find it better to set a range variable, like so Set oCell = Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False and then test if it was found If Not oCell Is Nothing If you don't find it, then you don't do the FindNext. If you do, you have to make sure that you don't then just find the same one, VBA Help on Find shows how. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pasty" wrote in message ... Hi there I am trying to get my code to let them decide if they want to change the control (which is linked from another workbook) if they say yes it will open up the other workbook (which works fine) but then I want it to copy the information from the previous workbook and locate it on the one that has opened the code I am using is this - does anyone have any pointers? Sub goToSoa() Dim soaValue As String frmYesNo2.Show If frmYesNo2.rdNo = True Then Exit Sub If frmYesNo2.rdYes = True Then soaValue = ActiveCell.Copy Workbooks.Open Filename:= _ "http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement of Applicability.xls" Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell on a workbook that I have opened
Hi I did this by getting it to print the value in to a cell offset from the
activecell and it is printing the text I want it to search for on the other sheet. "Bob Phillips" wrote: So that suggests that the value is not there. Check what is in soaValue, and the target worksheet. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pasty" wrote in message ... Hi, I tried putting this bit in but it comes up with "Compile Area: Variable not defined and goes to the Set oCell bit so i set the variable as a range which worked but it is not finding the relevant cell. "Bob Phillips" wrote: Rather than activate through the Find, I find it better to set a range variable, like so Set oCell = Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False and then test if it was found If Not oCell Is Nothing If you don't find it, then you don't do the FindNext. If you do, you have to make sure that you don't then just find the same one, VBA Help on Find shows how. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pasty" wrote in message ... Hi there I am trying to get my code to let them decide if they want to change the control (which is linked from another workbook) if they say yes it will open up the other workbook (which works fine) but then I want it to copy the information from the previous workbook and locate it on the one that has opened the code I am using is this - does anyone have any pointers? Sub goToSoa() Dim soaValue As String frmYesNo2.Show If frmYesNo2.rdNo = True Then Exit Sub If frmYesNo2.rdYes = True Then soaValue = ActiveCell.Copy Workbooks.Open Filename:= _ "http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement of Applicability.xls" Cells.Find(What:=soaValue, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a Workbook based on a Cell information in another Workbook | Excel Worksheet Functions | |||
How can I get the value of a cell in a un-opened workbook ? | Excel Programming | |||
Opening a workbook if not opened, going to it if already opened | Excel Programming | |||
Opening a workbook if not opened, going to it if already opened | Excel Programming | |||
How to see if the opened workbook is opened by another user ? | Excel Programming |