Receiving Error Message on Do Until EOF(1) line

FusionRed

New Member
Joined
Nov 4, 2004
Messages
15
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Russ At Index

Well-known Member
Joined
Sep 23, 2002
Messages
685
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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 .....
 

FusionRed

New Member
Joined
Nov 4, 2004
Messages
15
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

New Member
Joined
Nov 4, 2004
Messages
15
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 :devilish:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

What do you want to loop around? A Range?

What does the variable Resource represent?
 

FusionRed

New Member
Joined
Nov 4, 2004
Messages
15
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this (written freehand so untested):

Code:
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

Well-known Member
Joined
May 19, 2002
Messages
541
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:

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

Forum statistics

Threads
1,147,626
Messages
5,742,215
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top