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

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

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

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

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

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

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

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