Receiving Error Message on Do Until EOF(1) line

[FusionRed]

I am receiving the error message:

"Run time error 52: Bad File name or error"

This occurs in the following code (I'm a novice trying to write a simple Do Until end of code macro):

Sub Change_Back_color()
'Change the interior cell color based on employee function.
Do Until EOF(1)
Select Case Resource
Case "Aiken Michael"
Cell.Interior.ColorIndex = 3
Case "Brown Jeanne"
Cell.Interior.ColorIndex = 3
End Select
Loop
End Sub

Can anyone help me understand the error message and why I'm recieving it/how to correct the error of my ways????

Thank you in advance for your help.

FusionRed

 

[Russ At Index]

Hi FusionRed.

Searched for your error and the only (relevent) peice of info
i could get was ....

"Is there an extra blank space at the beginning of your input cell" ?

Maybe helps you .....

 

[Andrew Poulsom]

Do you have a text file open as #1?

 

[FusionRed]

I had nothing else open at the time, so I'm unsure why the error message.

In the interest of time, I rewrote the code and got it working without the EOF(1).

Thank you for your time and expertise.

FusionRed

 

[just_jon]

There is no EOF statement in which refers to the Excel file itself - that's why Andrew asked if a .txt file were open [ they do have EOF(file number) ].

What is it you're trying to do - explain in words and we'll point you in the right direction.

 

[FusionRed]

I thought that I could use this to do a "do until" loop to the end of the file, I now note that I should have included the file name and path. So, now realizing my error, how exactly can I utilize EOF(1)? Is this only used when there is a text file????

Many thanks again.

fusion Red

 

[Andrew Poulsom]

What do you want to loop around? A Range?

What does the variable Resource represent?

 

[FusionRed]

I want to loop around a range and Resource represents the specific column to be altered.

In fact, Resource "column d" is the range I want to loop through.

FusionRed

 

[Andrew Poulsom]

Try this (written freehand so untested):

Sub Change_Back_color() 
'  Change the interior cell color based on employee function. 
   Dim Rng As Range
   Dim c As Range
'  From D1 to last used cell in column D
   Set Rng = Range("D1:D" & Range("D65536").End(xlUp).Row)
   For Each c in Rng
      Select Case c.Value
         Case "Aiken Michael" 
            C.Interior.ColorIndex = 3 
         Case "Brown Jeanne" 
            C.Interior.ColorIndex = 3 
      End Select 
   Next c 
End Sub

You could also use Conditional Formatting to colour the cells.

 

[OdinsDream]

You'd want to use the EOF function if you were processing text files with excel, but if you're actually working on an excel spreadsheet, you can use this kind of code to loop through cells in a range:

For Each Cell In Range("A1:B16")
   If Cell.Value > 10 Then
      MsgBox Cell.Value
   End If
Next Cell