PDA

View Full Version : Loop not working



epd
03-28-2012, 03:16 PM
Hi,

what i am trying to do:
*i have about 2000 rows.
*start from first row
*for each row that contains .pdf in one cell, i want another cell in that row to equal .dir
*hyperlink cell if cell contains dir
*move onto next cell (for all approx 2000 cells)

the problem:
*it is hyperlinking every cell even if the other cell doesn't contain .pdf.
*i think it is looking at the first row and if the cell has .pdf it applies it too all cells

what i want to do:
*select entire row of first row (21)
*if col 21 = .pdf then col 23 = .dir
*of col 23 = dir hyperlink .dir to fname value
*move onto next row (22)....do the same....and so on and so on all the way to row 2039

the code i am using:

Private Sub UserForm_activate()
'Unprotect Workbook
ThisWorkbook.Worksheets("Cover Page").Unprotect Password:="password"
ThisWorkbook.Worksheets("D - Documentation").Unprotect Password:="password"
ThisWorkbook.Worksheets("E - Electrical").Unprotect Password:="password"
ThisWorkbook.Worksheets("F - Process Flow & P&ID").Unprotect Password:="password"
ThisWorkbook.Worksheets("G - General Arrangement").Unprotect Password:="password"
ThisWorkbook.Worksheets("L - Layout").Unprotect Password:="password"
'Open Userform on same screen as workbook and center
With UserForm1
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
End With

'DIR HYPERLINKING

Me.Repaint

'select cells and clear values
ActiveSheet.Range("W21:W2039").Select
Selection.ClearContents

'Run Hyperlinking
For Each cll In Range("W21:W2039").Cells
fname = "I:\Drafting\As Built\4CP - Pinkenba\E - Electrical\Zircon Plant\"
shortname = ".dir"
Selection.EntireRow.Select
If CStr(Selection("21").Value) = ".pdf" Then
Selection("23").Value = ".dir"
Else
Selection("23").Value = "-"
End If

If CStr(Selection("23").Value) = ".dir" Then
cll.Parent.Hyperlinks.Add cll, fname, , , shortname
Else
cll.Value = "-"
End If

Next cll

'Close UserForm1
Unload UserForm1
End Sub

also posted here (http://www.mrexcel.com/forum/showthread.php?p=3098465#post3098465)

thankyou

Bob Phillips
03-28-2012, 03:38 PM
fname = "I:\Drafting\As Built\4CP - Pinkenba\E - Electrical\Zircon Plant\"
For Each cll In Range("W21:W2039").Cells
shortname = ".dir"
Cells(cll.Row, "W").Value = IIf(Cells(cll.Row, "U").Value = ".pdf", ".dir", "-")

If Cells(cll.Row, "W").Value = ".dir" Then
cll.Parent.Hyperlinks.Add cll, fname, , , shortname
Else
cll.Value = "-"
End If
Next cll

epd
03-28-2012, 04:30 PM
Thankyou very much xld. It works perfectly.

Are you able to tell me what was going wrong with my code?

Bob Phillips
03-29-2012, 01:33 AM
Not really. I had no idea what this code and its brothers was all about

Selection("21").Value

as I have never seen this notation before. I think you believe it is returning the 21st cell in the selection, but as far as I can see it is just returning the value 21. Anyway, I replaced it all with a much more tried and tested way. I also moved the loading of the fname to outside of the loop. It is only set once, it never changes, so there is no need to do it in the loop - inefficient.