I'm making a Purchase Order generator for work. Essentially, the main screen has buttons and the user selects the company, job number, their name etc. They click 'Generate' and it will great a brand new excel file for them with all the correct codes, ready to populate and send to a client. In the main sheet, we also have a master list showing every purchase order made to date. This is where I am currently stuck. I will have many more questions on the way. This is my first program so very new!
Lets say we have the following in cells A1 and A2. The rest is blank:
I am busy with a very large dataset +-20 000 records showing the movement data and I am having problem with inserting missing data: I have columns titled day hour and position, but multiple records for each as data was initally recorded by second. Data looks like this
What I need to end up with is a sequential dataset running from day 1 to 388 with hours 1-24 of each day. Positions need to be filled in for all data recorded (see above). The big problem is that movements between positions need to be recorded and accounted for i.e if more than >80 of time was spent at the position Z3 then that is the position for the hour.
In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?
I have tried many different ways of coming up with a solution of this problem without writing a VBA program, however, Excel's date and time formatting scheme seem to be tripping me up. As a result, I am trying to figure out what direction to go.
I have seached the board up and down looking for a solution and I have found one problem that is midly similar but I do not fully understand the code. I have tried to modify it but to no avail.
Here is my problem:
I have 9 columns of data that are reported in 15 minute intervals for a little over 3 years. There are missing data in the data set and it would be infeasable for me to manually find and replace the missing data. (Over 110000 rows of data)
Example of data (Where "/" delienates column seperation):
Date / Temp 2m / Temp 10 m / Radiation / RH / WindAve / WindMax / WindMin / Rain
So what I need to do is this: 1) Find which data times are missing 2) Add the appropriate amount of rows in between where the missing data would be 3) Add the correct dates to the new rows 4) Add "N/A" to the columns that have no data
You can see that my date and time are formatted in mm/dd/yy hh:mm
I found this on the website and was trying to modify it to my needs:
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
I'm updating someone's spreadsheet and so don't won't to add new cells or anything to it if possible. The problem I have is that I need to perform hlookup's of 3 cells, average them (some responses are non numeric) and then hlookup the output to give a final value.
The problem I have is that putting the lookups in one cell and then averaging them and looking up the average in another works, but all three steps in cell won't, it keeps choking on the non numeric responses.
Are their any work arounds to this, as the sheet has quite a bit of VBA code attached and I don't really want to have to add columns and then change the VBA code too.
I'm trying to loop through a list of about 1300 items and copy the rows where one of the cells has a fill color to 2 different sheets in the same workbook. If there's no fill color, the row moves to a different sheet.
My code is determining that all cells have a fill.
For Each cell In Range("Classification") If cell.Interior.Color blank Then cell.Select Range(Selection, Selection.Offset(0, -9)).Copy Destination:=Range("Compliance") Range(Selection.Offset(0, -5), Selection.Offset(0, -9)).Copy Destination:=Range("Mstar") Else cell.Select Range(Selection, Selection.Offset(0, -9)).Copy Destination:=Range("RP") End If Next cell
I am creating a budget worksheet for non-technical users. They choose a starting month from a drop down menu (already created). I want the remaining 11 months to automatically fill in to the right (in a row). I have created a macro (initiating autofill) that requires them to click the button after they choose the starting month but this copies the drop down menu & Input message from the Data Validation I used to guide them initially.
I have a workbook with two worksheets. Worksheet #1 is a form that will be populated with data and saved as a new worksheet, then cleared and used repeatedly as a master form. Worksheet #2 is a log / register of the unique forms completed and saved from the master each time. I need to assign a unique sequential # to each form when it is saved and record this number in a column on Worksheet #2 (the Log). I am using some macros for the copy work but struggling with the auto-numbering of the forms when completed and saved.
Is it possible in Excel to automatically rename all the tabs of a workbook in one move in a sequential format - eg renamimg 52 weekly tabs Week 1, Week 2, Week 3 etc.
I'm trying to create a single column of stock bin codes for eventual conversion to barcodes. The bin codes run like this:
A1A A1B A1C - etc. to A1H, then the sequence starts again with: A2A A2B A2C etc. up to A6H, when the 1st character changes to B and the sequence starts again
This needs to continue until the sequence reaches Z6H
How to do this without having to enter each code manually?
I have written some code for a friend of mine, but I cannot test it because I currently don't have a printer attached to this computer. I am confident that it will work, but I would like a second set of eyes to confirm that I havent missed anything.
I have a userform with two textboxes. One of them asks how many copies of the invoice to print out, and the second one asks what invoice number to start with. THe invoice numbers are recorded in Cell I1.
[Code]......
Also, for the future, in a situation like this, is there anyway to test if the code would work without a printer? I tried substituting printpreview, but my computer locked up.
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 have written some code for a friend of mine, but I cannot test it because I currently don't have a printer attached to this computer. I am confident that it will work, but I would like a second set of eyes to confirm that I havent missed anything.
I have a userform with two textboxes. One of them asks how many copies of the invoice to print out, and the second one asks what invoice number to start with. THe invoice numbers are recorded in Cell I1.
In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805).
HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column).
Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?
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.
You will probably find this very easy but I am having all sorts of trouble making it work as I want to! Basically I need to do the following procedure...
1) Open an Inputbox to collect an eight digit number 2) Insert a column in A:A 3) In A1, enter a col header (URN) 4) In A2, enter the number that was collected in the inputbox 5) Enter sequential number from A2+1 to the last row
Ideally, this would be randomised, so after stage 2 do RAND(), sort, and clear contents, but if I can get the main part right I'm sure I can work that out!
They correspond to a date from a cell and I just format one for "Monday" through "Saturday". In another module I'm opening a file that has worksheets named "Monday" through "Saturday" as well. I need to do the same thing to each of these sheets so I'm trying to setup a For/Next loop. I'm assuming you can't put a variable to a variable as I keep getting 'variable not defined' when I try to insert 'i'? Or am I just going about this all wrong?
For i = 1 To 6 Workbooks.Open ("J:AcctMgtITrepADIinf-v2-WE0922.xls") Sheets(dDDDD & i).Unprotect Password:="hownowbrowncow" Sheets(dDDDD1).Columns("C:E").Insert Shift:=xlToRight Sheets(dDDDD1).Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True With Sheets(dDDDD1) Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)) End With ConcName = "=B1&"", ""&C1" LastRow = RngCol.Rows.Count Sheets(dDDDD1).Range("$E$1:$E$" & LastRow).Formula = ConcName Next i
I have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red blue yellow green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.
Data A001
Data A002
Add next number (A003) when data entered in cell to left
How do I go about having a formula which will return the number of times 0 is repeated in a column sequentially?
e.g.:
0 1 0 0 0 0 0 1 0 0 0
The idea being that after row 7 (there have been 5 consecutive zeroes), the count would reset when it hits a value greater than zero, and then count again once it hits a zero again.
