HYPERLINK BY VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Back
Top