I'm looking for a way to ensure that users of a spreadsheet have filled in all required cells. VBA code which will prompt if a cell is blank which will activate on a button click
All cells are 'Named ranges' so hoping there's a way in which I can point to all named ranges and if they're blank display the below error message.
"Please ensure you have filled in all required fields"
I've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)
Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long) age = begAge endAge = begAge + t Dim holder As Long Dim RMDnow As Long Dim RMDall As Long hat = 70 If endAge < 69 Then Else If begAge <= 69 Then Do While hat < (endAge) RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False))) RMDnow = (1 - marginalTax) * RMDnow...........................
I have written some code that copies certain cells from a spreadsheet constructed form back to a master spreadsheet database. The idea being to prevent users of the form from accessing the database and mucking it up. I write it the long way round and am now trying to modify it to not have to open and close the database everytime BUT whenever I change it I get errors that I think relate to the code not understanding the change of object...
' 3. send updated data to the database (all yellow boxes will update)
' set parameters for cells to copy from Dim r As Long, e4 As Long, e6 As Long, e18 As Long, e20 As Long, e22 As Long, e24 As Long, _ e26 As Long, e28 As Long, e30 As Long, e32 As Long, e34 As Long, e36 As Long, e38 As Long, _ e40 As Long, e42 As Long, e44 As Long, e46 As Long r = Range("D2") e4 = Range("B4") e6 = Range("B6") e18 = Range("B18") e20 = Range("B20") e22 = Range("B22") e24 = Range("B24")
The little blocks of code go on for 17 open/closes!
I have a workbook that starts off with the following sheets:
MAIN_PAGE - Where filter criteria is selected via checkbox and PLOT button
RAW_DATA - unfiltered raw data
After running a series of macros tied to the PLOT button, two new sheets are created:
FILTERED_DATA(HIDDEN) - where the filter criteria from the MAIN_PAGE is applied, and the filtered data is stored.
CHART - The plot of the hidden FILTER_DATA sheet.
Every time the PLOT button is pressed, the the macro searches for the FILTERED_DATA and CHART sheets, and if present, clears and overwrittes with the new appropriately filted data. This works and suits the user's needs 95% of the time. However....
QUESTION If the user decides to keep the old CHART and FILTER_DATA as well as the new FILTER_DATA and CHART, I need something to differentiate the two charts and data sheets. My thought was to have the user rename the CHART. The problem is that because the FILTERED_DATA sheet is hidden (and has to stay that way), how can the name change of the CHART be carried over onto the hidden FILTERED_DATA sheet, therefore keeping the plot and it's data in tact? For example, if I append the name CHART with a 1 to make it CHART1, how do I automatically change FILTERED_DATA to FILTERED_DATA1?
I am trying to write some code that is linked to a Command button. The code in the command button is in my workbook called "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" but then I have another workbook called "MF BANK EXPOSURE SUMMARY.xls" that I want to do some work with - namely delete blank columns and it is here that I am having the problem.
My problem is this: my code module is contained in my project "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" and the columns that I want deleted are in the "MF BANK EXPOSURE SUMMARY.xls". Now even though I believe I have activated the "MF BANK EXPOSURE SUMMARY.xls" workbook the action, that of deleting the blank columns is performed on the "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" workbook. Why?? Here is the code that I am using:
Sub Commandbutton() 'ASK FOR DATE AND SET IT IN THE REPORT Workbooks("MF Consolidated ACTUAL DAILY REPORT - Dev.xls").Worksheets _ ("Seg and Non Seg Bank Summary"). Range("I1") = Application.InputBox("PLEASE ENTER REPORT DATE IN THE DD/MM/YYYY FORMAT")
'ACTIVATE MF BANK EXPOSURE SUMMARY Workbooks("MF BANK EXPOSURE SUMMARY.xls"). Sheets("Seg and Non Seg").Activate Dim iCol As Integer Dim Isheet As Integer With ActiveSheet.UsedRange................
I have the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB: Sub AgeValidation() Dim age As Integer Dim dob As Range Dim AgeMsgAnswer16 As String Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
I want a macro fill the value which is on the upper cell.
eg:If A1= Code and A2 is blank, A2 should be filled with A1 value which is 'Code'.
In the attached, I want C3,C4,C5 to be filled with 'A' which is in C2,
likewise I want C7,C8 to be filled with 'B' which is in C6 and C11 to be filled with 'C' which is in C10, likewise the data has to be filled in entire C col.
What I required is either a Macro or Code for formulas in column 'F' in the attached spreadsheet that correspond to the SUM of each description and divided by 37.5 e.g. in F10 the formula should be =D10/37.5 the formula should be F12 D10/37.5 and so on all the way down the Pivot table
My problem is as the amount data increases on the Data Tab the formulas in column 'F' will become out of line with the corresponding Sum of each description so I guess I need some code or formula that check every time the Pivot table is refreshed.
I have a grouping and subgrouping of rows in my worksheet and I want to use the minimum value from each group and subgroup and use those values in a report on a separate worksheet using vba. Here's the situation
What I'm basically trying to do is make a code that looks at where the "x" values are in my range and if the month matches the current month, open a pop up that says "Service Required: Equipment Name" when the file is opened.
If it's any easier, an actual date could be written instead of an x.
An alert system for service required if you will. I have attached a file.
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
I need the macro to put Rec sheet data for each bank in Sheet Summary under the Each bank header, the macro needs to figure out the number of rows required and adjust if necessary as the rec data is not fixed. See below examples
Rec ABCDEFG5CategoryRecAccountValue DateEntry DateTypeAmount6CITIBANKCASH AMP1WTGDPFUSD1-Oct-075-Dec-08LDR-2,203.677CITIBANKCASH IMS02WTFPT2USD24-Jun-0817-Dec-08SCR2,001.388JP MORGANCASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LDR-3,608.919RBSCASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LCR3,889,582.9410BOACASH DESWTFPT2USDSUSP16-Sep-0829-May-09LDR-1,557,609.9511BOACASH IMS09ZPBRKQUSD17-Sep-0817-Dec-08LCR4,554,511.1112 13 14 Excel tables to the web >> Excel Jeanie HTML 4
Summary ABCDEF6CITIBANK 7RecAccountValue DateEntry DateTypeAmount8 9JP MORGAN 10RecAccountValue DateEntry DateTypeAmount11 12RBS 13RecAccountValue DateEntry DateTypeAmount14 15BOA 16RecAccountValue DateEntry DateTypeAmount17 18 19 20 21 22 Excel tables to the web >> Excel Jeanie HTML 4
Summary ABCDEF4 5 6CITIBANK 7RecAccountValue DateEntry DateTypeAmount8CASH AMP1WTGDPFUSD1-Oct-075-Dec-08LDR-2,203.679CASH IMS02WTFPT2USD24-Jun-0817-Dec-08SCR2,001.3810 11JP MORGAN 12RecAccountValue DateEntry DateTypeAmount13CASH DESWTFPT2USDSUSP15-Sep-084-Sep-09LDR-3,608.9114 15RBS 16RecAccountValue DateEntry DateTypeAmount17CASH DESWTFPT2USDSUSP3970640060LCR3,889,582.9418 19BOA 20RecAccountValue DateEntry DateTypeAmount21CASH DESWTFPT2USDSUSP3970739962LDR-1,557,609.9522CASH IMS09ZPBRKQUSD3970839799LCR4,554,511.1123 24 25 26 Excel tables to the web >> Excel Jeanie HTML 4
I have data from Columns A to D. I want to do the following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
I am trying to use vlookup in vba but i receive an error '1004' "Unable to get the VLookup property of the Worksheetfunction Class". I need a way around this. I have ID numbers in the first column and I am trying to input the name of the table it corresponds to in column "O". I have 4 different tables. I am using For Each cell in Selection to go through every cell by using If statement along with VLookup. As soon as the function is forced to go to the next IF selection, it gives me the error.
Range("O3:O" & myCount + 2).Select For Each cell In Selection If IsEmpty(cell) Then If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _ Workbooks("Warehouse Inventory"). Sheets("TWGReports").Range("$a$2:$z$1000"), 1, False) Then cell.Value = "TWG Facility" If cell.Offset(0, -14) = Application.WorksheetFunction.VLookup(cell.Offset(0, -14), _ .....................
I need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
I have a column containing ranges of apha-numeric codes that I wish to expand into an adjacent column as the following example:
AL 0-4 AL 0
E 4 AL 1
H 16-23 AL 2
[Code] .....
I would like the VB to distinguish and expand the different range permutations from the first column, leaving single codes intact and separating comma separated codes as shown. If it makes it easier, I manually split out the comma separated codes onto separate rows.
code to have check marks appear in multiple non-adjacent columns in a worksheet by double clicking.
The code I am using works for a single column : (The named range "Checkboxes1" is D4:D100)
VB: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Count > 1 Then Exit Sub If Intersect(Target, Range("Checkboxes1")) Is Nothing Then Exit Sub
[Code]......
However I also need the code to work on two other named ranges "Checkboxes2" (E4:E100) and "Checkboxes3" is G4:G100.
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2() Dim WhatToPrint As String WhatToPrint = Sheets("Reports").Cells(3, 6).Value 'sets the variable to equal the contents of cell D3 which contains the formula 'summarising the print ranges I want to print Sheets("Reports").Cells(3, 6).Select ActiveCell.FormulaR1C1 = WhatToPrint ' pastes the variable in cell F3 - just to check that it looks like I want it to Sheets("Reports").PageSetup.PrintArea = WhatToPrint 'uses the variable to set print area - this is where it fails! 'if you replace the variable with the contents of cell F3 the macro will work ActiveWindow.SelectedSheets.PrintPreview End Sub
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)
If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.
This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
