where to find a spreadsheet which has a macro to perform the FFT on data with > 4096 points? It seems Excel's internal FFT is limited to this amount. I searched the forum and elsewhere on web and have not found what I am looking for. I wish to enter the data in a column of the spreadsheet, preferably in a named range such as "Data" and have a macro perform the FFT.
View 3 RepliesExel 2010 cells formatted to Time
need to look at h1 and h2 if no data ignore
other wise add data together if total exceeds 08:45
then deduct 45min from h1
if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only
Example
h1= 5:00
h2= 2:00
h3= 8:00
h1=
h2=
h3=
h1= 8:00
h2= 2:00
h3= 7:15
I am looking to implement the "Compare Documents" function (available in Word 2010) in Excel 2010 through VBA programming between different text contained in two cells.
In Word this function works quite well (not perfectly), but it highlights in different ways which part has been deleted and which one has been added between an "original" document and a "revised" one.
For the nature of my job, I need to do this on a daily basis and I used to output text from Excel to Word, then compare the two text, and then copy it back to Excel.
Here comes the problem: since in Word the text is formatted (and what I'm looking for is formatted/highlighted text as output), I can't just paste it in Excel as it is: any editing, merging, splitting done on the pasted text (that eventually I need to do) makes the formatting disappear (above all with VBA functions, that can only output data and can't format it).
In other words: given two cells containing different text, I would like to be able to fill a third cell with text formatted accordingly to the TextDiff output between the two original cell.
E.g.:
INPUT:
Cells(1,1).Value2 = "my name is Andrea and I like jogging" (original)
Cells(1,2).Value2 = "my name is Giovanni and I like running" (revised)
OUTPUT:
Cells(1,3) wll contain: "my name is AndreaGiovanni and I like joggingrunning"
Obviously, since UDF doesn't allow formatting of cells, I would need to adjust the main Sub for each pair of document I have to revise, but that won't be the problem: what I need is the engine. It's been two years and a half that I do advanced VBA programming at work but it looks like I can't grasp the rationale behind the LCS algorithm.
I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.
So for example:
Sheet1 says the following on column N:
cat
dog
lion
bear
Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear
Column B
B1: 2
B2: 8
B3:15
I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.
I have a text box that is set to "=B1". B1 is a cell from an Access table import with memo format. The text memo is long, probably on average between 1000-2000 characters. I have set up the text box to automatically resize for the text, but the text stops at what I assume is 255 characters, even though cell B1 displays the entire memo.
How can I set it up so that this text box (or any other similar shape) displays the text and retains its resize/wrap property?
This is done in Excel 2010.
I'm using a Private Sub Worksheet_Change(ByVal target As Range) macro on Worksheets(2) to perform certain actions when a selection from Worksheets(1) is pasted to Worksheets(2). Before ending the Worksheet_Change sub I'd like it to deselect the copied selection on Worksheets(1). I've tried to do this by selecting Worksheets(1) and moving the cursor, but VB ignores the "With Application.Worksheets(1)" instruction. Removing "Private" from the sub heading makes no difference.
Is it possible do this with a Worksheet_Change routine? If so, how?
Excel 2010 / Win 7.
I have some code that imports a csv file into a workbook. This works fine when i use a file named .csv. I was hoping to use a random file name (in this case .bmhs) so that we can determine which files we need to import.
The problem i have is when importing a '.bmhs' file the data comes in column A and is a comma seperated list. If i use .csv then the commas are used as the column seperators (which i need).
impFle = Application.GetOpenFilename(filefilter:="BMHS Files, *.bmhs", Title:="Select Import File")
would give me 1,2,3,4 all in column A
impFle = Application.GetOpenFilename(filefilter:="CSV Files, *.csv", Title:="Select Import File")
would give me 1 in column A, 2 in column B, 3 in column C and 4 in column D.
Is there any way that i can maintain using my own file name (.bmhs) but have excel treat it as a csv file.
I am using lesson 33 of Excel VBA and Macros with Mr Excel as my guideline. The coding is as follows:
Dim WBO As Workbook 'original workbook
Dim WBN As Workbook 'individual data workbooks
Dim WSL As Worksheet 'List of files worksheet
Dim WSN As Worksheet
Set WBO = ThisWorkbook
Set WSL = WBO.Worksheets("List")
Set WSD = WBO.Worksheets("Data")
[Code] ......
I have indicated above with ( ) the beginning of my problem.
From the workbook with the list of workbooks to open, in each case I want to refer to a worksheet in each workbook
named "Report" and grab all the rows with data exept heading (rows 1 & 2) and 9 columns.
How do I get WSN in the code above to include the worksheet named "Report" or do I need different coding. Using Windows 7 & Excel 2010.
I am trying to set up a simple sports picking list using Excel 2010. I would like to be able to carry out several filers and then extract results to a new sheet to allow printing.
For example, there are four available terms and I would like to be able to filter by term/sport and student. Ideally I would like to be able to add a command button once this is working.
I use Excel 2010, 64-bit-version with Windows 7. I have one workbook, where whenever I create a formula that references to one specific sheet, the Excel crashes and cannot be quitted even with Task Manager. I have to restart the whole system.
I do not have any worksheet events in either sheet. This is the same workbook that crashes if all cells are selected from the top-left corner of the worksheet.
I tried by deleting unnecessary COM add-ins but that didn't work.
I am using excel 2010, windows 7. When i make a new worksheet or update it randomly blanks out lines that already have information in them. It blanks out the row number also.
View 1 Replies View RelatedI need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
I have a workbook with worksheet like 2a, 2b, 2c, 2d, 2e, 3a, 3b...etc Is it anyway i can make a tab which will hide a group of worksheet (like 2a,2b....2e) together and unhide all the worksheet with another click on it? becasue i got too many worksheet to show in the bottom..
View 2 Replies View RelatedI am using Excel 2010 64-bit (with SP2 and all other updates installed).
I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)
I need to sort this worksheet as follows:
1. By background color, so yellow is on top, white is on bottom.
2. Then, within the yellow rows, data in column J.
3. Then, within the white rows, data in column A.
Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.
Is there a way to combine these steps, perhaps with a macro?
I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.
I have an excel file with three worksheets in it. I can happily protect each sheet with a different password but what i want to be able to do is the following:
a. have a global administrator password for all the sheets - so i assume set the same password to protect the whole sheet
b. have individual passwords that only allow the user to insert rows and change data within restricted columns, i.e. can only edit columns A:Z and cannot manipulate any formulas within that range A:Z
I have excel 2010 if that makes any difference.
I need to create a separate PDF file for each Excel Worksheet, each worksheet has a different name.
I am using Excel 2010
I am using excel 2010.
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
Excel is not responding to Shift - F9.
I have created a worksheet and password protected, I know the password but now want to send to someone and remove the password protection option instead of them having to use a password to open it.
View 5 Replies View Relatedhow to use the offset function to create a dynamic range in Excel 2010. An in-house Excel form I work with spans columns A thru P and has three sections. In Section 2 the user fills in employees who are requesting to work overtime. Section 2 starts row 12 and ends row 61. It's sometimes necessary to add rows to this section. How do I make this section/range dynamic using the offset function?
View 1 Replies View RelatedI have a workbook that filters the data on the worksheet "Reports" this then adds new worksheets and copies and pastes the relevant data that has been sorted, this is done by the name in column "B".
What I need is to rename the new worksheets with the data that is in cell "B2" of the new sheets.
Using excel 2010.
Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
I have a workbook that is doing something I don't understand. In several places I am using arrays to transfer/modify information, then placing the array back onto the worksheet, and this works perfectly. The following code... doesn't.
(I've cut out unnecessary code like variable declarations - all my integer items are declared as Long)
Code:
Dim varA As Variant, varB(10000, 1) As Variant
Set wkDest = Sheets("a")
erB = 0
For lpTerm = 1 To 4
[Code]...
and several other ways. What happens is just null output, a full column of nothing. However, when I expand my worksheet range to two columns, all the data from varB appears - it is shifted down one column and row. So my data starts at .cells(2,2) instead of .cells(1,1). The data is all perfect, I've verified it left and right.... I just can't figure out why it's getting shifted like this.
Excel 2010. How/where to define userform object?
Button on worksheet has following code:
Sub Button1_Click()
Load UserForm1
UserForm1.Show
End Sub
Execution of this code generates following error:
Run time error 424
Object required
How/where to define userform object?
I need a formula or array to perform the following in Excel 2010:
I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.
sample feedback report.xlsx
In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.
I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.
I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.
View 4 Replies View RelatedI have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
i found this code...
Code:
Sub Button1_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim SNfound As String
'Your sqlserver 2008 connection string
Const stADO As String = "Provider=SQLOLEDB.1;" & _
"" & _
[code].....
but i dont see where to put in the Database object...
The Database it needs to connect to in SQl is called
The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal
using Excel 2010 connecting to SQL 2008
I am trying to add the sheet name to the center footer for each worksheet in a workbook. The workbook has about 80 sheets and it is cumbersome to do this manually. I am using Excel 2010.
I have tried to record a macro capturing what I do manually, but when I run the macro on another sheet, it does not add the sheet name to the center footer.
I have tried searching for a macro online and the ones I have found just crash excel.
I know I am probably missing something obvious in my macro code.
I know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.
How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.
How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code:
Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then
[code]....