I need to number some rows starting at row 5. Need to place the number in column AB of each row.
Needs to quit numbering at last record (column F will have information in the last record)
i need this to be a macro, never done an autonumber macro before....
If you look at schedule.jpg you will see a how our work schedules are formatted.
Then if you look at schedule2.jpg, you will see what I wish to convert it to.
Now I'm looking for a way to search by the name of our employee (2 seperate sheets) and then by the time which corresponds to the first "1" in the chart under that name and the last "1" in the chart under that name.
So once I fill out our schedule.jpg, it will auto-populate the times on schedule2.jpg.
Is this even possible?
If necessary, I will be able to put both schedule and schedule 2 on one sheet.
On schedule.jpg time starts at 7:00 - Column C
and ends at 8:00 - Column AC
I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.
View 7 Replies View RelatedNot something I've ever had to do before as i generally work with data sheets, i'm creating a document to print but need the page number to start from a set value and not default "1" of "10" etc,
I need it to start from say "5" of "15" rather than the "1" of "10",
Its simple to do in Word, but like i say not something I've ever had to think about in Excel.
I have a spreadsheet where I track resources needed on a project. Each resource that is selected in the resource column has a corresponding % avg and an average number of months where the resource is used. % Avg and # of Months are found in a different worksheet.
What I am looking to do is, for example: if resource A will be on the project starting in May 2015 and assigned at 30% for 7 months, I want to populate 30% starting in the colunm labeled May 2015 and copy the 30% so that a total of 7 months are populated. Also, if any of the values change (i.e: the Resource Name or the Start Month) I want to erase all the percentages that were entered in the row. How can I achieve that with a VBA code? I have formulas that retrieve the # of Months and the % Avg already.
These are the columns that I have in my spreadsheet:
Resource NameStart MonthJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15
I am having trouble setting up my cycle count spread sheet. For instance if my starting number is 1000 and when it reaches 0 or below it starts to count in minus when I just want it to automatically reset to 1000
View 3 Replies View RelatedI'm using the VBA code below for a piece of code.
[Code] .....
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
Something like:
[Code] .....
I want formula which can automatically populate the amount based on the number of months starting from the date vehicle is received. For Eg.
Nissan patrol Rent is AED 5800/- per month & we deliver it on month Sept 2011 for 24 months then i want excel to automatically populate AED 5800/- for next 24 months starting from Sept 2011.
I have Ctrl+x to run a macro and it is not working. I have used it before fine, but made modifications this time and am having problems now. I'll just show the code for now and can give more details if the fix is not obvious.
Sub PO_Flash_Report_Detail() ....
We're trying to use one template to enter data with users being able to start entry from a number of different starting points. this changes they way the look-ups have to work. The attached sample explains it a whole lot better.
View 2 Replies View Relatedim trying to learn abit of vba and have put some macros together however cannot figure out where to start for the following
when the workbook opens it checks sheet2 : c10 ="Y" then sheet3! is displayed. If sheet2 : c10 = "N" then opens normally.
I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.
However, I can't seem to build a macro that will run from whatever cell I am in.
Everytime and try it just goes from the cell I was in when I recorded the macro.
For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.
I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
I would love to be able to click on an assigned button in a new sheet, and a pop box appears.
Then I can type in a word, click find, and the macro will go through my whole list of keyword phrases and find all the words STARTING with the word chosen in the pop up box.
Yes, I could just sort from a-z, find the word, then copy and paste etc.
But I work with large lists sometimes, and its just so much easier to type the word, click find and the macro returns the list of just the phrases with the starting word I've chosen
As a note. I did put a post up a while ago which a coder called Jindon solved which works perfectly. (A great job by the grand master)
This macro finds the words ending with.
So, what I would really like is exactly the same macro, but it finds words at the start of a phrases not the end of a phrase.
I'll post Jindon's macro now
Sub Find_Words_Ending_In()
Dim sTime As Single, res As String, msg As String, x As Range
Range("c3:c" & Rows.Count).ClearContents
res = InputBox("Enter word to find")
If res = "" Then Exit Sub
sTime = Timer
On Error Resume Next................
I am using the following code to identify any blank rows in a sheet and delete them:
View 3 Replies View RelatedI have a macro that I run repeatedly in a certain type of spreadsheet that does an export on a selection, pulls up a "Save As" message box and saves as a text file. There are 9 sheets in the workbook that contain the same data analysis for a set of samples, the only difference between them being the number of data (e.g. some may have 1500, 1400, 1600, etc.). I only select three columns of data to export (I6:K????) that contain counts (1,2,3,....), x-coordinates and y-coordinates. The counts column (I) uses a formula that only counts if there is an x-coordinate next to it (Col J).
[Code].....
Generally, what I do to make quick work out of selecting the variable ranges is to select Sheets 03-11 (the first nine sheets) and select K6:I6 (starting w/ K6). Then, on each individual sheet, I do CTRL+SHIFT+DOWN to select all the relevant data (if I started my selection with I6, then it would select all cells that contained formulas which may or may not have x,y-coordinates adjacent to them). Once the data is selected (I6:K????), I run the export macro and save the data as text. I would like a macro that can automate the selection for each Sheet 03-11, excluding Sheets "all", "data" and "summary", and run the export macro, first prompting me for a file location and a file prefix. When the text file is saved, it uses the file prefix and Worksheet name to build the filename, i.e. "pathprefix_wkshtname".
I've attached an example workbook : 20120511_Au-cit_pH5_test.xlsx
Can weeknum work with the week starting on saturday instead of sunday or monday?
if not, what are my options?
i wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....
View 9 Replies View RelatedI'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.
View 3 Replies View RelatedI'm wanting a function to display the largest number starting with 7,8 and 9.
I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.
I need:
One displaying the largest number in the group starting with 7.
One displaying the largest number in the group starting with 8.
And one displaying the largest number in the group starting with 9.
Could I make use of an IF function and the MAX function?
Something like:
M20:
=IF("1st digit = 7", "display the largest of the list")
M21:
=IF("1st digit = 8", "display the largest of the list")
M22:
=IF("1st digit = 9", "display the largest of the list")
I've used =MAX(H5:H75), but this always displays the top "9" number.
I want something so I can show the top 8xxx number, and the top 7xxx number too.
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View RelatedI need to make a series of numbers and letter auto fill in a spread sheet
Example
2650A
2650B
2650C
And so on.
I have been trying to "auto number" property ID's
I am putting a database of properties all over the world and can't find a way to autonumber based on property criteria.
For example:
Mexico
Tijuana
Florido
Industrial
[Code]....
Incremental in the number must be when a property meets the same criteria, that way the end will be "0002" in each case above.
I would like that Excel can display which is the next number following once the criteria is resumed, is this possible?
I have a database to which I am connecting a form, I need to have a unique ID for each record and due to the number of tables need to include letters in my numbering (ex. U00001, for Users; B00001, for Books; and V00001, for videos.) I have adjusted a very helpful macro I recieved from Roy Cox and am currently trying this code on the "user form":
Count_Row = 1
DATABASE_RECORDS = Sheets("Users"). Range("B1:B10000")
'To identify the next blank row in the database sheet
For Each DBRECORD In DATABASE_RECORDS
If DBRECORD <> "" Then Count_Row = Count_Row + 1
RowNum = Count_Row
X = RowNum - 1
Sheets("Users").Range("A1" & RowNum) = "U000" & X
Next DBRECORD
This is supposed to find the fill the "A" (ID) column after the "B" Column has been filled. Currently It is placing U0001 in cell A12 when all that is in the sheet is headers.
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
I've created an Invoice Template in excel. Each time I open it I'd like a certian cell "D3" to increase by one number starting at 2000. Each time this is opened a new customer information is input and then saved to their file. Please give me the very basicis on how to do this. I've already gone into other forums which have provided a code, but I have had any luck getting ti too work.
View 9 Replies View Relatedi have been trying to write an Excel sheet in 2007
that can be given a random amount of (money) and show
me the best way to spend it.
Weapons:
Name - Power - Cost
1 - 10 - 20
2 - 20 - 40
3 - 40 - 80
4 - 80 - 160
5 - 160 - 320
6 - 320 - 640
So i have 40K to spend, i want to eneter 40000 into a box and it
will auto populate the number of units i can buy, always setting the weapon
#6 as the priority.
How to convert number/text to text/number?
example
a=1
b=3
c=5
Let's say
1) column A1 value is "35", any formula/macro can auto convert to "bc" at B1?
2) column A1 value is "ab", any formula/macro can auto convert to "13" at B1?