Error 91 - Little Help 1

[Vamphyri]

I have written the following code. It's rather simple, and I SHOULD be able to figure out why it's throwing an "Error 91" at me. However, I think my wife has taken to beating me about the head with large blunt objects while I sleep and cannot figure this one out. I think it has to do with my declaration of a range.

The code is SUPPOSED to check and see if the range I have specified is blank. If so, it would run the rest of the code. If not, it would exit the sub. I plan to add a msgBox later.

Any pointers would be appreciated.

Code follows:

[Private Sub ButtonXfer_Click()

Dim rng As Range
rng = "Master_First_Row"
Application.ScreenUpdating = False
If rng = "" Then
Sheets("Old").Range("Old_Employer_Name").Copy Sheets("Master").[C4]
Sheets("Old").Range("Old_Employer_Address").Copy Sheets("Master").[C5]
Sheets("Old").Range("Old_Employer_City").Copy Sheets("Master").[C6]
Sheets("Old").Range("Old_Employer_State").Copy Sheets("Master").[C7]
Sheets("Old").Range("Old_Employer_Zip").Copy Sheets("Master").[C8]
Sheets("Old").Range("Old_Employee_Name").Copy Sheets("Master").[A13]
Sheets("Old").Range("Old_Employee_Zip").Copy Sheets("Master").[C13]
Sheets("Old").Range("Old_Employee_DOB").Copy Sheets("Master").[D13]
Sheets("Old").Range("Old_Family_Status").Copy Sheets("Master").[G13]
Sheets("Old").Range("Old_Enrolling_Status").Copy Sheets("Master").[H13]
RecolorMaster
Else
Exit Sub
End If
Application.ScreenUpdating = True

End Sub]

Thanks a ton


In the immortal words of Socrates, who said:
"I drank what?

 

[Bong]

Dim rng As Range
rng = "Master_First_Row"
I think set rng=range("Master_First_Row")
and then
If rng = "" Then If rng.name = "" Then

_________________
Bob Rashkin

 

[fumei]

Dim rng As Range
rng = "Master_First_Row"

Look at it. Look at it again. Does the second line look like a Range? It is a string. Also, what is Error 91?

Object variable or With block not set.

You need to use:

Set rng =

However, if you put:

Set rng = "Master_First_Row"

you will get another, different error. A Type Mismatch, as you are declaring rng as a range object, but "Master_First_Row" is NOT a range, it is string.

faq219-2884

Gerry
My paintings and sculpture

 

[Vamphyri]

OK! I have come up with the following, but now am getting a "Type Mismatch" error.

[Private Sub ButtonXfer_Click()
If Worksheets("Master").Range("Master_First_Row").Value = "" Then
Application.ScreenUpdating = False
Sheets("Old").Range("Old_Employer_Name").Copy Sheets("Master").[C4]
Sheets("Old").Range("Old_Employer_Address").Copy Sheets("Master").[C5]
Sheets("Old").Range("Old_Employer_City").Copy Sheets("Master").[C6]
Sheets("Old").Range("Old_Employer_State").Copy Sheets("Master").[C7]
Sheets("Old").Range("Old_Employer_Zip").Copy Sheets("Master").[C8]
Sheets("Old").Range("Old_Employee_Name").Copy Sheets("Master").[A13]
Sheets("Old").Range("Old_Employee_Zip").Copy Sheets("Master").[C13]
Sheets("Old").Range("Old_Employee_DOB").Copy Sheets("Master").[D13]
Sheets("Old").Range("Old_Family_Status").Copy Sheets("Master").[G13]
Sheets("Old").Range("Old_Enrolling_Status").Copy Sheets("Master").[H13]
RecolorMaster
Application.ScreenUpdating = True
Else
MsgBox ("Master Census NOT Empty! Process Terminated!")
Exit Sub
End If


End Sub]

I think it has to do with the range of "Master_First_Row" being a range of A13:I62?

I tried changing the definition of the range to a single cell and the sub ran fine.

I need the sub to make sure all cells in the range of "Master_First_Row" are empty prior to running the rest of the code.

Any ideas?

Thanks for the quick response by the way.




In the immortal words of Socrates, who said:
"I drank what?

 

[ck1999]

why not do a loop

for each cell in range ' need to look up correct syntax
if cell <> "" then
rangeempty = false
end if

then use

if rangeempty then



else
msgbox


ck1999

 

[fumei]

You need to do a little looking up about object types.

"I think it has to do with the range of "Master_First_Row" being a range of A13:I62?"

Good thinking.

faq219-2884

Gerry
My paintings and sculpture

 

[Vamphyri]

ck1999 & fumei,

I think I'm beginning to understand.

I have changed my code to the following:

Private Sub ButtonXfer_Click()
    Dim Master_First_Row As Range
    Set Master_First_Row = Worksheets("Master").Range("C4:I10", "A13:I62")
    For Each Cell In Master_First_Row
        If Cell <> "" Then
         rangeempty = False
        End If
    Next Cell
    If rangeempty Then
            Application.ScreenUpdating = False
            Sheets("Old").Range("Old_Employer_Name").Copy Sheets("Master").[C4]
            Sheets("Old").Range("Old_Employer_Address").Copy Sheets("Master").[C5]
            Sheets("Old").Range("Old_Employer_City").Copy Sheets("Master").[C6]
            Sheets("Old").Range("Old_Employer_State").Copy Sheets("Master").[C7]
            Sheets("Old").Range("Old_Employer_Zip").Copy Sheets("Master").[C8]
            Sheets("Old").Range("Old_Employee_Name").Copy Sheets("Master").[A13]
            Sheets("Old").Range("Old_Employee_Zip").Copy Sheets("Master").[C13]
            Sheets("Old").Range("Old_Employee_DOB").Copy Sheets("Master").[D13]
            Sheets("Old").Range("Old_Family_Status").Copy Sheets("Master").[G13]
            Sheets("Old").Range("Old_Enrolling_Status").Copy Sheets("Master").[H13]
            RecolorMaster
            Application.ScreenUpdating = True
            MsgBox ("Transfer Complete!  Please save the file.")
        Else
            MsgBox ("Master Census NOT Empty!  Process Terminated!")
            Exit Sub
        End If
End Sub
[code]

  The code runs without any errors,but displays the msgBox for non-empty cells.

I think I'm on the right track, just having a bit of difficulty.

Any hints would be appreciated.



In the immortal words of Socrates, who said:
"I drank what?

 

[ck1999]

Can you explain this better


The code runs without any errors,but displays the msgBox for non-empty cells.

1. which msgbox you have 2. 1 should run in cells are not empty ?

2. Does it transfer the information?

NOTE: Right before the for loop put rangeempty = true

ck1999

 

[Vamphyri]

ck1999,

The code displays the "Master Census NOT Empty! Process Terminated!" msgBox. No it does NOT transfer the information. I'm rather confused.

I tried using the "rangeempty = true" statement. It DID transfer the information. However, when I pressed the button again (it should have stopped the code and displayed the "Master Census NOT Empty! Process Terminated!" msgBox) it simply transferred the information again.

What am I doing incorrectly?

Thank you for your patience.

Any hints, tip or tricks you can suggest would be great!


In the immortal words of Socrates, who said:
"I drank what?

 

[Vamphyri]

ck1999,

Just a thought. Could the problem be with my declaration of the range "Master_First_Row"? I would think the code would not run at all if I had declared it incorrectly, but I also thought Enron was a great investment...


In the immortal words of Socrates, who said:
"I drank what?

 

[ck1999]

I may have messed this up change this

If Cell <> "" Then

to

if cell = "" then

and see if it works then

ck1999

 

[ck1999]

This range
Worksheets("Master").Range("C4:I10", "A13:I62")

will give you the range from a4..I62.

to tell what the range would be use this
Worksheets("Master").Range("C4:I10", "A13:I62").select

What are the cells you want to check?

You may need to use to loops 1 for each range

    Set Master_First_Row = Worksheets("Master").Range("C4:I10")
    For Each Cell In Master_First_Row
        If Cell = "" Then
         rangeempty = False
        End If
    Next Cell
if rangeempty then ' no reason to do this loop if cell there is a cell already empty
    Set Master_First_Row = Worksheets("Master").Range("A13:I62")
    For Each Cell In Master_First_Row
        If Cell = "" Then
         rangeempty = False
        End If
    Next Cell
end if

ck1999

 

[Vamphyri]

ck,

Changed the code as suggested above.

Now code is displaying the "Master Census NOT Empty! Process Terminated" msgBox even though I have deleted all information from the range.

Any other ideas?

At this point I am willing to try anything.

Thanks a TON for the help you are providing.


In the immortal words of Socrates, who said:
"I drank what?

 

[ck1999]

I think I am confusing my self

Lets start over and try this

Private Sub ButtonXfer_Click()
    Dim Master_First_Row As Range
    dim rangeempty
    Application.ScreenUpdating = False
    rangeempty=true

     Set Master_First_Row = Worksheets("Master").Range("C4:I10")
    For Each Cell In Master_First_Row
        If Cell <> "" Then
         rangeempty = False
        End If
    Next Cell
if rangeempty then
     Set Master_First_Row = Worksheets("Master").Range("A13:I62")
    For Each Cell In Master_First_Row
        If Cell <> "" Then
         rangeempty = False 
         exit for 
        End If
    Next Cell
end if
    If rangeempty Then
            Sheets("Old").Range("Old_Employer_Name").Copy Sheets("Master").[C4]
            Sheets("Old").Range("Old_Employer_Address").Copy Sheets("Master").[C5]
            Sheets("Old").Range("Old_Employer_City").Copy Sheets("Master").[C6]
            Sheets("Old").Range("Old_Employer_State").Copy Sheets("Master").[C7]
            Sheets("Old").Range("Old_Employer_Zip").Copy Sheets("Master").[C8]
            Sheets("Old").Range("Old_Employee_Name").Copy Sheets("Master").[A13]
            Sheets("Old").Range("Old_Employee_Zip").Copy Sheets("Master").[C13]
            Sheets("Old").Range("Old_Employee_DOB").Copy Sheets("Master").[D13]
            Sheets("Old").Range("Old_Family_Status").Copy Sheets("Master").[G13]
            Sheets("Old").Range("Old_Enrolling_Status").Copy Sheets("Master").[H13]
            RecolorMaster
            Application.ScreenUpdating = True
            MsgBox ("Transfer Complete!  Please save the file.")
        Else
            MsgBox ("Master Census NOT Empty!  Process Terminated!")
            Exit Sub
        End If
End Sub

see if this works

sorry for the confusion

ck1999

 

[Vamphyri]

ck,

Correction. I posted my last reply before the website was updated with your second posting.

I have made the changes you suggested in your last post. I changed the line "rangeempty = False" to "rangeempty = True" as the code should check to see if the cell = "" (that would be blank, correct?).

The code will copy the information just fine. However, if I run the code again (the cells are obviously no longer blank) it simply copies the information again.

Is there a different way to do this? Am I going about this a complete ridicuous way?

Thanks



In the immortal words of Socrates, who said:
"I drank what?

 

[Vamphyri]

ck,

SCORE!!!!!!!

Thank you very much. I appreciate the help.

Out of curiosity, what does declaring the "rangeempty = true" do for the code?

BTW. Star for you simply for being patient. Plus you solved the problem.

Thanks again.

Chris


In the immortal words of Socrates, who said:
"I drank what?

 

[ck1999]

I am glad your problem is now resolved.

I think the biggest issue was the loop originally was checking from a4 to I62.

It makes sure it is true before you run the loop code.
This way you know it starts off as "True" so the only way it would be false is if a cell was not blank.

ck1999

 

[Vamphyri]

Gotcha!

Thanks, again.


In the immortal words of Socrates, who said:
"I drank what?