Could anyone tell me if it is possible to get a file to save as follows:
filepathfolder called "YYYY"folder called "MMM"File.xls
where YYYY is the year and MMM is the month?
The folders YYYY and MMM may or may not exist. Is it possible to get excel to create them if they do not already exist?
I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5
in the adiacent cell(4,6) i want to put a function that:
1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value
2)Choose color font depending on value
3)Choose color background depending on value
I wrote this piece of
Public Function StatusResponse(AdiacentCell As Range) As String
Dim thisStatus As String
Dim ThisFontColor As Integer
Dim ThisbkColor As Integer
Select Case AdiacentCell.Cells(1, 1)
Case Is = 0
ThisbkColor = 2
ThisFontColor = 1
StatusResponse = "NO DATA"............
I am using XP and Excel 2003 and I have a workbook that I currently back up to a remote server whenever closing out the book. This back-up copy becomes a "controlled document" that should not be used or changed once it is created. Is there anyway to make it save as a "Read Only" file, so that no one can modify the back-up copy.
Currently I do it manually, but that is becoming tiresome as well as when I forget that nice little error message pops up and....
This is my code so far:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim Ans As Integer
Dim FName As String
Msg = "Would you like to make a back-up of this file?"
Ans = MsgBox(Msg, vbYesNo, Title)
Title = "Team Facilities"
If Ans = vbYes Then
FName = "P:Facilities Departmentackups2007" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs FName
End If
End Sub
Is it possible to save/send a workbook to compressed zipped folder by vba.
Using standard windows zip method (refer right click file option). As all users will have this feature, as i dont want to use a thrid party zip app.
So i and other users can automate zip of workbook into an email via macro.
I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.
Example:
On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?
I can't seem to find out how to use wildcards like "?" and "*" in the VBA- Replace("string","s*r","k") which should give "king"
View 3 Replies View RelatedIn column A, I have the following values:
build
chil rat
datcen
mang
nextg
ost rat
omp
sco rat
In column B, I need the formula so it shows values that if column A = "build", "nextg" or "datcen", then the value in column B is "nextg" and if column A = "chil rat", "ost rat", "sco rat", then the value in column B is "mig". For everything else, it should be blank.
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
=IF(;Delays!B1:B200="Greber")..............
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies View RelatedIn my spreadsheet below I want to be able to enter a sales number for January, the value of cell F2.
I want cells F3 thru F12 to automatically calculate according to the "Growth Per Month" value in cell H1.
Example: If January sales are 20,000, then February should calculate to 21,000 (january * 105%).
Excel Jeanie HTMLSheet1
E F G H 1 2010 Per Month Sales XXX 5% 2 XXX XXX 3 February $ 10,000 4 March $ 5,000 XXX 5 April $ 2,500 6 May $ 1,250 7 June $ 625 8 July $ 313 9 XXX $ 156 10 September $ 78 XXX 11 October XXX 12 November $ 20 XXX 13 December $ 10 14 $ 39,990
Spreadsheet Formulas Cell Formula F3 =F2*10*H1 F4 =F3*10*H1 F5 =F4*10*H1 F6 =F5*10*H1 F7 =F6*10*H1 F8 =F7*10*H1 F9 =F8*10*H1 F10 =F9*10*H1 F11 =F10*10*H1 F12 =F11*10*H1 F13 =F12*10*H1 F14 =SUM(F2:F13)
As shown in the below image. I have some data from A13 to I 13. Currently if I put the cursor on A13 and press DELETE button then all the formatting from A13 to I13 goes off. But the data still remain there.
Is it possible then when I press DELETE the content of A13 then all the data from B13 to I13 should also get deleted ?
Excel Jeanie HTMLSheet2 *ABCDEFGHI13Key FieldEmp 11 2 4 8 5 3 7 Excel tables to the web >> Excel Jeanie HTML 4
i just tried the below formulae but its not working. if Currency in col A and Currency col B is same it should be "Matched" if not same the "CCY Mismatch" and if "#N/A Sec" then "Security Not Found"
View 3 Replies View RelatedI need to prevent users from entering several rows of data seperated by a "/" or a "," or a blank space ""
We use unique identifiers (around 500,000 of them) so I cant really use a drop down box to populate and then make the user select.....
If a user populates a cell with "10005486 / 10045446" or "10005486,10045446" I want to highlight a cell red and then count the instances of red cells on another tab so I can track "errors"
I was intending to use conditional formatting when a "/" or "," or " " is used within a cell. If a user makes this error, I should probably include a msgbox saying why the entry they have made is invalid also....
I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.
"Available" means an employee has NO task at all.
I am having problems referrring to a range using .Cells
If I try to use the remarked code (red text), it crashes with VBA "400" error for which I cannot find any explanation. It also crashes with same "400" message if I use a cells reference to a numeric column instead of "y").
The code works using the black it loops a column in Sheet("Holidays") and loops a row in Sheet("Schedule"), then color fills 2 ranges in columns in Sheet("Schedule") when the values in 2 cells are equal.
I have attached a scaled down version of my Workbook with this code.
How do I disable the "Save" option from File Menu and allow only to "Save as"?
Excel 97/2000
I've been working on a macro that opens me up to a certain path so I can select a file.
This is the path sofar:
TheFile = "\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1"
My code takes me there, but I want to go a step further if it is possible.
\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1THE NEWEST CREATED FOLDER"
I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.
View 2 Replies View RelatedI have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
I am saving a .csv to a .xls and regardless of what method I use to save it in a macro there are quote marks around the filename in the dialog box. This only appears to happen if I start with a .csv as I have tested it with a .xls and there is no issue. I have tried using the following code so far.
View 4 Replies View RelatedI have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".
View 3 Replies View RelatedI have the folowing criteria :
1) If “Completed" , then highlight as green
2) If “Pending” , then highlight as red
3) If “In Progress” , then highlight as blue
4) If “X Required” , then highlight as green
I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.
I want to replace all defined range names in the sheet that start with "Street" to "Road"
For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"
I want to change them all in vba to Road-01", "Road-02" etc.
how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".
View 2 Replies View Relatedi have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)
View 4 Replies View RelatedOn the sheets where 'present' needs to be checked, after linking the checkbox to the cell it now says "TRUE" or "FALSE". How do I get rid of that? There is a formula that this affects also (just FYI).
View 3 Replies View RelatedI use the following code to get pivot table data source throught ODBC connect to CSV. However, when I chose hide item in "Page" fields, it showed "all" instead of "multiple items".
View 3 Replies View RelatedI can not figure out why the ChDir command doesn't change to "C:" as programmed, instead it defaults to (I'm guessing last active folder) on my "D" drive (Which is "D:Test")? My goal is to check if "PPG" exists in "C:MEASURE-6000", if not use "C:" as a default.
View 2 Replies View RelatedI have a spreadsheet with about 20,000 lines. (The number of rows fluctuate) I need a macro to remove (delete) rows that contains the phrase "Off Peak" and "Weekend"
I was setting it up in a loop but I don't know how to make the loop end after ALL the "Off Peak" and "Weekend" info is removed.
My macro just does a find then delete the row and then loop to the find again. Is there an easier way and can the "Off Peak" and "Weekend" statements be combined into one search
Do While
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.EntireRow.Delete
Loop
I can choose some cells in a sheet to protect them and prevent any one to change.
it's supposed to change and edit in the other cells.
the problem which I face when I try to use " merge and center" or to insert picture or autoshapes ..etc. in unprotected cells!