Im looking for code for a button that asks for a Record number, and it will delete the record. But the record number is written in cells as 'Record No.1' 'Record No.2' etc. But i want the messagebox to ask for a single number and it would delete that record. Now the position of the records is what will be the difficult bit about this code, but it has a pattern.
For Record No.1 i need deleting cells C2:J100
For Record No.2 i need deleting cells L2:S100
For Record No.3 i need deleting cells U2:AB100
If anyone could give me a bit of code that works for these 3 then i could waste a load of time filling the rest of them out.
if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.
I have recorded macros in the attached file to highlight cells that contain matching initials by using conditional formatting on the first cell and copying it to the remaining cells. The problem is that if rows are inserted above that change the first cell reference then the macro no longer works properly. Is there a way to use a relative cell reference within the macro? The macro buttons are in cells A4 to A13. I have no VB experience and created the macros by recording keystrokes and using tips found in this forum.
Hello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.
I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.
In my attachment, I had try to create a PO system that will show a dialouge box and capture input data of the dialogue box into the worksheet.
However, I am not able to make the macro recognise any manual deletion from the worksheet (e.g. delete any row in between the full data). Hence, when I input new data, the row will start from the last register counting of rows (i.e. Range("g1") in my attachment.) and it will leave a blank row after the last row of data and so on...
I will also like to know if I need to fix the "date" input to select from a "calendar" pop-up and input the date format into (e.g. year into column a, month into column b and day into column c) 3 separate columns. Is it possible?
So essentially I need a VBA Macro code for Excel 2003 that can take raw data that is input every month via a text document and move it to the next sheet in the workbook. This data is essentially broken down by a "platform" in the raw data section ("P") with monthly totals for each month but it is broken down by platform in total which goes in descending monthly order on the "dat" tabs (which is the final place for the data).
So what needs to happen is the data for the last two years must go from the "P" tab to the "PDat" tab under the correct platform and the correct spot for the newest month. The data that is input each month is in the same format month to month, just starting two rows down from the last month.
At the end of two years, the oldest year of data must be deleted and the other data moved up to the vacant area so that the sheet never grows too large (i.e., at the end of this year 2012 will be deleted and all the data will need to get moved up).
I have a workbook that contains 2 worksheets. The first worksheet contains all the data. The second sheet contains formulas that reference (absolute for all) the first sheet.
I run into problems (actually the users do) when the data set is updated with new information. Sometimes the users will delete all the data rows first and then copy the new data to the blank cells. When they do this, it messes up the formulas with #REF! error. Is there a way to prevent this? or is there a way to hard code the formulas so I don't get this error?
I have a cell with data validation: list, that allows values contained in named range. I want to disallow to delete contents of this cell (with 'del' key, or when i edit cell, delete text and then press 'Enter'). Is it possible to do with formula or VBA
On some of my worksheets, the slider bar on the right has become a small square rather than a long rectangle. This causes this slightest shift downward to jump through large amounts of rows. Excel seems to think there must be data down there I need. How do I get rid of empty cells at the bottom of a worksheet so that this no longer happens?
I have completed an automated time sheet but have realised that there are some days where overtime entries make it go wrong.
There is one column where you enter an overtime shift when the cell has an entry I need all the other calculations to be zero
A1 START TIME B1 END TIME C1 HOURS WORKED ARE CALCULATED using a formula D1 OVERTIME COLUMN ( IF AN ENTRY IS PUT IN HERE MANUALLY I WANT C1 ALWAYS BE 0
I tried various ways with isblank but just get true or false.
This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
I have a workbook with two macros that the user can run individually. They unfortunately take a fair amount of time to complete (approx 1 hour each) so I implemented some timing related code that at the end of the macro completion, it notifies them with a msgBox indicating how long it took to run. With that said, I would like to allow the user to run a macro that simply calls the other two so that they can kick it off at the end of the day and they both will be finished by morning. I created a new macro that simply makes a call to each of the other macros individually. The problem is, my msgBox that displays after the first macro completes, waits for "OK" acknowledgement which defeats the whole purpose of being able to run them both via one macro. I have tried to include Application.DisplayAlerts = False prior to calling the first macro but it is not working. Here is an example of what I am trying to do.
Sub runAllMacros() Application.DisplayAlerts = False Call compileBookData Call compileLaborData Application.DisplayAlerts = True MsgBox "All Rollup Macros have completed execution."
I have a code where column K is not responding to a value being deleted out of column J. I've highlighted the portion of the code dealing with this. Deletion should trigger the worksheet_change event to clear out column K as it's supposed to. how to get deletion to take effect?
This is part of a macro in a worksheet_change event. When a cell in column J gets deleted by a user, the corresponding cell in column K should also clear. But it's not responding to the delete.
It DOES clear when the other 2 criteria are met (.cells(1,10) = 0 and .cells(i,5) <> "Annuity"). The worksheet_change event should pick up on the cell deletion, but it's not. And column J is already a trigger for the macro to run, so I'm not sure what's going on. Either the trigger is still wrong, the isempty(.cells(i,10)) is not correct syntax, or this event just doesn't respond to cell deletion.
I have a worksheet with multiple entry boxes in a row. Depending on the value in one row, another row will either yield the result from a formula, or remain blank, and this cell needs to be locked. I was able to do this by first putting the formula into the destination cell, and then using custom data validation that says the cell value = "". So this prevents anyone from typing in that cell, no matter if it yields the formula result or must stay blank. What it doesn't protect against is someone deleting the contents of the cell. The reason I don't want to protect the cell is because I want people to be able to highlight the whole row past that cell and delete it all at once. So either using Data Validation, a formula, or VB programming I need to not allow someone to do ANYTHING to that cell, even delete the contents, without locking the cell. Hopefully it can be done, thanks!
For example, if E17 = "X", then K17 = INPUT!$M$28*J17. If E17 <> "X" then K17 is blank. Either way, I don't want any typing or deleting of K17. This applies for range K17:K91, and corresponding E17:E91.
is there a way to use the functionality of 'MsgBox' but within a function instead of a macro? I use: MsgBox "You must enter a Acquisition Date", vbCritical, "Error Message" now I would like to use it within a funtion.
I can record a macro that will sum a column of data at the end of data in that column - simple; my problem is getting it to work on other workbooks that have a different amount of rows in it. I tried recording the macro using the relative button as well as without using it, but either way I do it, I can never get it to work on a different workbook. For example, I record the macro, then remove a couple rows after to simulate running on another workbook, and then when I run it, the sum gets put in the cell that used to be the end of the column instead of the real end of the column…Same is true if I add some rows. I don't know if I am using the right terminology below (OK, I know I'm not) but it gets the message across best when I say I think I need something that will do the following (substitute "code" for "make believe code"):
1. Go To cell F1 2. Go To last entry In this column 3. Move down one cell 4. Sum F2:Fx (where x = one cell above current selection - the last populated cell In the column With data In it before the sum)
Instead of this (what I have right now)................
I have an application (in xl'97) where users enter a date in N4. =TODAY() is in C7 as the date of entry. I need some vba to show a message should C7-N4 be >10d.
1. Calculate (perform F9 manual calculation) 2. Display a msgbox (MsgBox "Enter a city or zip code to calculate mileage.", vbExclamation, "Travel Schedule")
Is there an operator that lets you string multiple macros or do they need to be recorded separately and recalled in one macro assigned to the button?
How would I add a YesNo MsgBox to my existing macro that I have set up through a commancd button? I would like the user prompt to ask the following ? ---> "Would you like to move the data sheet forward 1 week?". Then if "Yes" is selected, run the following macro. If "No", end the subroutine.
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.
This one is a two parter. Part I is a bit of a necessity and Part II is a nice to have that would save me a boatload of time.
Part I: I need to assign a macro to a specific object in a cell. I've seen the code for targeting a static cell, but my data can be filtered and sorted.
For example, I have a cell in I6 that has text "Partial Deployment" that I want to run a macro on. If I were to sort alphbetically, this cell could end up as I20.
How to assign a macro to the text inside the cell? Also the text is not unique.
Part II: I would like to write a macro using relative referencing for similar to the below:
When user clicks on said object from Part I, the macro reads the corresponding column in that same row and users that to filter data on a separate spreadsheet.
Example: User selects "Partial Deployment" in cell I6, the macro references cell E6, copies the number "191", and uses that number to filter data on a separate spreadsheet.
This recorded macro inserts a line below the cell that active when it was first recorded. It then copies some text and a formula to the line that was created.
Sub Macro11() Rows("10:10").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A9:E9").Select Selection.Copy Range("A10").Select ActiveSheet.Paste Range("H9").Select Application.CutCopyMode = False Selection.Copy Range("H10").Select ActiveSheet.Paste Range("F9").Select End Sub
If you run this macro again at a different cell position, it goes back to the first starting position and repeats the same action. I see that the cell references from the first recording are fixed in the macro. I can't figure out how to make the macro use the new current cell position as the starting positiion when it runs again.
