hi,
I have a dynamic hyperlink by the following code:

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

it works well but the problem is like when I drag a formula like
this,=IF(C127,"YY"," ") in column A. it's like for example when the cell C127
is not empty the the cell A127 will be YY and so on.
in this case the active cell jumps to the last cell in which we have
the formula, but I want it go to the last cell with "YY".
any help?
thanx

First, that doesn't look like a hyperlink to me.
Second,
=if(c127,"yy"," ")
could leave a space character in that cell. I can't think of a time when this
is better than returning "" (no space character).

I'd use:
=if(cl27,"yy","")
The cell will still look empty, but might make other formulas easier to write:
=if(trim(a999)="", ...
is uglier than:
=if(a999="",...

Third, you can use Edit|find in your code to look for that YY value:

Dim FoundCell as range
with activesheet
with .range("a:a")
Set FoundCell = .Cells.Find(what:="yy", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
msgbox "YY wasn't found!"
else
application.goto foundcell, scroll:=true
end if

(Untested, uncompiled. Watch for typos.)

Searching after the first cell in the range, but with a direction of xlprevious,
will find that last cell with yy in it.





peyman wrote:

hi,
I have a dynamic hyperlink by the following code:

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

it works well but the problem is like when I drag a formula like
this,=IF(C127,"YY"," ") in column A. it's like for example when the cell C127
is not empty the the cell A127 will be YY and so on.
in this case the active cell jumps to the last cell in which we have
the formula, but I want it go to the last cell with "YY".
any help?
thanx

--

Dave Peterson

thanx Dave.too much stuff to read!.i need time.I'll let you know later.

"Dave Peterson" wrote:

First, that doesn't look like a hyperlink to me.
Second,
=if(c127,"yy"," ")
could leave a space character in that cell. I can't think of a time when this
is better than returning "" (no space character).

I'd use:
=if(cl27,"yy","")
The cell will still look empty, but might make other formulas easier to write:
=if(trim(a999)="", ...
is uglier than:
=if(a999="",...

Third, you can use Edit|find in your code to look for that YY value:

Dim FoundCell as range
with activesheet
with .range("a:a")
Set FoundCell = .Cells.Find(what:="yy", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
msgbox "YY wasn't found!"
else
application.goto foundcell, scroll:=true
end if

(Untested, uncompiled. Watch for typos.)

Searching after the first cell in the range, but with a direction of xlprevious,
will find that last cell with yy in it.





peyman wrote:

hi,
I have a dynamic hyperlink by the following code:

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

it works well but the problem is like when I drag a formula like
this,=IF(C127,"YY"," ") in column A. it's like for example when the cell C127
is not empty the the cell A127 will be YY and so on.
in this case the active cell jumps to the last cell in which we have
the formula, but I want it go to the last cell with "YY".
any help?
thanx

--

Dave Peterson