I am using a userform to allow the users to pick from two combobox to generate the first 3 digits on the part number (which are inserted into a textbox automatically), the final 4 digits are auto generated using the following code:
[Code].....
This is called when a button is pressed, this then checks the already existing parts sheet to see if the number exists, if it does then it will ask for another number, incremented with the previous code. This will continue until a unique number is found.
This is the code that does this:
[Code] .....
My issue is that if I reset the form to create a new part number which is in another catagory, I need the auto increment to reset to 0, so the new part number can be checked. At the moment it carries on incrementing from the point the form is reset. The same happens if the user decides to change a catogory (maybe for the first part in the catagory, should be ***-0001), if the last part was ***-0055, then it will assign the new part 0056, not 0001.
So how can I reset the auto increment to back to zero on for example a listbox / combobox change?
I have fought with this for 2 days and I am trying to have a worksheet cell update the date and time if the cell it is referencing (B3 see below)
Here is the formula I am using however it updates every time a new row is added to the worksheet. So the Date/Time stamp on each row is always updated to NOW() everytime.
=IF(ISNA(B3),"0/0/00 00:00",NOW())
I would like to have a Date/Time stamp stay as is once that row has been added. Is there anyway to stop the field from updating once the date and time have been set?
Excel tries to be helpful, and formats some of the cells in scientific notation.
I have tried to format the column to the "Text" format prior to the import with
Columns(2).NumberFormat = "@"
This does not work. If you import the table you can see that rows 1405, 1406, 1407, 1408 all have the same scientific notation value. Their true values should be 0E0, 0E4, 0E8, 0E9 respectively.
There are quite a few other instances in the column when a text value has "E" for the second character that Excel will format on its own.
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones). Unfortunately excel always converts this field into a date and a time but this is not what I want. Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
I decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.
How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
Im trying to create a catalogue for our complaints that come in each day at work. Ive created a basic layout I like, it uses 3 cells for a week day and 1 cell for a weekend day. The date is listed on the side of each day. I would now like to be able to quickly use this format for the rest of the month/year but need a way to do this easily as I cant do each day separately in the future. Ive tried selecting cells and then dragging down but all it does is repeat the previous 7 days and the same dates instead of incrementing the date.
Im using the following HLOOKUP formula : =HLOOKUP($B$14,$DA$15:$EH$380,2,FALSE) and I need to drag it down 365 times, but the row index num, ie 2 does not increment with the drag. Is this correct or is there a way of making the 2 increment?
If I'm in a worksheet and I spot a row I'd like to copy into my second worksheet, how do I arrange to have it increment the rows?
EX: searching through a worksheet, I see a suspicious entry. I want to copy it into another worksheet. I use the macro. I go back to the original worksheet and see another suspicious entry that I'd like to copy. I'd need a macro that would copy the active row from sheet one, and increment the rows on sheet two before pasting.
I create inventory cards and each needs a unique 4 character sequence number. Currently I have it set up with 4 cards per sheet. If I need to print more than 4 cards, the worksheet will print one sheet of 4, reset itself and increment the sequence number by 4 and continue this cycle until I've printed the number I need.
Currently I have it only using numbers. I start at 1000 and it increments, by 4, up to 9995. Once it reaches 9995 or higher, the increment resets to 1000 and starts over. The problem I am facing is I am now starting to get several cards that have the same "unique" sequence number. What I would like to do is change it from numerical sequencing to alphanumeric sequencing.
This is the code I currently use for the incrementing....
I've got above basic VBA knowledge and learning very quickly (largely due to great forums like this) I'm now creating a userform for the first time. I'm trying to create a New Supplier form and one of the fields is going to be for Supplier ID where it takes the first 6 letters of the Supplier name (entered by the user) & tags on '001' at the end. I've managed to do this but I would like some code to search existing supplier ID's ( Column A on separate sheet) and check whether the ID created already exists and if it does add on '002' instead of '001'.
SO for example..
If user creates a supplier called Microsoft the supplier ID will be Micros001 - this I can do, but if Micros001 already exists then it will generate an ID Micros002, Micros003 and so on...
on the sheet PANEL in cell A2 I want to reference the information on sheet 480 in cell B5. Then on sheet PANEL in cell A3 I want to reference the information on sheet 480 in cell B54.
'PANEL'A2 needs to equal '480'B5 'PANEL'A3 needs to equal '480'B54 And so on...
I need the reference to skip 49 cells each time so that I can just copy the formula on down the column.
I'm putting together a document where I'm trying to automatically create a reference number for each entry. The reference number needs to consist of a site ID (found in column A), an underscore, and then an incrementing number dependent on how many entries the reference number appears.
For example, in column A, the first time the site ID: 12345 appears, I'd like the reference number 12345_01 to be created. The second time it appears, the reference number would be 12345_02. And so on.
If possible, as illustrated above, the first nine references need to have a 0 before the number - i.e. 01, 02, 03, etc - when it hits the tenth instance the reference becomes 10, 11, 12, etc.
I've tried to solve this with COUNTIF, but cannot get it down.
I have a spreadsheet which I need to remove all the blanks from, but instead of shifting cells up (I figured out how to do that in a macro), I want them to shift left.
I understand the basics of for...next loops etc, but I can't seem to understand how to make the selection in a range string variable. Not sure if this is a good explanation!
I am running a macro where I pass it starting column and it processes the next 10 columns. How can I pass it "J" and have it increment K,L,M,N,O,P,...?
I need to complete what would seem a fairly simple incrementing task but I'm not getting the results that I'm looking for. I don't know much about macros so would prefer not having to use one but I'm willing to try if it's not too complicated.
I'm using Excel 2010 on a PC. For several projects, I need to increment cells that contain text and numbers. The column contains data in a cell (A1), followed by a blank cell (A2), then cell A3 contains new data followed by a blank in A4, etc. What I need to do is 1) copy the exact data in A1 to A2 and then 2) increment the number used in A1/A2 by 1 in A3, copy that into A4, and the pattern repeats down the column.
Here is an example of what I have in column A: Test_01 [blank cell] Test_02 [blank cell] Test_03 [blank cell] etc.
This is what I want as a result. Test_01 Test_01 Test_02 Test_02 Test_03 Test_03 etc.
Do I need to save the data in the column as text, general, or numbers? Is there some function that would make incrementing the data possible as I've outlined above?
At the beginning of a loop, my cursor is positioned on a row. I want to test two cells on that row.
Depending upon the result of the two cells, I want to make the row a certain color.
After that, I then want to move down to the next row, test the same two cells in THAT row, color accordingly and loop again until I reach the bottom of the spreadsheet.
My problem is this: If I can use a loop that lists various conditions, along with the corresponding statements that color the row accordingly, how can I when finished increment the cell references so that the conditions change to refer to the cells the next row down?
If a loop is not possible, any suggestions you have for solving this problem would be appreciated. I have about 200 rows so am trying to condense my code; this may be hampering my ability to figure out the solution.
I have a sheet with all of my data sorted into columns. I am writing a macro to select and copy the value from column A if there is any value in column B on the same row. The output would be sent across consecutive columns.
As an example;if B3 is not blank, copy A3 to I5 if B4 is blank, do nothing if B5 is not blank, copy A5 to J5 if B6 is not blank, copy A6 to K5 and so on.
I cannot figure out how to increase the output column identifier. Everything else I can get
I have the following function that looks at a range of data and deletes duplicate entries in each column. I want to change the range that is examined programmatically, rather than having to retype it for each range in this huge sheet! I have subtotaled and counted the rows in each range, so I know how many there are, but I'm stumped as to how to increment teh range correctly. I'm attaching a small sample of my data. The full sheet is 30,000+ rows and 94 columns.
Sub DelDups2() Dim rngSrc As Range Dim NumRows As Integer Dim NumCols As Integer Dim ThisRow As Integer Dim ThatRow As Integer Dim ThisCol As Integer Dim J As Integer, K As Integer, x As Integer Set rngSrc = ActiveSheet.Range("c68", "cr70") NumCols = 94 NumRows = rngSrc.Rows.Count ThisRow = rngSrc.Row ThatRow = ThisRow + NumRows - 1 ThisCol = rngSrc.Column For x = 1 To NumCols For J = ThisRow To (ThatRow - 1) If Cells(J, ThisCol) > "" Then................
