Add +1 To A2 If A1=1 HOW TO
I simply want to record the number of occurances, without date or time.
if cell a1 = 1 I want it to add +1 to cell a2.
The cell a1 will only be a 0 or a 1, every time cell a1 changes from a 0 to a 1 I want it to add +1 to cell a2. so if a2 has 0, and a1 changes from a 0 to a 1, it will add +1 to a2. a2 now =1. the next time a1 changes from a 0 to a 1, it will add +1 again to a2, a2 now =2.....and so on.
View Complete Thread with Replies
Related Forum Messages:
Vba Calculator: Click Add After Inputting The Digits Just To Have It Added To He Variable, Which I Just Add Up At The End
i have 1 - 9 number buttons, the +, - , * , / , and a C for clear, as well as an exit button. the display is a label. i have to click add after inputting the digits just to have it added to he variable, which i just add up at the end... terrible.
Option Compare Database
Dim var1 As String
Dim var2 As String
Dim first As String
Dim second As String
Dim sign As String
' on load
Private Sub form_load()
Form.Caption = "this calculator lab sucks."
Me.lbDisplay.Caption = ""
' on clear
Private Sub cmdClear_Click()
Me.lbDisplay.Caption = ""
var1 = ""
var2 = ""
Not A Valid Add-in Message Loading Add-in
I am using the GetSaveAsFilename function to save a file. I have the filefilter as "Excel Add-In (*.xla),xla", however when I save a file this way as an add-in, then try to load that add-in, I get the following error message.
'C:Documents and SettingsUser1DesktopBook3.xla' is not a valid add-in.
Does this mean that the GetSaveAsFilename is not capable of saving a valid xla file?
Add-in Created; And Added Via Tools, Add-in
I've created and excel spreadsheet that has code like this in a Module named Print w/ in the workbook:
I have saved it as an MS Office Excel add-in.
I added the add-in to my excel environment via Tools, Add ins...and let it copy it to my C:Documents and SettingsjohnApplication DataMicrosoftAddIns directory.
But yet when I create a menu button and assign the Print_wsPrint to a custom menu, it complains and says "The macro Model.xls!Print_wsPrint cannot be found," where Model.xls is the workbook I am in. It's in my add-in, not in the current wb.
Add-in Not Showing Under Tools | Add-ins
I placed an add-in (.xla file) with a " function" and a "sub" into C:Microsoft Office XPOffice10Library. When I go to Tools-->Add-Ins the Add-In doesn't show up. When I browse the file it says it already exists and still doesn't show up. The strange thing is that the "function" does work, but the sub (which is a sub worksheet_change) doesn't run. I also tried doing the steps as they are laid out in http://www.ozgrid.com/VBA/excel-add-in-create.htm and ended up with the same problem. The function works when I open up a brand new Excel application and use Book1, so I know the function isn't embedded in the sheet.
Reference Add-in Sheets From Add-in
how to get a workbook containing macros to save as an addin xla format to include hidden worksheets?
I have referenced them in the code, in unhides, copies as a new sheet and then rehides it.
Yet when I run it as an addin, when it goes to unide the sheet, it says Im referencing something which doesn't exist (because it's not included in the xla addin).
Add-in - How To Use Code In The Add-in
I created a worksheet as an add-in, and I am trying to use some code out of it to add to some of buttons in other spreadsheets. How do I call a subprocedure out of an add-in in another worksheet in VBA. I have tried several things. I have tried calling it like it was in a normal workbook by using the keyword Call, and I have done some other things like
IF - How To Add More Than One IF
I have figured out how to return the correct "answer" based on an IF formula, based on one parameter only (either true or false). This is the formula in cell G2 of the first worksheet called MREC-1 data, and it is based on the age category in column F. What I would like help with is returning the correct answer from the other 3 possilbilities (what the correct answers should be are stated in cells H3, H4, and H5. Can someone please show me how to add the other 3 "IFs" to my formula?
I have the following formula. Problem is I need to Add the word Value in the formaula sheet
I use the following sub. is there anyway I can get this sub to past the word value in every column Row 1 before any text that is there before?
The data is copied daily to load and duplicates are removed and new entries are copied to list sheet, by searching on the concatenation.
The problem i have is that i also want to add to the list, the row lines from the load sheet if the date changes in column G. Not really sure how yo approach this. I have attached sample data.
I am trying to create a macro, that adds rows to the worksheet.
That when it runs, opens up a dialogue box and ask
"How many rows whould you like to add?"
The result I would lie to do is:
1. I would like it to have the same properities & formulas and other cells in the same column.
2. It only needs to be a range of columns (ie A:X not all the way across)
3. it add them one row above the totals row (which would move each time rows were added (if that is too hard then at the top would be fine.
4. This can be run if the sheet is protected. (though I can add a script to unprotect & protect if need be.
I think the hardest part for me is how to allow the number of rows to be added to be based on a dialogue box, that adds them smoothly
Search And Add
I'm trying to write a macro that will find any cell containing something, here's the trick, I need it to find any cell containing a string (such as "x"), but it also has to be the right color, font face, etc. and then look at the value one cell to the left of it. (I can do the rest of the macro, which would add them all up and place them all somewhere)
Add A Line
I have a very simple spreadsheet. Two columns: Date and Comment. I would like my employees (2) to go in and leave each other messages. I have setup conditional formatting so that when they enter their names the row will format with a different color for each person.
What I would like to do is:
That when they hit the enter key at the end of their comment that a new line is added above this line.
What I am trying to do is have their comments entered in ascending order so that the latest comment is always at the top. These two employees are completly excel illiterate and I really dont want to do the sort function.
They will be entering data on a daily basis.
I did do a search but couldnt find anything that helped me.
The rows start at A4 through B50.
Add In Same Cell
Name# of fishWeightLunkerShow upTotalDead Fish PenityYTD WeightYTD # of FishBob 510.50.51100
Hello, I want to put a YTD number in H2, by taking C2 and adding it to H2 when there is a number in there or not.
Ex. C2=10.5, H2 is now 0 so in H2 i want it to read 10.5
But after the next fishing tournament I would like it to look like this.
Ex. C2 now is 6.30 so in H2 I would like it to do this =sum(C2+H2) and read 16.80.
Disabling A COM Add-In
Is there a way using VBA that will allow me to disable a COM Add-in when I open a workbook without me (or the user) going through the steps to remove it first.
The reason for my asking is this: We have installed SAS Add-in 2.1 to Excel 2007. So this brings up SAS under the Add-Ins tab. But then when my program executes and it sees I use command bars, it adds my command bars under the SAS menu. I am trying to make the interface as clean as possible for the user, so they only see my command bars or menus.
Also, if we were to install the SAS Add-in 4.2 to Excel 2007, I have read in a SAS paper that SAS Add-in 4.2 creates its owe tab on the ribbon instead of inside the Add-in tab. So I would be able to use xml to make the SAS tab visible="false".
I have a range of cells in column A... it's called SPACE. The code should be if the value in each cell is equal to "1" and select the NEXT row and INSERT an empty space.
Then keep going down the range to find the next "1", and add a space... but if it's not equal to one, keep going. Essentially I'm grouping data, with new data sets being indicated by the "1", and need to put an empty row in between the data sets.
Add Value If Greater Than
I am trying to create a formula that will automatically calculate greater than or less than and then add x or y depending.
I am shipping some items and we can fit 100 or less in a small box that weighs 3oz and 100+ in a bigger box that weighs 6 oz.
I want the formula to look at the quantity and determine if the quantity is 100 or less it should add 3 and if greater than 100 then add six.
I tried tons of google searches but can't seem to figure it out. This is the first forum I could find that I figured might be able to help.
I have been doing this all by hand in multiple columns...
Macro Add On
The code below does what I need it to do for all files within the specified directory (C:Documents and SettingsDesktopLab) but what I'd like it to also have it do is before it saves and closes a file, highlight all worksheets in that file except for the one named "Buttons" [which all the files in the directory contain] then save and close the file
I need this to be done because I'd like to be able to simply right click over any file and click Print and know that all worksheets (except for the one named "Buttons") in that file will print.
Application.DisplayAlerts = False
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C:Documents and SettingsDesktopLab"
fn = Dir(myDir & "*.xls")
Do While fn ""
Add Up In Range
I have a sheet which consist of columns with some personel data, the weekdays and some columns with numbers that should be added up based on a users request from Monday till users choice (could be Monday till Sunday) and from users choice till Sunday.
Sheet contains about 1000 of these ranges, my idea is to add 2 rows after each range with the add ups based on the two users choices, in the second choice should also be the possibility to not add up anything because in the first choice it is possible to let add up all (Monday up to and untill Sunday).
Add-in And VBS
I am running the following VBScript to start Excel and run a macro. This works in all cases except where the Excel macro requires a 3rd party add-in to run. It appears to me that when Excel is started through VBA it somehow does not run from its normal path and so loses the addressing to the add-in. Is there anyway to have VBA overcome this add-in problem?
Set xlObj = CreateObject("Excel.application")
xlobj.Visible = True ' Remove comment mark " ' " to see spreadsheets open
xlObj.Workbooks.Open "c:my spreadsheet.xls"
xlObj.ActiveWorkbook.Saved = False xlObj.ActiveWindow.Close
My macro simply imports a text list and copies the add-in formula down.
I have tried to set a reference to the add-in via Tools>References>Browse (and navigate to, and select the add-in). At this point I get an error "Name conflicts with existing module, project or object library". This is true even when I have previously removed the add-in from the add-in list. Ignoring the error.
Add The New Row
I have containers with different SN# for each and the containers have items which are inside of it. My Excel sheet looks like this
ParentID Desc SN#
USAU Container USAU
USAU Tent 1234
USAU Stove Ab987
01BD BOX 01BD
01BD Pliers N/A
01BD Screw Driver N/A
Every time the ParentID changes I want to enter a blank row
Basically I want to sererate the Parent and children from other
I need to send a file to hundreds of users so that I can tell who has a particular add-in installed. This file will then be saved to a particular directory. The problem I'm having is controlling the error that occurs when the add-in is NOT installed. The below approach (using On Error Goto) clearly isn't the way to go. I need something like
If(Iserror(AddIns("AmartaSpecs") Then Cells(2,3)="No"
The code I have so far is:
Cells(1, 1) = "Username"
Cells(1, 2) = "Computer Name"
Cells(1, 3) = "Add-in installed?"
Cells(2, 1) = Environ("Username")
Cells(2, 2) = Environ("Computername")
On Error GoTo Error
Set a = AddIns("AmartaSpecs")
Cells(2, 3) = "Yes"
Cells(2, 3) = "No"
ChDir "E:FinanceSQL Add-in test"
ActiveWorkbook.SaveAs Filename:="E:FinanceSQL Add-in test" & Environ("Username") & ".xls"
Add 1 To New Sheet Name
I have a workbook that among other things, generates a new sheet and names it "newsheet". Once that "newsheet" is created many other macros run on "newsheet". Once it completes, if I decide to re-run the macro it will try to re-create a new worksheet named "newsheet" and execute all the other macros on it. This of course generates an error and stops.
Is there a way of adding a small portion to my code to say: if a sheet already exists with the name "newsheet" then create a new sheet named "newsheet" + 1 (“newsheet1”) and so on. This way I can always run the code and have a newsheet1, newsheet2, newsheet3 etc.
How To Add To Time
I have a set of over 1000 data points. Each data point is recorded every 2 seconds. An example of the first three data points for the time column is
03/27/2009 11:12:19:73503/27/2009 11:12:21:73503/27/2009 11:12:23:735
I only want to display HR:MIN:SEC for each data point.
One way I thought of doing it is by starting with my start time of 11:21:19 and then adding 2 seconds to every data point.
Add And Name A New Workbook
this is what i have, which is not working
FilePath = "\Dycwfp01misdept$ProjectsTechDispatchPRODUCTION_REPORTSCompareDataFiles"
strSite = "Manassas"
strDate = "_12 / 01 / 2009_"
strReport = "ScoreCard"
ActiveWorkbook.SaveAs FilePath & strSite & strDate & strReport
Add Or Sub Time
I have two cells formated to TIME 1:30 PM.
I have a column that has start times. Another with finish times.
A1 = Start
B1 = Finish
My natural thought is =a1-b1 would give me my total time for the day.
I am having trouble sharing an Excel Add-in I've created which contains three macros (modules). I have looked everywhere but, oddly, I can't seem to find a definitive answer. I would like for people to be able to install the add-in, and have access to three macros which it contains.
What is the best solution to this problem? I would like to add buttons to the add-in ribbon that execute each macro individually. The macros should be available to all workbooks once the add-in is installed.
Add Up Cells
I have a spreadsheet which lists order placed by customers in a month. Every customer falls under an area code depending on where they are in the country i.e GB1, GB2, GB3 etc. At then end of every month, I have to produce a report showing all orders placed by the area codes.
At the moment, I have all the GB1 customers together, followed by the GB2 customers etc vertically, so at the bottom I can total the orders by simply using SUM. This means every time I want to add a "new" customer to the spreadsheet, I have to insert cells within the spreadsheet. This is time consuming as I have 5 different tabs for the 5 different factories!
What I want to do is not to have to worry about what order the customers are in the list (so I could maybe alphabetise them), but that for the totals at the bottom, Excel looks through the sheet and adds up all the values for the weeks for customers GB1 together, GB2 together etc.
The spreadsheet looks a bit like this:-
Area Customer Week 23 Week 24 Week 25 Week 26
GB1 P Smith $456.84
GB1 L Jones $45447.65 $45618.70
GB2 R Jenkins $46583.00
GB2 XYZ $778.49 $789.47
GB3 XYZ £159.45 $45694.87
GB4 XYZ $64456.77
GB6 XYZ $781.78
Add The Vlookup
im trying to created a worksheet that adds the number of days people are off in a year, i have come up with this formula as people can chage teams and therefore position in the worksheet,
Add The Number Once
I need a spreadsheet where I will record the bus numbers into a sheet but I only want to be able to add the number once ie.. error if duplicate bus entered. Also the numbers will change from time to time, old buses sold when new ones arrive. I have the following code that someone helped me with wich works fine provided you don't have to change the numbers.
Add-In Not Removing
So I created a custom Add-In, but the add-in isn't removing itself when I uninstall it or when I close the program (this creates an error in saving files, so all files are getting corrupted)
This is all the code I have running the menu bar and I can't figure out the proper closing code
Formula- If Between Add
I am having a few issues stringing along a formula.
I need A2 to say "If A1 is between 0 and 2 add 0, if greater than 2 but less than 4 add .30, if greater than 4 but less than 6 add .70 and so on.
A2 looking at A1
if 0 through 2 add 0
if 2 through 4 add .30
if 4 through 6 add .70
if 6 through 8 add 1
if 8 through 10 add 1.3
if 10 through 12 add 1.6
Basically these are employee break times that I need to automatically figure into our labor hours. Every 2 hours they get "x" amount of break time. So if they have over 2 hours of work they will get 1 break, if they work over 4 hours then I factor in a lunch and the previous break ect..
IF Then Else For Add The Values
I have a Userform open up in a workbook and it asks you to enter your initials and then it asks you to choose a city from a combo box. I want to have it to where if you choose no city (so that the linked cell is empty) it will take you to a seperate page upon clicking next.
So far I have the "Next" button accomplishing the following tasks.
If the user selects a city then i need all of the code below, but if they do not then I need excel to recognize that Cell B1 is empty and will take you to a different sheet.
Basicly I need an "If Then Else" code to add to what i have to take me to the other sheet if B1 is empty or to continue what I have if not.
HTML Sub Customer_Info()
Application.DisplayAlerts = True
Best Way To Add Times?
I have a bunch of recordings on my DVR. So I put the titles and description onto a spreadsheet each in their own column. Then I created a "length of time" column. So why, in this attached spreadsheet, does the "grape" total autosum all the rows in that column correctly but the "cherry" total does not total it's column correctly?
I looked at the format of all cells in both columns and they are both set at h:mm.
Add The Squares
How would I add the squares of cells A1:ZZ1 or A1:A1000, without having to do
This is a basic example of what I actually want to do, but is essentially the same problem.
IF Cell = <> Then Add Value?
We have a spreadsheet where we enter payments into columns. But if we do a refund we enter the figure like this <$500.00>. I want to create a formula that totals all of the figures in the column but when a figure is written as <$500.00> it deducts this amount from the total. I tried using this in another cell
=IF(A1="<>",A1,"") But obviously it only works if I have <> entered into A1.
Add Range Name
I have a list of Dynamic Range Names Staff1 to Staff20. As a new member of staff is added I want to add a range name using that persons name. I am using the following code to add the range name. NewName is calculated and = Staff1 for the first name added, staff2 for the next etc. The range name is created ie. Keith but when looking at the range names the refers to box shows ="Staff1"How do I get rid of the parenthesis?
Add Up After An IF-Else Condition
I have a rows of the following.
The following are the outputs.
When I do summation on the output cells, the results returned 0. Do I have to do anything procedure in order to add up values that are generated by If-Else?
Custom Add In
I added a custom add in ages and ages ago and have forgotten how I did it! I'm now trying to remove it but not having much luck.
Its not listed in the Tools>References menu and its not added on to a workbook saved in the C:Documents and SettingsUserApplication DataMicrosoftExcelXLSTART folder.
Are there other ways I could have added it? If so can someone let me know as I might be able to remove it then!
Add Characters Between...
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space when an upper case letter is immediately followed by a lower case letter?
Add-in Is Read-only
I've created an add-in. When I try to update the coding in the add-in, Excel reports that the add-in is read only and prompts me to choose another name for it. However, it is not currently selected in my Tools -> Add-ins... window.
Can I Add A Calendar
I have a spread sheet that requires people to put dates in certain cells. I was wondering if it is possible to have a small calendar pop up when the cell that requires a date is clicked on. The calendar would need to show the current month, and future months for the calendar year. I’ve seen this feature in OneNote 2007, and wasn’t sure if this same feature could be created for excel. I’m not sure if this can be done by a macro or not.
QueryTables - Add
I recorded setting up a query to get information off our Data Warehouse about a Tracking Number that the user enters into the spreadsheet and got this little piece of gold.
Dim sqlFromText, sqlWhereTxt, sqlSelectTxt
Dim qt As QueryTable
For Each qt In Worksheets("DW Data Dump").QueryTables
On Error GoTo err_Label
The problem is that now when I try to run it I get a general ODBC error and the debugging line that is highlighted is .Refresh BackgroundQuery:=True.
Add Value Into Cell
if its possible for VBA to perform add value in cell. Lets say, Col1, row 12 & 13 is holding the same value "GH". I would like to perform an automation filter out, from row 12 & 13 Col1 into Col2 row 12 & 13 value GH_01 and GH_02.