im 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 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() ....
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"
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 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....
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 am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies View RelatedI have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).
I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.
A B
1 150
2 200
3 250 50
4 120
5 260
6 157
I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.
View 5 Replies View RelatedI'm working on a stacked bar graph based on the below info...
Min
Mid
Max
N1
$21.00
$30.00
$38.00
[code]....
I'm trying to adjust the starting point so that it does not start at 0.
For example...
N1 - $21.00 - $38.00
N2 - $26.00 - $45.00
N3 - $30.00 - $53.00
is there a way to do this?
1) I have added multiple command buttons as options within different categories but when I select one, all others are reset. Is ther a way to section off the command buttons into groups so that say the first three act together, the next two act together and then the last four act together, allowing a total of three options within three different categories?
2) How do I get the options from the userform to the worksheet?
3) How do I put an input box on a user form?
I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.
View 9 Replies View RelatedI have a code that print previews a certain amount of rows that I define. This basically says print preview the first 10 lines down.
Can the code be modified so that i can select a start cell. So instead of always starting at A1 I could select say start at G1?
Is there a built-in function in Excel to sum subsequent numbers, starting from 1?
If I give for example 5 as input value to the function, it should return 1+2+3+4+5 = 15.
If I give 3 -> it should return 1+2+3 = 6 and so on.
I'm trying to display payroll numbers (eg 0001234), but the zeros are not displayed. How do I get this to happen?
I need some code to add to a macro that will delete customer account names that start with an "A" in column C. Specifically it needs to find customer names starting with the letter "A" and delete that entire row.
In row 6 column O I need for the formula to look at column I and determine if the month is 1, If yes then I need for it to determine if the date is one of the following,
Day(I6)>=1,Day(I6)<=5), If TRUE Then do the following
Cells(J6)*1.04)*2080/12
If False then
ElseIfDay(I6)<=6,Day(I6)=<12, If TRUE Then do the following
Cells(J6)*43.33)+((J6*1.04)*129.99
If False then
ElseIfDay(I6)<=13,Day(I6)=<19, If TRUE Then do the following
Cells(J6)*86.66)+((J6*1.04)*86.66
If False then
ElseIfDay(I6)<=20,Day(I6)=<26, If TRUE Then do the following
Cells(J6)*129.99)+((J6*1.04)*43.33
If False then
ElseIfDay(I6)<=27,Day(I6)=<31, If TRUE Then do the following
Cells(J6)*1.04)*2080/12, but put this into column P
If row 6 Column O if the month is greater then 1 then I need the formula to do the following,
=J6*2080/12
What I am trying to do is have excel take columns O through Z look back to column I and determine which month the performance review is due in. Then I need for it to determine which week the increase rate will be effective for. So for each month the “If the month is >1” would change to 2,3,4,5,…..and so on.
In addition I am going to have the formula look at the shift and if it =3 then (J68)+1.00, but for the increase I have to have it -1.00 then J6*1.04+1.00. I will also have to create a table that holds the max rate for each grade, and then figure out the formula to figure out this new rate.
I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
=IF(ISNA(MATCH("N06*";Sheet1!AI:AI;0));"error";INDIRECT(ADDRESS(MATCH("N06*";Sheet1!AI:AI;0)*1;1;1;T RUE;"Sheet1")))
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!$A$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.
View 7 Replies View RelatedI'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
I start an Excel program from a shortcut in different folders. I know that I can find the path of the Excel program in "Parent.Path". But I don't know how to find the path of the folder the shortcut was in. It should be findable because when I try to do something like <Save>, Excel knows the correct folder there.
An example: I open Excel in "Folder 1" using a shortcut to the Excel Program in "Folder 2". Parent.Path tells me "Folder 2". How do I find out what "Folder 1" is from inside Excel?
I have a form pop up when user runs a macro and it populates the form beginning with ActiveCell. How do I always populate the form beginning from column A (row based on ActiveCell)? It would be really helpful if I could just set cl to take the ActiveCell and back it up to column A.
View 7 Replies View RelatedI'm trying to do a vlookup but I want to be able to change the table array starting point depending upon a defined cell header. so
=vlookup(a1,sheet1!b1:c5,2,false) where b: would be defined by a header as
=vlookup(a1,sheet1!header name:header name+1,2,false).
So it will work much in the same way as match does for VLOOKUP
($A2,'Sheet 1'!$A:$FG,MATCH(AP$1,'sheet 1'!$1:$1,0),FALSE) but the array needs to change.
By doing this my vlookup will work automatically depending upon the header.
How can I create a sequence with a formula starting in A1 that will give 1,1,2,2,3,3 and so on horizontally?
View 3 Replies View RelatedI would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.
After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.
None of the functions I have see so far will facilitate this...
Is it possible to set the starting path for the open dialog - using expression. GetOpenFilename so that the contents of the correct folder are visible immediately?
View 3 Replies View Related