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...
What I would like to have done is to look for each cell in column M with only the string of "note" in it, then look one cell below and to search the text using boolean operators and if certain words are found, increment a series of counters.
For example, using cells M16 and M17 as references, if "please" AND "relationship" AND "customer" are found within the text, create a counter and increment it by 1. The program should loop through all of the cells in column M and determine how many instances of that particular set of words is found. If there are 20 instances in the column then somewhere on the sheet, counter 1 should indicate 20.
There are a couple other set of words I would like to search as well, with each new set creating a new counter and indicating the total instances found of that particular set of words. For example, in cells M40 and M41 I would like to search for "failing" AND "name" AND ("first" OR "last"). Thus, if there were 50 of these instances a second counter somewhere on the sheet would indicate the number 50 when the program finishes.
I was using the find and replace but I don't think I can use boolean operators with it, and I'd also like to learn how to do this programmatically.
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....
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................
I have a spreadsheet with a summary tab and 30 data tabs. The data tabs are named page-1 to page-30. In the summary page I have the following formula in cell C39: 'page-1'!C20
I want to be able to drag horizontally across 30 cells and have it increment to 'page-2'!C20, 'page-3'!C20 etc., and also drag it vertically and have it increment to 'page-1'!C21, 'page-2'!C22 etc.
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've got a sheet with 3 columns A, B and C headed: "Surname", "Forename", "Membership No" with then a variable number of rows containing the data. There are no blank cells (the macro already checks for this).
In column D I have "Group Name" which I need to populate with GrpA for the first 250 members, GrpB for the next 250 and so on down until it stops with the first blank cell in column C. At the moment I'm happy for it to cope with up to 2000 members, i.e. 8 groups, though may need to extend it later (no danger of getting past GrpX though!).
I'm attempting to write a simple piece of code to correct a formatting error in a spreadsheet. To fix the error I need to move a row of data up one row and to the right 2 cells. Then Delete the empty row and repeat until the end of the section that is messed up. I currently have the user selecting the range to copy, the cell to paste to and the row to stop at. The copy and paste aspect is working as intended however the macro isn't incrementing the rows correctly. This plus the deleting of rows I believe is causing the macro to crash. Is it possible to increment the rows of a range? When I try datarange.col = datarange.col + 1 I get an error.
I use spinbuttons to increment 3 data cells (C7:C9). These cells are used as entry data for some calculations and also trigger some macros via change event. Every time I use them the workbook crashes, this is only since I modified one of the instantiated macros that sorts a table on another sheet. See below the spinbutton code with the call of the macro and further the code of the sorting macro:
Private Sub SpinButton1_SpinDown() Range("C7").Value = Range("C7").Value - 0.05 End Sub
Private Sub SpinButton1_SpinUp() Range("C7").Value = Range("C7").Value + 0.05 End Sub
Private Sub SpinButton2_SpinDown() Range("C8").Value = Range("C8").Value - 0.05 End Sub......................................................
I currently have a project i am working on based on a invoice system and in my case it is to do with Van rentals. what i am looking to do is create a macro so that when the user clicks the order van button, it brings them to the order page and automatically creates an invoice number which is automatically added to the invoice database along with the customers details and the date of the invoice in a different sheet.
i have been browsing various forums and tutorial and have come across an a piece of code that is incrementing numbers each time the button is clicked. I can add the first set of details to the invoice database however i am unable to create a new row which copies the formulas and just change the invoice number as it is incremented - it just copies the same number
Now i am a complete novice in using VB and i have knowledge to an extent using excel. Is it possible to do that?
Sub test() ' ' test Macro Sheets("Invoice List").Visible = True Sheets("Customer Menu").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Customer Van Order Form").Visible = True ActiveWindow.SmallScroll Down:=-12 Sheets("Invoice List").Select ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]:R[7]C[7]" ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]" Range("B2").Select.............
