Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
I cant get this to work, could someone help me? Equity = ActiveCell.Value Cells.Find(What:=Country, After:=Range("Equity") LookAt:=xlWhole).Activate Thanks in advanc -- CarolineHedge ----------------------------------------------------------------------- CarolineHedges's Profile: http://www.excelforum.com/member.php...fo&userid=3570 View this thread: http://www.excelforum.com/showthread.php?threadid=56605 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
After is looking for a range, you are providing it with a string.
Dim Equity as Range Dim sCell as Range "Search Cell" Set Equity = ActiveCell Set sCell = Cells.Find(What:=Country, After:=Equity, _ LookAt:=xlWhole) If sCell is Nothing Then MsgBox "Country Not Found" Else sCell.Activate End If This assumes Country is a VB variable, if you actually want to look for "Country" then enclose it in quotation marks like this: What:="Country" You do not have to use sCell and set sCell to the .Find, however if you don't use that syntax and Country is not found an error will occur. HTH Die_Another_Day "CarolineHedges" <CarolineHedges.2bnxyt_1154103009.0834@excelforu m-nospam.com wrote in message news:CarolineHedges.2bnxyt_1154103009.0834@excelfo rum-nospam.com... I cant get this to work, could someone help me? Equity = ActiveCell.Value Cells.Find(What:=Country, After:=Range("Equity"), LookAt:=xlWhole).Activate Thanks in advance -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=566055 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
I have made some ammendments but I can not get it to work, see ful code: For I = 1 To 35 Sheets("Menu").Select Country = Cells(39 + I, 3).Value If Country = Empty Then GoTo 7 Sheets("Portfolio Valuation").Select Cells.Find("Equity").Select Dim sCell as Range "Search Cell" Set Equity = ActiveCell Set sCell = Find(What:=Country, After:=Equity, _ LookAt:=xlWhole).Activate If sCell Is Nothing Then Next I Else sCell.Activate ActiveCell.Font.Bold = True Selection.EntireRow.Insert Selection.EntireRow.Insert End If Next I It doesn't like the line in red above. also is it right to have next after the End IF, or should it be before? The idea is that I will have a long list of countries which the loo will search through, and for each country it finds it will bold, an insert two rows. if it doesn't find the country it will move onto th next I. But I also need it to search for the countries after the wor "Equity" appears on the spreadsheet. Thanks in advance,. agai -- CarolineHedge ----------------------------------------------------------------------- CarolineHedges's Profile: http://www.excelforum.com/member.php...fo&userid=3570 View this thread: http://www.excelforum.com/showthread.php?threadid=56605 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
Apologies, this should say: For I = 1 To 35 Sheets("Menu").Select Country = Cells(39 + I, 3).Value If Country = Empty Then GoTo 7 Sheets("Portfolio Valuation").Select Cells.Find("Equity").Select Dim Equity As Range Dim sCell As Range "Search Cell" Set Equity = ActiveCell sCell = Cells.Find(What:=Country, After:=Equity, _ LookAt:=xlWhole) If sCell Is Nothing Then Next I Else sCell.Activate ActiveCell.Font.Bold = True Selection.EntireRow.Insert Selection.EntireRow.Insert End If Next I But it still doesn't like the bit in red, or the next I bit in red which i should perhaps replace with activecell.selec -- CarolineHedge ----------------------------------------------------------------------- CarolineHedges's Profile: http://www.excelforum.com/member.php...fo&userid=3570 View this thread: http://www.excelforum.com/showthread.php?threadid=56605 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
Try this:
Dim Equity As Range Dim sCell As Range '"Search Cell" For I = 1 To 35 Sheets("Menu").Select Country = Cells(39 + I, 3).Value If Country = Empty Then GoTo 7 Sheets("Portfolio Valuation").Select Set Equity = Cells.Find("Equity") If Not Equity Is Nothing Then sCell = Cells.Find(What:=Country, After:=Equity, _ LookAt:=xlWhole) If Not sCell Is Nothing Then sCell.Activate ActiveCell.Font.Bold = True Selection.EntireRow.Insert Selection.EntireRow.Insert End If End If Next I The main problem was that you forgot the ' before the comment in the Dim statement. As a general rule, Dim statements should always be at the top of your code. Also you can't have two next I statements, the easiest work around is to put a jump in there, however in your case it wasn't needed, but here's how to do it just in case: For i = 1 to 10 if i 5 then Goto SkipToNext Else msgbox i end if SkipToNext: Next i HTH Die_Another_Day CarolineHedges wrote: Apologies, this should say: For I = 1 To 35 Sheets("Menu").Select Country = Cells(39 + I, 3).Value If Country = Empty Then GoTo 7 Sheets("Portfolio Valuation").Select Cells.Find("Equity").Select Dim Equity As Range Dim sCell As Range "Search Cell" Set Equity = ActiveCell sCell = Cells.Find(What:=Country, After:=Equity, _ LookAt:=xlWhole) If sCell Is Nothing Then Next I Else sCell.Activate ActiveCell.Font.Bold = True Selection.EntireRow.Insert Selection.EntireRow.Insert End If Next I But it still doesn't like the bit in red, or the next I bit in red, which i should perhaps replace with activecell.select -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=566055 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
Many thanks for the code but i can't quite get it to work: On the line: sCell = Cells.Find(What:=Country, After:=Equity, _ LookAt:=xlWhole) the error message is that the object variabe or With Block variable is not set. When I put the cursor over the variables: sCell = "Nothing" Country = "Australia" Equity = "Equity" So i am not sure how to fix. I have copied the code in straight from the email. Thanks -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=566055 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
sry my fault
sCell is a Range, which is an object and therefore must be "Set" Set sCell = Cells...... HTH Die_Another_Day CarolineHedges wrote: Many thanks for the code but i can't quite get it to work: On the line: sCell = Cells.Find(What:=Country, After:=Equity, _ LookAt:=xlWhole) the error message is that the object variabe or With Block variable is not set. When I put the cursor over the variables: sCell = "Nothing" Country = "Australia" Equity = "Equity" So i am not sure how to fix. I have copied the code in straight from the email. Thanks -- CarolineHedges ------------------------------------------------------------------------ CarolineHedges's Profile: http://www.excelforum.com/member.php...o&userid=35705 View this thread: http://www.excelforum.com/showthread...hreadid=566055 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with method
Yes!!!!!! You are brilliant. Thank you very much -- CarolineHedge ----------------------------------------------------------------------- CarolineHedges's Profile: http://www.excelforum.com/member.php...fo&userid=3570 View this thread: http://www.excelforum.com/showthread.php?threadid=56605 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please post this thread a correct full method, method about | New Users to Excel | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
Another Method or 2? | Excel Discussion (Misc queries) | |||
GetObject method not work after Call Shell Method | Excel Programming | |||
Why QUIT method doesn't work after COPY method? | Excel Programming |