I have a large spreadsheet set up with multiple rows, I have the formula -
=a5&"repeated text"&c5
this brings back 2 results
test repeated text test
repeated text
In the instance this is the latter with no combined cell vale, how would I mass delete them?
I am trying to make certain changes to the page setup on all sheets in a workbook. Here is my code but something is not working. I think I need to somehow select the next sheet because the macro is only performing the task on the active worksheet. My Visual Basic skills are somewhat limited so I'm sure this is not the most efficient way.
View 3 Replies View RelatedI am trying to do the following: I want to replace the values in A1:A3 with "0" and clear the contents of B1:B3 for all worksheets that contain "ILA" in the worksheet name. Is there a simple way to do this or another thread that already answers this question? I tried to search on the internet but found nothing useful. Here is a sample file (NOTE: there are no macros in this workbook even though it is saved as an .xlsm).
Clear and replace macro on multiple tabs.xlsm
I am taking an online computer class and when i enter a formula (=c5/c11) and then press f4, to make the cell c11 absolute, this does not work. Instead it brings me the "Project" right side mini screen. Is there a way to program the f4 key to return absolute results?
View 5 Replies View RelatedI'm developing a financial spreadsheet.
I've got a cell "Taxable Income" and another cell "Tax on Taxable Income" in sheet2.
When I enter a value in "Taxable Income" I want "Tax on Taxable Income" to automatically calculate based on formulas in sheet1. I don't know how to get "Tax on Taxable Income" to determine which formula to apply from the different tax brackets.
For example:
If taxable income is <$18201, tax = $o
If taxable income is between $18201 and $37000, Tax on Taxable Income =0.19*Taxable Income
If taxable income is between $37001 and $80000, Tax on Taxable Income =3572+0.325*(Taxable Income - $37000)
The idea behind having formulas in a different sheet is so over time when tax rates/brackets change I only have to change the formulas on one sheet.
I know how to use basic AND, IF statements e.t.c but I can't seem to get the syntax right for this to work.
I would like to have a cell in which there is descriptive text and the results of a calculation. For example, in row 2, I want to state the current total number of items in inventory at the top of the sheet in the center of the used cells.
Inventory Total is: SUM(A4:A64,000) as of: Date()
I can't merge cells or manipulate the text in such a way to neatly bring the text and calculation together. And there are actually 2 calculations in the example above. One to SUM the items and one to add the date.
Possible to have two spreadsheets and then perform the following tasks
1. Check If the cell in column I from cell I4 and the spreadsheet varies in length is blank and if it is - see item 2
2. The data equals certain values i.e. CDE or CDE - QUO or CDE - PRO - see item 3
3. Perfom a VLOOKUP to another spreadsheet
If the routine stops at any of the items then move to the next cell
Data is on spreadsheet - Allocations.xls
Vlookup is performed on this spreadsheet - 001 - Allocations - Blocks
The Vlookup for example is: =VLOOKUP(A5,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536.9,FALSE)
i need formula that will look in Col A and perform a subtraction in Cell b1 between 2 value dates 28/12/08 -12/12/08. Though I could have done this manually but was wondering if there is formula which will avoid duplicate dates. So the formula is set and when i copy new data in Col A daily the formula should be able to pick 2 dates regardless where the second date starts at...
View 9 Replies View RelatedSum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.
Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].
Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.
Attached File : LoopingVBA.xlsx
I'm a fairly new user to using VBA and I'm currently running into a little snag. I'm trying tu create userform which will let me know if a person already exists in my sheet by looking up the last name in "A", then looking up his first name in "B", if such entry exists then display a message saying " Yes". If entry user has not been entered in the sheet then use a combox, to select the reason , then choose a date ( i have a working date picker ). Then entry should be entered in sheet (Name, Surname, Reason, date).
Whats working, I can use all of the above to enter all the info into the sheet, what I need to do is verify if user is alread in there, if not, then add it. The so on and so forth
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
I have 2 sheets with a formula in sheet 2 looking to pick up information from sheet 1. the formula in sheet 2 is:
=HLOOKUP(C8,'Cost-Effort'!$B$7:$AH$42,26, FALSE)
But C8 appears in W7 and X7 in sheet 1 and will only pick up the first Occurrence and I need it to pick up the second Occurrence for this specific task.
I have this excel workbook with VBA code that starts when the workbook is opened.
Every day at 12:00 the windows task scheduler opens the file (hidden). The problem is that my computer is not always turned on at 12:00 hence it would be better if I could store the file in the cloud and run task scheduler from there.
In an optimal world there also has to exist a dropbox connection.
I have a PDF sheet with info laid out like this:
Code:
Peter Paul Mary
Text Corporation
One two three street
Mars, New York 90000 USA
phone: (111) 555-2222
email: tellmewhy@yahoo.com
There are 10 names and addresses like this on each PDF page and the entire PDF file has 50 pages so to manually copy and paste each bit of info into separate cells on the spreadsheet's appropriate cells "Name, Company, Address, State, Phone, Email" e.t.c is too difficult.
I need a macro in a excel for the below scenario:
1)i have merged cells from A1:B3
2)if i click on a macro the output in the above cells should be as follows"
"Login to database"
3)if i run the same macro for the 2nd time the output should be:
"Are you done with the reporting?"
4)if i run for 3rd time the output should be:
"Logoff from database"
5)Say example if i run the same macro for 1o times i should get 10 different outputs....if i run for the 11th time the output should get repeat from 1st....
6)Output need not be in order....random also fine...
If possible gimme 2 different codes:
1)Output should be in the order wise from 1-10 and get started from1 again
2)Output should be random....
I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.
is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
I need to work with a spreadsheet with 15000 rows of data. By the time I link this file with my final file and add formulas, my file has reached a massive size and the following pop up error message appears.
View 3 Replies View RelatedI am using code that creates a new workbook and then copies a number of sheets to the new workbook. I am using Application. ScreenUpdating = False but as the sheets are being copied over, the Excel task bar button jumps around.
View 2 Replies View RelatedI am trying to create a task list from a master matrix of items to be completed. When fully populated the maters matrix should have approximately 200 items to be completed over the year. I want to create a monthly to do list in EXCEL based on the master matrix. Some reports will be due on various timeframes. The reports will also be assigned to different workers. I would also like to be able to provided brief instruction for each task. Items will be added and subtracted from the master list based on changing requirements. I would like each month to be a different tab in the workbook. Attached is a spreadsheet that better explains what I am looking to create.
View 7 Replies View RelatedI've used Excel 2003 to write a macro to analyse some date from our internal proprietary database. I use some Perl to generate a bunch of csv files from the data, and the run the macro to generate graphs and statistics from that. What I would like to do now is run this entire operation as a scheduled task, so that, every week or so, the data is downloaded, the processing is done on the csv files, and the results are saved to a location to be viewed on our internal site.
I'm assuming that the best way to do this would be somehow run something from the command line (to be embedded in a script) which basically says "1) Open this csv file 2) run this macro 3) save the output to this location", but I can't figure out how this can be done.
it is more than 5 days now that I try to perform calculation on pivot.
problem: I have one cell that I set to show values as "% of the column total" when try to perform the calculation through insert calculation field with other cell the calculation is not correct/ it seems like the "show values as "% of the column total"" disturbing the calculation resolve this thing
I have a table of data with two rows of unqiue identifiying information. I am trying to develop a formula that will allow me to do a vlookup twice, once for each row of unique identifying information to retrive the value in same row for the third column. For example:
apple red 50
aaple blue 75
aaple yellow 80
apple green 90
grape red 12
grape blue 17
grape yellow 45
grape green 41
So, I am trying to do a lookup where I can say what is the value in the third column for apple and red. Or, what is the value in the third column for grape and green.
The following code works great in selecting individual cells and forming a text string with them. What I need to do is to change this slightly andi instead of just reading the cell, have the preceding value added to the current value "(Cells (e,i)" and that new runnng total value is added to the "temp" string.
The concept is 12 cells represent Jan - Dec (numbered 1-12)
I'm trying to perform a running total; where Feb(2) = (Jan(1) + Feb(2)) and Mar(3) = Feb(2) + Mar(3).....
I tried setting x to do the running total and then passing it to "temp" but I kept getting mismatch errors, or buffer overflow errors, or out of memory errors.
Is there someone who can provide some assistance with the proper way to do this? ....
I am trying to write a macro that will allow me to add task to [url].... (task managing webpage) from data within excel. I could easily write a macro that emails the information to my toodledo email address, but this is not what I would like to do.ow to start the code utilizing Toodledo's API, which can be found here:
View 5 Replies View RelatedIve been looking at a bunch of examples, but everything that I have tried just gives me errors. what code to use to make a vbs script to run in windows task scheduler that will...
Open "J:GroupsBSHEETSSDANew EOD.xlsm"
Run the macro named "EODPrint"
Save the workbook
and close the workbook
Leaving excel open if other instances exist.
Can a macro from Excel VBA create a Task in Outlook?
I would like to have Excel create a task populated with a date in a given cell in Excel.
Basically, click a button and a task is creaed in Outlook based on the date in a given cell.
Is this possible?
What I do know is how to set up a task in Task Scheduler and I have it set to open my Excel file: Workbook1.xlsm. This task is set to run at 7am each morning. Workbook1.xlsm has a bunch of different macros in it.
What I'd like to do is to have each of those macros to run automatically when the file is automatically opened by Task Scheduler.
i have a validation list in A5 pointing to F6:F, i need the refmask_no to change according to the validation list in A5:A.
So if i choose from List LEADCASH the refmask_no= '100007995' etc
How can i automate the changing of refmask_no?
Sheet1 ABCDEF4Validation ListCorr _acc_no Mapping 5LEAD CASH ABNAMROrefmask_no = '100007997'
Corr_acc_noLead Set6 '100007995'LEAD CASH7 '100020741'LEAD CASH ABNAMRO8 '100020645'LEAD CASH AUDNZNR9
I currently use task scheduler to open a a spreadsheet at 4am every morning and run a macro. This macro refreshes data pulled from an accounting software. The macro also closes the spreadsheet at a specific time. After this is complete I have task scheduler open a separate spreadsheet and runs a macro to pull the data from the first spreadsheet into a dashboard presentation of sales data.
This all works fine...what I am looking to do is make sure both instances of excel get closed. Currently, this whole process is complete around 6am and when I get to work around 7 or 8 am there are two instances of excel still open. The workbooks are closed but excel is still open. When I am in the office I can just close these instances of excel but if I am out of the office these two instances of excel stay open and mess things up for the next mornings refresh.
Is there a way to make sure both instances of excel get closed? I am using the code below to close the spreadsheets.
Code:
Sub Closeworkbook()
ActiveWorkbook.Close True
End Sub
