I want to restrict the adding the rows and columns and also deletion of rows and columns in excel template, the same template is circulated to all the program owners to capture there project metrics. With that template through macro values are extracting to consolidated report. Some times program owners are adding extra columns/rows with that we are getting errors/incorrect reports.
View 14 RepliesI have the following code which works but after the user gets an initial error says that they have to enter what is a ComboBox choice they get a second error when they press backspace to delete their entry. Is there a way to just give them the first error but not an error when they press backspace to erase their mistake in the form?
This checks the user input...
Private Sub Combobox5_Change()
If ComboBox5.ListIndex < 0 Then
MsgBox "Please Only Pick From The List. Use Admin Page to Add More to the List", vbCritical, "Error"
End If
End Sub ...........
how do I restrict editing to certain columns/Rows and allow some to be edited by users?
View 2 Replies View RelatedHow do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.
IE: Range Name "MyRange" = A10:A100
I insert a new row at A4 then "MyRange" = A11:A101
I want it to stay at A10:A100.
Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).
Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line
VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer
[Code].....
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies View RelatedI'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies View RelatedIn 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?
My data is in the following format,
Company Name Bill Status
GTAEPL ACCEPTED
GACEPL REJECTED
I need a vba code if the in Bill status column rejected is there all those rows to be deleted.
Here's how my project will need to work. I need to transfer text files(.txt) to the same workbook, but 1 worksheet for each text file. In here, I can choose multiple files. After that, I need to delete WHOLE rows that has a blank cell(except 1st column), starting from row 4. Then the last requirement would be to fill the 1st column, a portion of the text file's name.
First, I need to click the command button in order to choose the files I want to load. first.png
And then, if I chose two .txt files. It will also load two text files. In here it is, CLASS 2014 and CLASS 2013 second.png
What I want to happen next is for "2014" to be inputted in columns 1, rows 5 to 15. And then delete rows with blank cell, in which here is rows 8, 10, 11, 12 and 15.
I have a listbox on a userform and dependent on which of the 9 values they select, I want to delete the rows in a range that do not contain the string.
I can get the solution using a match on the listbox items but not the reverse, i.e. delete non matches, dependent upon the variety of values that can be selected.
I could potentially use a filter to drop them out, is this the best way? Always struggled trying to build multi filters.
I have a question on how to define fixed rows on making cell blank on new selection change.
The code below affects all rows under coloumn 1.
Is there any way to affect only eg row 1 to 20?
I attached a sample file for reference.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
strName = Target.Name.Name
On Error Goto 0
If ActiveCell.Column = 1 Then
Application.EnableEvents = False
ActiveCell.Offset(, 1).Value = vbNullString
Application.EnableEvents = True
End If
End Sub
Using VBA... I have Column N (eFIUnitRebate) which I want to swap with Column M (FFUnitPcnt)
Now with Column M as eFIUnitRebate and N as FFUnitPcnt I want to create an extra column (adjUnitCost) where Column N is and push FFUnitPcnt to become column O, etc.
Here is how the whole process should work:
Swap M and N
Insert new Column in N
Insert new Column in P
Insert new Column in Q
Insert new column in R
There is more information on the sheet after these columns so for all column inserts it needs to push whatever was there over.
So the layout is as follows:
J
K
L
M
N
O
P
Q
R
Units
UnitCost
UnitRebate
[Code] .....
These columns are thousands long, so I would like the formulas for them to be applied to the whole column as well.
I wish to have a column of values. It will be dates. There will be a value in each cell (hours worked by employee) I want to know only the total hours for the last 30 calendar days. I still need to see previous values, but I just want to total the last 30
View 9 Replies View Relatedi have two columns
column1: status
column2: total time of status
infinite long
now what i need to do is count the total time a specific status has.
f.ex.
column1: status1, status2, , status1
column2: 0 days 4 hours, 1 day 0.5 hours, 5 days 3.5 hours
how can i make a formula that looks up
if (in column1= status 1)
time= column2
the result i need is:
status1 = 5 days 7.5 hours
status2= 1 day 0.5 hours
i thought about a countif over column1 but i dont know how to add the second column
i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
View 14 Replies View RelatedWhat's the fastest way to display the sum of each column in one row. Here's a picture of what I want to do: http://www.filefreak.com/ppublic/33833/excel%20help.bmp now I want the sum of each column to be displayed in for example row 10. I have tried manually clicking "autosum" (sp?) but that will take a lot of time as I have about 400 more of these.
View 2 Replies View RelatedI would now like to as remove the first column and add another column a and b. A would say "Service From Date" and column B would say "Service To Date" In addition I would like to add a column between "5202 Meals Provided" and "5221 S.A. Facility Lodging" a column that reads "7025 Number of Days Care" and that tallies like the rest of them.
View 14 Replies View RelatedI need to editing a VBA script and can't assist right now. Here is the VBA script as it exists today:
View 3 Replies View RelatedI am new to the Macro editing of Excel. I have a list in the first tab that I would like to click yes or no (how to set up a checkbox) and the appropriate column will pop up in the next tab with appropriate header. I would like it to be where the column will pop up in the correct order, so even if one is clicked in the middle, it knows where to put it.
View 6 Replies View RelatedI d like to ask for a vba code that adds two columns starting from d5 and e5 for several worksheets called multi1,multi2,multi3,........The d column contains only numbers, the e column contains number and letters (product descriptions)
View 9 Replies View RelatedI have a data worksheet, which contains about 20,000 lines of journal entry info. There is a separate column for debit and credit, not one "amount" column. On another worksheet, I am aggregating the data to review the impact for this series of journal entries over a time period (12 months). Thus, my pivottable contains "Accounts" in the "ROW" and "Period" in the "COLUMN" sections of the PivotTable Layout. The "Debit" and "Credit" fields are then summed in the "DATA" portion of the PivotTable.
My issue revolves around getting the actual impact for each account for each time period. Essentially, I need an extra column inserted between each time period that sums the debits and credits for that period. Do I need a macro for this (not any experience with macros)? My file is way to big to share, so let me know if a pared-down sample would work.
I am trying to write the code for adding double speechmarks for the number of columns on my spreadsheet with a column heading Notes. There are about 10 columns out of 30, and all of them need double speachmarks.
My manual process was to insert a new column in from of the 'Notes' column, use the concatenate function for example - =concatenate("""",A2,"""") and copy the function down the column. The I was geting rid of the formula by copypaste special values, transfering to my original 'Notes' column and at the end deleting the new column that I added at the beginning.
Is there a quick code to populate a concatenate formula via VB? If not, what code can I use to replicate my manual process via VB. On the quick note, the spreadshee has always the same number and order of columns, but number of rown is defferent each time.
I have 5 coloums in which the last three have numeric values. The first two coloums have name and country code. Now, The first two coloums have duplicate values but the rest of the corresponding columns have different numeriv values. What I want now is to add the numeric values of the coloumns of the duplicate data and want to display in the first row. For ex there are A = name , B= Country , C D and E contains different numeric values. A and B have dupilcate data but different C, D and E values. Now I want to add up the numeric values of the dupilcate data and get it displayed in a new coloum in the first row of the duplicate data set.
View 1 Replies View RelatedI just downloaded an income/expense template from Zillow to manage my rental properties. The template works great but it's only set up for 5 houses and I need more columns as I own more than 5. In the attached template the houses are listed by property codes and that is the column that I need to add to.
View 8 Replies View RelatedEach week I get new weekly data-metrics. I have a series of formulas that figure LAST 4 weeks average, LAST 8 weeks, etc etc. How do I drop in a new week of data and have my formulas adjust? For instance, my formula figures last 4 weeks average of May Wk 1, May Wk 2, May Wk3, May Wk4. Now it is June Wk1, so I want to figure (without changing any formula) May Wk 2, May Wk3, May Wk 4, June Wk 1.
View 1 Replies View Related in my workbook: in sheet1 i have many cells with formulas which calculates values from sheet2. I also have a macro which is used often that will arrange and prepare data in sheet2, including adding some columns.
The problem is that after these columns are added, the formulas in sheet1 will be changed, even if I used f4 ($) in the formulas.
how can I freeze these formulas? I think a solution is to include the formulas in sheet1 in the macro I'm running for sheet2. But I would like to avoid this.
I want to total cells that have data in them across a row. I have a name in Column A and then in Columns C-Z I have data. In Column B I want Excel to scan across cells C-Z and if a cell has data in it (it will be text) I want it to add one to the total in cell B. Would CountIF do this with text involved?
View 7 Replies View RelatedThis macro has a problem in that it totals up the colunmns in two places by error
The total should be at the end of the coumns only.
It gives me a total part way through the list which muddles things up a bit.
Sub prepareSAPexport()
'
' prepareSAPexport Macro
' prepare the sap export ready to be compared to CDS
'
' Keyboard Shortcut: Ctrl+s
'
Range("A1").Select
[code]....
OK, I have a spreadsheet that has several pages for tracking when items are added and removed. On the main sheet I need to have the total items added, the total items subtracted, and the total avaialble. This is also based off of month.
For example, on sheet 2 it would show 5 of an item added, 2 of an item subtracted, all for March. It would also show 3 items added, 2 subtracted for April. On Sheet one i need to report in a row any items added, any subtracted, and the current total. I am pretty sure that i have the formula for added items and subtracted items for the given month, but i do not know how to get the current total.