Loop through a range
 

I need to check a group of computers for a certian file. The computer
names will be in column A. I got it to work with the below code for
cell A1.

How can I get it to loop through all the rows in column A and return
the Y or N to column B?

(If possible please respond to the group as well as by email as I use
Google and it takes forever to see responses)

-----
Sub checkfile()

mname = "\\" + Range("A1") + "\c$\fred.txt"

If Len(Dir(mname)) 0 Then
Range("B1") = "Y"
Else
Range("B1") = "N"
End If

End Sub
-------

Data:
________________
A B
________________
machine1 Y
machine2 N
machine3 N
machine4 Y
machine5 Y

Loop through a range
 

Sub checkfile()
Dim cRows As Long
Dim i As Row

cRows = Cells(Rows.Count,"A").End(xlUp).Row

For i = 1 To cRows
mname = "\\" + Cells(i,"A").Value + "\c$\fred.txt"

If Len(Dir(mname)) 0 Then
Cells(i,"B").Value = "Y"
Else
Cells(i,"B").Value = "N"
End If

Next i

End Sub

--

HTH

RP

"Fred" wrote in message
om...
I need to check a group of computers for a certian file. The computer
names will be in column A. I got it to work with the below code for
cell A1.

How can I get it to loop through all the rows in column A and return
the Y or N to column B?

(If possible please respond to the group as well as by email as I use
Google and it takes forever to see responses)

-----
Sub checkfile()

mname = "\\" + Range("A1") + "\c$\fred.txt"

If Len(Dir(mname)) 0 Then
Range("B1") = "Y"
Else
Range("B1") = "N"
End If

End Sub
-------

Data:
________________
A B
________________
machine1 Y
machine2 N
machine3 N
machine4 Y
machine5 Y

Loop through a range
 

Try this

For iloopCells = 1 to lastcell
mname = "\\" & cells(iloopCells,1) & "\c$\fred.txt"
'*** now do dir test

next iloopCells


Lastcell will be the last cell you want to use. If the row is dynamic try

For iLoopCells = 1 to ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row


"Fred" wrote:

I need to check a group of computers for a certian file. The computer
names will be in column A. I got it to work with the below code for
cell A1.

How can I get it to loop through all the rows in column A and return
the Y or N to column B?

(If possible please respond to the group as well as by email as I use
Google and it takes forever to see responses)

-----
Sub checkfile()

mname = "\\" + Range("A1") + "\c$\fred.txt"

If Len(Dir(mname)) 0 Then
Range("B1") = "Y"
Else
Range("B1") = "N"
End If

End Sub
-------

Data:
________________
A B
________________
machine1 Y
machine2 N
machine3 N
machine4 Y
machine5 Y

Loop through a range
 

Thanks Bob,

But I get an error: "User defined type not defined" with the i As Row
highlighted.

Any ideas?

Fred



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Loop through a range
 

Dim i as Long
--
Regards,
Tom Ogilvy

"Fred Gerbig" wrote in message
...
Thanks Bob,

But I get an error: "User defined type not defined" with the i As Row
highlighted.

Any ideas?

Fred



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Loop through a range
 

Sorry, type, should be
Dim i As Long

--

HTH

RP

"Fred Gerbig" wrote in message
...
Thanks Bob,

But I get an error: "User defined type not defined" with the i As Row
highlighted.

Any ideas?

Fred



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Loop through a range
 

OK, Thanks to everyone I finally got it working. I am not sure if my
Error checking is in the best form but it works.

Thanks again!
Fred
----------
Sub checkfile()
Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To cRows

mname = "\\" + Cells(i, "A").Value + "\c$\fred.txt"

If Len(Dir(mname)) 0 Then

Cells(i, "B").Value = "Y"

If Err.Number = 52 Then Cells(i, "B").Value = "Machine Not Found"
On Error Resume Next
Else

Cells(i, "B").Value = "N"

If Err.Number = 52 Then Cells(i, "B").Value = "Machine Not Found"
On Error Resume Next

End If

Next i


End Sub




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!