The context of my question is "Invoice Numbering".
I would like to have a sheet where I can have an incremental number. Every time I run the macro it should strikeout the last number and it provides me an incremental number.
I used such a sheet in a previous job and I cannot find it online.
I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...
1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.
2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?
3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?
I have a report blank that is comprised of numerous excel worksheets (fixed letter size). During the completion of the report, one may add, delete, and/or move worksheets. I want each worksheet to have a cell that dispalys 'page # of total number of sheets'. Is there a way to automatically update this information?
View 4 Replies View RelatedI have got 2 sheets:
- list (list of transactions)
- accepted (accepted transactions).
I want to make macro, which will:
1. select max value from sheet.accepted "B:B"
2. enter max value +1 in sheet.list "B8"
3. automatic fill down from B9 to the end of list with number increasing by 1 (in B9=B8+1, in B10=B9+1 etc.)
I tried with this:
After entering some values in a form I want to get a unique id for that record to be displayed in a message box and thereafter inserted into column A in the same row as the rest of the information. The numbering should be 1, 2, 3 etc and when I use the application the next time the id displayed should be the next number. This is what I've got so far, but now my unique id is always 1 (not very unique at all).
Private Sub UserForm1_Initialize()
Dim rIds As Range
Dim MaxId As Long
Set rIds = .Range(Cells(A, A), Cells(Rows.Count, 1).End(xlUp))
MaxId = Application.WorksheetFunction.Max(rIds)
With Me.IdBox.Value = MaxId
Private Sub DateBox_Change()
DateBox = Format(Date, "yy/mm/dd")
End Sub
I would like to ask for some assistance to my Spreadsheet problem.
To illustrate my problem please see below:
1------------> Level 1
1.1----------> Level 1 Sublevel 1
1.1.1--------> Level 1 Sublevel 1 Item 1
1.1.2--------> Level 1 Sublevel 1 Item 2
1.2----------> Level 1 Sublevel 2
1.2.1--------> Level 1 Sublevel 2 Item 1
1.2.2
2
2.1
2.1.1
2.1.1
2.2
and so on
I need to have a formula to automate the adding of outline numbers from "Level 1, Sublevel 1, Item 1..." So that if I deleted or added a row, the rest will automatically update themselves.
I will be the one manually inputing the Top Level and Sublevels I just need that the Items will be automatically added and computed basing from the previous level.
What I am planning to do is:
1. Check previous cell if the format has two decimal points or periods --> kinda stuck here
2. If there's only one then it will be the Sublevel 1 Item 1 (or depending to its location)
3. If there's already two periods "." then it will compute automatically for what item it is already.
ie:
1
1.1
1.1.1
1.1.2----->Checks the previous cell if it has 2 decimal places then if it does then it will add 1
I am trying to work out how to automaticaly fill in the next number in a sequence of bespoke numbers like this in column B 1012-0153-70, next 1012-0153-71, next 1012-0153-72 and so on so when i click on the next open cell in column B it adds the next number in the sequence
I have the following VBA code in my Sheet:
Private Sub Worksheet_SelectionChang()
With Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
If Target.Address = .Address Then
.Offset(-1, 0).AutoFill Destination:=.Offset(-1, 0).Resize(2, 1), Type:=xlFillDefault
End If
End With
but nothing seems to work. i also have the following VBA code on the same sheet:
Private Sub ComboBox1_Change()
End Sub
Private Sub Calendar1_Click()
[Code].....
I'm trying to make a sequential resultlist starting with nr 1, 2, 3, etc under the column: Rank ? This should be part of a macro, so autofill is not an option... As you can see, the number of rows are different from each group, and starts with nr 1 for every group. (Some formatting became all wrong posting this.........
View 5 Replies View RelatedI have a spreadsheet set up in the following way;
Excel Sheet Set Up.JPG
What I'm trying to replicate in column A is similar to the live bullet point numbering that can be found in Word documents where if a line is deleted the section numbering (think bullet points in Word) automatically fall into line and update i.e what was 1.4 automatically becomes 1.3 and so on.
So far I've tried the simple formula of = cell above +0.1 which works fine until i have to remove lines.
I have a list of 48 000 zeros and ones (0 and 1s) (and 12 separate lists).
If there is a 1 then I will return a zero.
If there is a zero then I need to find the next 1 and return the 'distance' to that 1.
For example
1
1
1
1
0
0
0
0
1
The first zero will have to return 4, the second 3 etc.
I have tried using Match(1,[ range],0) but this takes for ever to run.
I could write a udf to do this but figure that it would have to be based on a loop.
In the mean time I will implement the udf.
I have dynamic number in K2. How to make numbering list whose length is based on that K2 number? I want that list to start from A2. So, in other words, if K2=3, the list should be like this:
A
empty (cause my header will be there)
1.
2.
3.
Notice that there should be period after each number too.
why the Macro below works fine when the spreadsheet is not filtered, but once you filter the spreadsheet it does not work. and if possible a solution.
Sub Count()
Dim MyInput As Integer
MyInput = InputBox("Enter Start Number")
MsgBox ("Start number is ") & MyInput
mycount = Selection.Rows.Count
MsgBox mycount
ActiveCell.FormulaR1C1 = MyInput
For Num = 1 To mycount
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = MyInput + 1
MyInput = MyInput + 1
Next Num
End Sub
I have a large sheet (currently some 5,000 rows and growing) where each row is allocated a Unique Reference, however that unique reference is based on two criteria, 'Region' and 'Type'.
There are four 'Regions' and three 'Types' across the whole sheet (see attched sample).
Because of the ever increasing number of entries and the fact that the sheet may be sorted so the unique references won't always appear in sequential order, I am looking to try and find a way for the unique refrence for 'new entires' to the sheet to be generated automatically, based on entires in other columns. The sheet structure is relatively simple, with 'Region' shown in one column and 'Type' in another (again, see attached sample).
The unique references adopt this structure - 1st letter of the region (N, S, E or W for North, South, East or West), followed by 1415, followed by the first 4 characters from the 'Type', (REGI, NATI or COUN for Regional, National or County) followed by a sequential 5 digit number 00001, 00002, 00003 etc.
resulting in for example N1415REGI00001 or W1415COUN00012 and so on.
Because there are thousands of entries, I need an automatic way for the unique reference to be generated, ideally once both the 'Region' and 'Type' fields are populated, so the macro (or whatever method works best) will automatically determine the previous highest number for the relevant series and automatically add the next number for the new entry, based on the above criteria.
I don't know if this is possible with a macro or whether there is an easier formulaic way to achieve this?
I need a macro that will number a cell (A1 for example) starting with the number 1, and another cell (A2) with the number 2, then back to the first cell with 3, then back to the latter cell with 4 and so on.
View 9 Replies View RelatedIn Column G I have a Drop down list of dates. In Column H I want to show the month for the dates, as per when they are selected. I tried simple doing in cell H2 "=G2" they changing the format of the cell for just the month. which worked but for the cells in H that haven't had a dates selected yet it brings back January-00 all the way down. I need a way of getting rid of the January-00 but having the cells ready so that wen the date in G is selected the month auto appears in H.
View 2 Replies View RelatedI have a list of workers' names for my payroll. to lessen the time everytime i do the payroll, how can i have the nearest name appear/pop up as i type it and select the correct name by just hittng the "enter" key?
View 4 Replies View Relatedi am working on a long drop-down list which consists of a series of numbers, such as 1,2,3.... When users select 2 from the drop down list, the cell value will be automatically converted to the value "movie" that "2" is corresponding to.
The list behind the drop-down list is like this:
column A column B
1 music
2 movies
3 books
4 games
5 travel
How can I create a Makro that looks for values (e.g. values > 90) and that then generates a list with: all the values greater than 90, the row names and column names of the values?
I am pretty sure I will need a Makro for this, but how to create it. I am using excel 2010.
I have attached a file to give you an example. The red table is the table with the values. The blue table is what I want to be generated automatically. In the example I did it manually.
Workbook1.xlsx
I have a list of a couple of thousand (and more) individual items. I want to filter the list and have a box for users to type in text (there are no numbers) and the results will be seen automatically reducing in number as more text is entered. (Similar to the Windows HELP files )
eg, typing A (or a) will show all entries beginning with A (without pressing
"Enter" or similar
typing AB will show only entries beinning with AB
typing ABO will show .........I guess you will understand the idea.
I have tried various forms of Filter - Auto and advanced - but still cannot get the spreadsheet to do what I want. Unless I am not doing the Autofilter (or Advanced filter) correctly (I am still a relative beginner!) I still cannot find a way of simply adding letters to a cell or input box and the filtering takes place 'automatically' as the letters are added.
I am trying to achieve an automated macro.
I would like my spreasheet to look at cell B2, compare this to a list of information in Sheet2 and if it matches, run Macro1
I am trying to accomplish the following, when i enter a word in column C but in the odd row i want a macro to automatically convert that word to uppercase.
View 2 Replies View RelatedI need a code that will automatically run my first macro. The first macro already calls all the others. I just need everything to run as soon as someone opens the file.
View 3 Replies View RelatedI'm in the process of trying to get a macro to run when one cell, named "ImpVersion" changes.
Here's what I've got so far.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("ImpVersion").Address Then
Call Implement
End If
0:
End Sub
The problem I'm having is that ImpVersion doesn't always exist, as it is only added in when it's needed. When ImpVersion doesn't exist, I get a 1004 error.
Is it possible that the very same macro runs kind of incorrectly while it's being run as "automatic" (F5 key) and absolutely correctly while run manually, line by line via F8 key?
I am trying to debug the code but no luck as I get proper results while run manually.
Is it possible to create a macro that automatically saves a backup of the excel document in another desired location?
I have this formula:
Sub backupbutton()
Dim fname
fname = "D:" & Format(Now, "dd mmm yy hh mm") & ".xlsm"
ThisWorkbook.SaveAs Filename:=fname
End Sub
But that just keeps on making multiple copies of the file rather than overwriting the backup in the D: location.
Also, that particular macro requires me to have to click on a button in order for it to work but I would prefer that it happens automatically when the original file is saved.
a way, without using a macro, to have a specific column automatically sort data being pulled in from another column, so that even if the data from the initial column gets changed, that the destination column will take that change into effect and update the sort automatically?
View 9 Replies View RelatedSo I have a guest list workbook. There are two sheets. On the first one is a list of names on Column A. On Column B is a classification: 'C' if confirmed; "D" for declined; "I" for pending. I want to have all the names with "C" on sheet 1 appear on sheet 2 automatically.
View 4 Replies View RelatedNeed Macro to automatically printout my worksheets daily at a specifi time?
View 9 Replies View Relatedhow to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).
The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)
I would like to know if there is a way to Auto number a text.
I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down.
Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on