Column A (Plan Date) and Column B (Date Recieved) contain date value. If B2 is blank, A2 will maintain the date entered in it. However if B2 has date (either earlier or later than A2) it will supersede the date with the date value in B2.
View 9 RepliesI was trying to use Sheet1 (data input sheet) to collect 33 values and write them to Sheet2 as a row. I wanted the user to be able to make changes/corrections. So if the set of data with same date and shift number is entered for a second time, my macro should be able to over-write the existing row in Sheet2. Have been digging for days yet with no avail.
I'm using Win XP pro and Excel 2003.
Just wondering if it is possible to have a formula in a cell but over write it with other digits while preserving the underlying formula?
I can lock the sheet/workbook but cant edit the cells once this is done?
I'm hoping to be able to delete what ever I have overwritten and for the formula to then take effect again.
I am trying to create a macro which copies the data of one column every time I click on it. However, every time I click on it I overwrite the data I already copied. What do I have to do, that instead of overwriting my data it copies it to the next free column.
See example attached. Macro example.xlsx
if i run my macro that saves a sheet as a certain name and then prints it, it works, but if i run my macro and the file already exists it asks me to overwrite it. If i select yes it prints it and carrys on with the rest of the macro, (good). If i select no the macro fails. How can i get it so if i hit no, it runs the rest of the macro after the print command?
View 2 Replies View RelatedI am trying to set up a macro in excel that will include a save as. This should overwrite an old file. The problem is that I can't get the macro to overwrite. Instead the popup "Do you want to overwrite?" appears and I have to click "OK" manually. Can this be done automaticaly? What is the VB code for it?
View 9 Replies View Relatedin cell A1 i have the balance. i would like to record the balance every time it reaches a new high in cell B1. how would this be possible please using excel 2007
View 9 Replies View RelatedI've struggled to find the code to unzip files using VBA but I finally found it and it works (using Shell).
The problem is that I want to unzip all zip files in a folder but my zip files contain 3 files:
1 csv file that I want to keep and 1 xml and 1 txt file that I want to delete.
I can't just unzip everything and deal with the txt and xml later because the xml and txt files all have the same name. So if I don't do anything, I have a series of overwriting messages prompting me to click yes or no. So what I want is either to delete xml and txt one by one after they are created or get rid of the overwriting prompt. I've tried to put a kill or delete file just after the unzip command but it doesn't work - it's telling me that the txt file doesn't exist (should I add some sort of refreshing command ?) and I couldn't find anything on google about overwriting text or xml files.
I've just written a bit of looping vba script that generates an overwrite alert (i.e. "Output range will overwrite existing data. Press OK to overwrite..."). This is fine and was purposely built into the script, but I want to be able to automatically ignore these alerts and loop through the script unhindered.
View 4 Replies View RelatedI have the following code which copies a sheet to another workbook and renames it with the current date. However I need it to check if there is a sheet already existing in the other workbook with todays date first. If there is then it should delete the old sheet and copy the new sheet or just overwrite it.
View 4 Replies View RelatedIs it possible to overwrite an existing .csv file with
The command :
ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=xlCSV
Or does it need to be Killed first ?
Plus do I need any of the options such as Create Backup, AccessMode As etc.
I would like to code a Userform Command button to save data by overwriting a specific row of data. The specific row is to be based on data matching in column A and column B. I'll give an example
Worksheet = "Failures"
Row 1 - Column A - Column B - Value
Row 2 - 12/3/2012 - FOX ------ 23
Row 3 - 12/3/2012 - CEF ------ 24
Row 4 - 12/3/2012 - COT ------ 23
Row 5 - 13/3/2012 - FOX ------ 56
Row 6 - 13/3/2012 - COT ------ 23
Row 7 - 14/3/2012 - FOX ------ 26
I would like the code that would search for the specific row (e.g Row 5 discovered by searching for 13/3/2012 and FOX) in the "Failures" Worksheet and then overwrite it with the following data values from a Userform called "QC"
(TextBox1) (TextBox2) (TextBox3)
13/3/2012 --- FOX -------- 24
i have searched this and can't find exactly what i'm looking for. I am hoping some one can alter a piece of script i am using. See script below. Currently it adds a comment in a text box to a new row and i would like it to add it to the end ( cell 15) of the last row used, not a new row. I am a newbie to VBA and am self teaching so the obvious is not really obvious.
Private Sub cmdErrorButton_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Log Book Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(x1up).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 15).Value = Me.txtError.Value
ws.Cells(iRow, 14).Value = Now()
End Sub
I have a spreadsheet where there is an option to either select a pre-priced item from a drop down list or type in manual entries. If the data is entered manually, I have included a formula to obtain cost data. For example, column A has the number of labor hours and column B multiplies Column A by $500. However, if a person chooses to select a pre-priced item from the drop down menu, can the pre-entered data from the VLookup list overwrite the formula? I do want it to overwrite if it is possible for it to do so.
View 2 Replies View RelatedI have a spreadsheet that's generated from a download. I need to modify one column of cells based on multiple criteria. Started to try with a nested If statement, but soon passed the limits allowed. Not sure if some kind of VLookup or VBA formula. To summarize what I'm looking for: If column A has a value of any of the following (10D, 11Z, 10Z, 2CS), then I need to change the Rate in column D based on the Years left to maturity in column B. The Rate percentage to be used is based upon the table shown in the attached spreadsheet. In addition, there is an exception if Column A has a value 2CB and an inventory # of 9300276 or 9300277 in column C, then I need to change the rate in column D based on the table as well. Otherwise the rates stay as originally imported.
View 3 Replies View RelatedWhen I use a macro to refresh all pivot tables, I get a whole bunch of messages popping up, asking me if I want to overwrite the existing cells. Is there a way that I can say no to all of these messages automatically?
View 6 Replies View RelatedI have an Excel sheet that I convert to PDF --here is Example of the name after PDF creates"Morning Report_08.16.14" , When it runs it just overwrites the file automatically if one was already on that date - I would like it to prompt to overwrite & if they choose 'yes' to overwrite the file and if not they can change the name If the new one is in addition to the one that already exists and I want it to save the new file with in the same folder.
Also when the current code is run if the PDF was left opened it stops the macro - Is it possible to see if that file is open and if so close it down so the code will work or is it possible to overwrite the file when open.
[Code].....
I want to be able to enter a value into a cell (within a specified range) and have it automatically multiply by a set value and overwrite the original entry with the result so making it all happen within one cell. The set value would be another cell on that sheet.
So the range for example is "H17:H74" and the value I want to multiply by is "D8"
Is this possible?
I've looked at another forum but it wasn't working when I wanted to define the set value as a cell.
I can't get the Displayalerts = False to work as it's supposed to when analyzing data running the correlation add-in. (ATPVBAEN.XLAM!Mcorrel)
For each loop the alert is displayed which I don't want it to.
The code below is just an example. I have several rows which I will loop through and therefore the Displayalerts=False must work.
Sub test4()
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View RelatedI have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View RelatedI presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
Im currently writing a payroll sheet within excell and trying to write an IF statement to make a cell blank if referring to another blank cell but it is showing the formula is incorrect. Below is the following IF statement that im using;
=IF( F4 <97, AM4*0.8, IF( F4 >=97, AM4*1.1, IF( F4=" ";" "; AM4)))
My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).
The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:
Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))
The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".
[URL] ...........
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies View Relatedin one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies View Relatedusing a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..
View 9 Replies View Related