Auto-save file after check that cells have data

[Glio]

Thanks to RoyUK yesterday I now have three cells checked to make sure data is in them and then the code is meant to save the file with some of the data from those three cells. The checks work but now the save part doesn't! If I comment out two of the checks the save does work.

Any ideas please

Linked to http://www.excelforum.com/showthread.php?t=625320

Please Login or Register  to view this content.

[mudraker]

Your problem is that you have your If statements nested inside the previous If statement. The workbook would save whenever Range("ExIncOp") was not empty


Try this

Please Login or Register  to view this content.

[Glio]

Mudraker,

Thanks for your solution which certainly allowed the checks to be made before the save

Unfortunately it is not now putting the first two letters of the Range ExIncOp at the start of the file name.

Please Login or Register  to view this content.

The ActiveWorkbook line did originally start with " & "Ex" ... and I thought it was picking up the variable i.e. the first 2 letters of whatever was in Range ExIncOp. However it is justing saving the filename as Ex...

I have tried to add Range("Ex").Value but that doesn't work either.

The range will only have three validated options so should return only Ex, In or Op.

What am I doing wrong?!

[mudraker]

When looking at your code it is very hard to know if it is correct or not when you use a mixture of Cell reffernces & named Ranges


In this command is "Ex" the correct named range or should it be "ExIncOp" or even C6 cell reference

Please Login or Register  to view this content.

Also when testing for cell contens you speciy Sheet names but you do not in the code above so it will be looking for range("Ex") on the Activesheet

[Glio]

Mudraker,

Cell C6 is the named range ExIncOp, which has validated three options.
I thought I understood that the 6th line of code

Please Login or Register  to view this content.

registered a variable which is then defined as the first two letters of whatever is in ExIncOp by

Please Login or Register  to view this content.

The value of Ex is then used as the first part of the file name

That was what I hoped was happening!

Thanks very much for your assistance.

[mudraker]

In your original macro the EX part of the command shown below is enclosed in " which means it is a string not a variable

Please Login or Register  to view this content.

To use the Ex variable you need

Please Login or Register  to view this content.

I also prefer to build the filename & path into a string before attrempting to use it when saving the file, especially when it is not giving me the results I am expecting

Please Login or Register  to view this content.

[Glio]

Mudraker,

Thanks for your response. As you can see I have taken up your proposal to define sFile and have added in some MessageBoxes to tell me what the variables are before they are used. This shows me that Ex is not being found

Please Login or Register  to view this content.

I note that if I type the formula Left(ExIncOp, 2) in a cell I get the correct response, however when I put it in the code I get nothing and if I enclose ExIncOp in "" I just get the first two characters of the range name i.e. Ex.
I have tried to substitute line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I can see that an easier way round this will be to have a hidden cell on the worksheet with a formula to create the file name!

[mudraker]

Oops I overlooked the error in the code
this

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

It is easy to mix up variable & named ranges which is one reason I do not used named ranges

[Glio]

Mudraker,
That's great! Something so obvious!
Thanks very much

[mudraker]

That is one of the pitfalls of writing code
We can be blind to

Something so obvious

That error was there from your 1st post & I kept overlooking it

[jindon]

Please Login or Register  to view this content.