Help with method

[CarolineHedges]

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

[Die_Another_Day]

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[CarolineHedges]

I have made some ammendments but I can not get it to work, see full 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 I after the End IF, or should it be before?


The idea is that I will have a long list of countries which the loop will search through, and for each country it finds it will bold, and insert two rows. if it doesn't find the country it will move onto the next I. But I also need it to search for the countries after the work "Equity" appears on the spreadsheet.

Thanks in advance,. again

[CarolineHedges]

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

[Die_Another_Day]

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

[CarolineHedges]

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

[Die_Another_Day]

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

[CarolineHedges]

Yes!!!!!!

You are brilliant. Thank you very much.