Search Results

Search found 4783 results on 192 pages for 'excel vba'.

Page 21/192 | < Previous Page | 17 18 19 20 21 22 23 24 25 26 27 28  | Next Page >

  • Need to copy columns H,K,L From one excel workbook to new workbook using Excel Macro

    - by bhargav reddy
    I have a excel workbook A.xlsx with columns A through T, now i need to copy specific columns H,K,L to a new workbook which would be created while i run a macro. I was able to successfully copy a range of columns from one worksheet to another, but i am not finding a way to copy specific columns to a new workbook. Private Sub copy_sub() Sheets("Sheet1").Columns("H:K").Copy Sheets("Sheet2").Range("A1") End Sub

    Read the article

  • How to create a workboook specific Excel Add in

    - by Ankit
    I would like to create a excel Add in which creates some additional toolbars and Menu buttons. But I want this addin to load only when a specific workbook is opened. I dont want to load the Addin if anyother workbook is open. I would like to know what are the possible ways to solve this problem and what is the best approach to implement this Add in (XLA or VSTO or COM Addin)

    Read the article

  • VLOOKUP in Excel, part 2: Using VLOOKUP without a database

    - by Mark Virtue
    In a recent article, we introduced the Excel function called VLOOKUP and explained how it could be used to retrieve information from a database into a cell in a local worksheet.  In that article we mentioned that there were two uses for VLOOKUP, and only one of them dealt with querying databases.  In this article, the second and final in the VLOOKUP series, we examine this other, lesser known use for the VLOOKUP function. If you haven’t already done so, please read the first VLOOKUP article – this article will assume that many of the concepts explained in that article are already known to the reader. When working with databases, VLOOKUP is passed a “unique identifier” that serves to identify which data record we wish to find in the database (e.g. a product code or customer ID).  This unique identifier must exist in the database, otherwise VLOOKUP returns us an error.  In this article, we will examine a way of using VLOOKUP where the identifier doesn’t need to exist in the database at all.  It’s almost as if VLOOKUP can adopt a “near enough is good enough” approach to returning the data we’re looking for.  In certain circumstances, this is exactly what we need. We will illustrate this article with a real-world example – that of calculating the commissions that are generated on a set of sales figures.  We will start with a very simple scenario, and then progressively make it more complex, until the only rational solution to the problem is to use VLOOKUP.  The initial scenario in our fictitious company works like this:  If a salesperson creates more than $30,000 worth of sales in a given year, the commission they earn on those sales is 30%.  Otherwise their commission is only 20%.  So far this is a pretty simple worksheet: To use this worksheet, the salesperson enters their sales figures in cell B1, and the formula in cell B2 calculates the correct commission rate they are entitled to receive, which is used in cell B3 to calculate the total commission that the salesperson is owed (which is a simple multiplication of B1 and B2). The cell B2 contains the only interesting part of this worksheet – the formula for deciding which commission rate to use: the one below the threshold of $30,000, or the one above the threshold.  This formula makes use of the Excel function called IF.  For those readers that are not familiar with IF, it works like this: IF(condition,value if true,value if false) Where the condition is an expression that evaluates to either true or false.  In the example above, the condition is the expression B1<B5, which can be read as “Is B1 less than B5?”, or, put another way, “Are the total sales less than the threshold”.  If the answer to this question is “yes” (true), then we use the value if true parameter of the function, namely B6 in this case – the commission rate if the sales total was below the threshold.  If the answer to the question is “no” (false), then we use the value if false parameter of the function, namely B7 in this case – the commission rate if the sales total was above the threshold. As you can see, using a sales total of $20,000 gives us a commission rate of 20% in cell B2.  If we enter a value of $40,000, we get a different commission rate: So our spreadsheet is working. Let’s make it more complex.  Let’s introduce a second threshold:  If the salesperson earns more than $40,000, then their commission rate increases to 40%: Easy enough to understand in the real world, but in cell B2 our formula is getting more complex.  If you look closely at the formula, you’ll see that the third parameter of the original IF function (the value if false) is now an entire IF function in its own right.  This is called a nested function (a function within a function).  It’s perfectly valid in Excel (it even works!), but it’s harder to read and understand. We’re not going to go into the nuts and bolts of how and why this works, nor will we examine the nuances of nested functions.  This is a tutorial on VLOOKUP, not on Excel in general. Anyway, it gets worse!  What about when we decide that if they earn more than $50,000 then they’re entitled to 50% commission, and if they earn more than $60,000 then they’re entitled to 60% commission? Now the formula in cell B2, while correct, has become virtually unreadable.  No-one should have to write formulae where the functions are nested four levels deep!  Surely there must be a simpler way? There certainly is.  VLOOKUP to the rescue! Let’s redesign the worksheet a bit.  We’ll keep all the same figures, but organize it in a new way, a more tabular way: Take a moment and verify for yourself that the new Rate Table works exactly the same as the series of thresholds above. Conceptually, what we’re about to do is use VLOOKUP to look up the salesperson’s sales total (from B1) in the rate table and return to us the corresponding commission rate.  Note that the salesperson may have indeed created sales that are not one of the five values in the rate table ($0, $30,000, $40,000, $50,000 or $60,000).  They may have created sales of $34,988.  It’s important to note that $34,988 does not appear in the rate table.  Let’s see if VLOOKUP can solve our problem anyway… We select cell B2 (the location we want to put our formula), and then insert the VLOOKUP function from the Formulas tab: The Function Arguments box for VLOOKUP appears.  We fill in the arguments (parameters) one by one, starting with the Lookup_value, which is, in this case, the sales total from cell B1.  We place the cursor in the Lookup_value field and then click once on cell B1: Next we need to specify to VLOOKUP what table to lookup this data in.  In this example, it’s the rate table, of course.  We place the cursor in the Table_array field, and then highlight the entire rate table – excluding the headings: Next we must specify which column in the table contains the information we want our formula to return to us.  In this case we want the commission rate, which is found in the second column in the table, so we therefore enter a 2 into the Col_index_num field: Finally we enter a value in the Range_lookup field. Important:  It is the use of this field that differentiates the two ways of using VLOOKUP.  To use VLOOKUP with a database, this final parameter, Range_lookup, must always be set to FALSE, but with this other use of VLOOKUP, we must either leave it blank or enter a value of TRUE.  When using VLOOKUP, it is vital that you make the correct choice for this final parameter. To be explicit, we will enter a value of true in the Range_lookup field.  It would also be fine to leave it blank, as this is the default value: We have completed all the parameters.  We now click the OK button, and Excel builds our VLOOKUP formula for us: If we experiment with a few different sales total amounts, we can satisfy ourselves that the formula is working. Conclusion In the “database” version of VLOOKUP, where the Range_lookup parameter is FALSE, the value passed in the first parameter (Lookup_value) must be present in the database.  In other words, we’re looking for an exact match. But in this other use of VLOOKUP, we are not necessarily looking for an exact match.  In this case, “near enough is good enough”.  But what do we mean by “near enough”?  Let’s use an example:  When searching for a commission rate on a sales total of $34,988, our VLOOKUP formula will return us a value of 30%, which is the correct answer.  Why did it choose the row in the table containing 30% ?  What, in fact, does “near enough” mean in this case?  Let’s be precise: When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter. It’s also important to note that for this system to work, the table must be sorted in ascending order on column 1! If you would like to practice with VLOOKUP, the sample file illustrated in this article can be downloaded from here. Similar Articles Productive Geek Tips Using VLOOKUP in ExcelImport Microsoft Access Data Into ExcelImport an Access Database into ExcelCopy a Group of Cells in Excel 2007 to the Clipboard as an ImageShare Access Data with Excel in Office 2010 TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window Latest Software Reviews Tinyhacker Random Tips DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010 Quickly Schedule Meetings With NeedtoMeet Share Flickr Photos On Facebook Automatically Are You Blocked On Gtalk? Find out Discover Latest Android Apps On AppBrain The Ultimate Guide For YouTube Lovers Will it Blend? iPad Edition

    Read the article

  • Display page numbers in a excel sheet generated using C#.NET

    - by constant learner
    Hello Stackers Does anyone have an idea on how to include or input the page numbers in the excel sheet generated using C# code. I use the libraries available in Microsoft.Office.Interop.Excel to generate the file. However by default in the output i cannot see the page numbers. I know to enable this via excel options (View -- Header and Footer ...) but i want to automate this via C#. Is this possible, if yes kindly share the snippet for the same. Thanks Constant Learner

    Read the article

  • Best language to use when exporting an excel file

    - by Aaron
    I want to write a macro program that takes in data from a text file and then arranges it in a specific manner in an excel file. I don't know which language has the best features for dealing with Excel. I prefer java, and I see someone made an api called JExcelApi, but I'm not sure about it's capabilities. I would like to be able to generate a graph automatically in excel based on the data in a certain column. Is this possible in any language? I would guess that Microsoft's VB or C# would have an advanced feature such as this, but I'm not sure. Thanks.

    Read the article

  • Export with VB to Excel and update file

    - by Filipe Costa
    Hello. This is the code that i have to export data to Excel. Dim oExcel As Object Dim oBook As Object Dim oSheet As Object oExcel = CreateObject("Excel.Application") oBook = oExcel.Workbooks.Add oSheet = oBook.Worksheets(1) oSheet.Range("A1").Value = "ID" oSheet.Range("B1").Value = " Nome" oSheet.Range("A1:B1").Font.Bold = True oSheet.Range("A2").Value = CStr(Request("ID")) oSheet.Range("B2").Value = "John" oBook.SaveAs("C:\Book1.xlsx") oExcel.Quit() I can create and save the excel file, but i can't update the contents. How can i do it? Thanks.

    Read the article

  • Dyanamic Chart Series Labels

    - by McVey
    I have some Visual Basic Code that creates a chart for each row. It sets the series values using this code: .SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8" What I am struggling with is how do I set the series labels? The series labels will always be the 1st row and be in the corresponding column. I know this is much simplier than the code above, but I am stumped. Any help is appreciated.

    Read the article

  • Mass saving xls as csv

    - by korki
    hi, here's the trick. gotta convert 'bout 300 files from xls to csv, wrote some simple macro to do it, here's the code: Dim wb As Workbook For Each wb In Application.Workbooks wb.Activate Rows("1:1").Select Selection.Delete Shift:=xlUp ActiveWorkbook.SaveAs Filename:= _ "C:\samplepath\CBM Cennik " & ActiveWorkbook.Name & " 2010-04-02.csv" _ , FileFormat:=xlCSV, CreateBackup:=False Next wb but it doesn't do exactly what i want - saves file "example.xls" as "example.xls 2010-04-02.csv", what i need is "example 2010-04-02.csv" need support guys ;)

    Read the article

  • Pasting formatted Excel range into Outlook message

    - by Steph
    Hi everyone, I am using Office 2007 and I would like to use VBA to paste a range of formatted Excel cells into an Outlook message and then mail the message. In the following code (that I lifted from various sources), it runs without error and then sends an empty message... the paste does not work. Can anyone see the problem and better yet, help with a solution? Thanks, -Steph Sub SendMessage(SubjectText As String, Importance As OlImportance) Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment Dim iAddr As Integer, Col As Integer, SendLink As Boolean 'Dim Doc As Word.Document, wdRn As Word.Range Dim Doc As Object, wdRn As Object ' Create the Outlook session. Set objOutlook = CreateObject("Outlook.Application") ' Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) Set Doc = objOutlookMsg.GetInspector.WordEditor 'Set Doc = objOutlookMsg.ActiveInspector.WordEditor Set wdRn = Doc.Range wdRn.Paste Set objOutlookRecip = objOutlookMsg.Recipients.Add("[email protected]") objOutlookRecip.Type = 1 objOutlookMsg.Subject = SubjectText objOutlookMsg.Importance = Importance With objOutlookMsg For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve ' Set the Subject, Body, and Importance of the message. '.Subject = "Coverage Requests" 'objDrafts.GetFromClipboard Next .Send End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub

    Read the article

  • Dynamic Chart Series Labels

    - by McVey
    I have some Visual Basic Code that creates a chart for each row. It sets the series values using this code: .SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8" What I am struggling with is how do I set the series labels? The series labels will always be the 1st row and be in the corresponding column. I know this is much simplier than the code above, but I am stumped. Any help is appreciated.

    Read the article

  • Allow paste in worksheet without overwriting locked cells

    - by jjeaton
    I have a protected worksheet that users would like to copy and paste into. I have no control over the workbook they are copying from. The protected worksheet has some rows that are available for data entry, and other rows that are locked and greyed out to the user. The users would like to be able to paste over the top of the entire worksheet from another random workbook and have all the cells available for data entry filled in, while the locked cells are undisturbed. In the current state, the user gets an error when they try to paste, because it cannot paste over the locked cells. Example: Worksheet 1: Act1 100 100 100 Act2 100 100 100 Act3 100 100 100 Worksheet 2: (The second row is locked) Act1 300 300 300 Act2 200 200 200 Act3 100 100 100 After copying/pasting Worksheet 2 should look like this: Act1 100 100 100 Act2 200 200 200 Act3 100 100 100 The values from worksheet 1 are populated and the locked rows are undisturbed. I've been thinking along the lines of having a hook where on paste, the locked cells are unlocked so that the paste can happen, and then are reverted to their original values and relocked. Is there some way I can loop through the cells in the clipboard and only paste cells where the target isn't locked? It is preferable to not create a separate button for paste, so there is less impact on the users, but if that's the only way, I'm not opposed to it. Currently, I plan on grouping the locked rows together, so that the data entry cells are contiguous, but then the accounts will be out of order, which is not preferred.

    Read the article

  • Clean up domain list in Excel - regex / macros?

    - by Tim
    I have a huge spreadsheet of domains that I need to clean up as follows: Remove all http:// (simple replace all - "http://" with "") Remove any www. (simple replace all - "www." with "") Delete any sub-domains (delete the actual row completely, not just the subdomain from the url) Remove anything after the domain extension (i.e. website.com/blah/blahbah/ becomes just website.com (simple replace all - "/*" with "", then replace all "/" with "") So what I'm left with is just a spreadsheet of clean domains like "website.com". I think I've got 1, 2 and 4 sorted (as above), but I'm really struggling with 3. Any ideas? Can I do this with regexp / vba, and actually delete the row completely? Sample data: http://www.scholastic.com/kids/stacks/games/ http://imgworld.teamworkonline.com/ http://topfreegraphics.com/ http://www.workcircle.co.uk/ http://www.healthycanadians.gc.ca/index-eng.php http://gsociology.icaap.org/methods/soft.html Post 1, 2 and 4 would leave me with: scholastic.com imgworld.teamworkonline.com topfreegraphics.com workcircle.co.uk healthycanadians.gc.ca gsociology.icaap.org It's those pesky sub-domains I need to just delete completely, just delete the row. I've realised I can't just search for 2 x ".", because obviously plenty of domain extensions (i.e .co.uk) include that. Any help appreciated.

    Read the article

  • How to populate Range variable from a Sub/Function call?

    - by Ken Ingram
    I am trying to get this sub to work but the operationalRange variable is not being assigned. Despite the fact that the function selectBodyRow(bodyName) works fine. Sub sortRows(bodyName As String, ByRef wksht As Worksheet) Dim operationalRange As Range Set operationalRange = selectBodyRow(bodyName) Debug.Print "Sorting Worksheet: " & wksht.Name If Not operationalRange Is Nothing Then operationalRange.Select Debug.Print "Sorting " & operationalRange.Count & "Rows." ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Clear ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add Key:=operationalRange, _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add Key:=operationalRange, _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(wksht.Name).Sort .SetRange operationalRange .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Else MsgBox "Body is not being Set" End If End Sub The Sub being called by the above Sub is: Function selectBodyRow(bodyName As String) As Range Dim rangeStart As String, rangeEnd As String Dim selectionStart As Range, selectionEnd As Range Dim result As Range, srchRng As Range, cngrs As Variant If bodyName = "WEST" Then rangeStart = "<-WEST START->" rangeEnd = "<-WEST END->" ElseIf bodyName = "EAST" Then rangeStart = "<-EAST START->" rangeEnd = "<-EAST END->" End If Set srchRng = Range("A:A") srchRng.Select Set selectionStart = srchRng.Find(What:=rangeStart, After:=ActiveCell, LookIn _ :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False) Set selectionEnd = srchRng.Find(What:=rangeEnd, After:=ActiveCell, LookIn _ :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False) Set result = Range(selectionStart.Offset(1, 0), selectionEnd.Offset(-1, 0)) result.EntireRow.Select End Function

    Read the article

  • Updating target workbook - extracting data from source workbook

    - by Allan
    My question is as follows: I have given a workbook to multiple people. They have this workbook in a folder of their choice. The workbook name is the same for all people, but folder locations vary. Let's assume the common file name is MyData-1.xls. Now I have updated the workbook and want to give it to these people. However when they receive the new one (let's call it MyData-2.xls) I want specific parts of their data pulled from their file (MyData-1) and automatically put into the new one provided (MyData-2). The columns and cells to be copied/imported are identical for both workbooks. Let's assume I want to import cell data (values only) from MyData-1.xls, Sheet 1, cells B8 through C25 ... to ... the same location in the MyData-2.xls workbook. How can I specify in code (possibly attached to a macro driven import data now button) that I want this data brought into this new workbook. I have tried it at my own location by opening the two workbooks and using the copy/paste-special with links process. It works really well, but It seems to create a hard link between the two physical workbooks. I changed the name of the source workbook and it still worked. This makes me believe that there is a "hard link" between the tow and that this will not allow me to give the target (MyData-2.xls) workbook to others and have it find their source workbook.

    Read the article

  • Variable type for Application on Time Events [on hold]

    - by user2931226
    Been Trying to figure out how to go about setting a Variable for some Application On Time events, But still not confident to do it. These events (Macro's) get called by another Macro then they wait for 10 - 20 minutes, then they don't Run. So have read that (EarliestTime argument) should be assigned to a variable to store it other wise it looses it when other things are running Help appreciated Thanks Sub settimers() Application.OnTime TimeValue(Range("$X$9").Text), "StartBlink" Application.OnTime TimeValue(Range("$W$11").Text), "StopBlink" End Sub

    Read the article

  • How to tell if 2 arrays share the same element.

    - by Ommit
    So this is a simpler form of my problem. Lets say I have 2 arrays. A= {1,2} and B={2,4,6}. If A and B share an element then delete that element from B. I know you can loop through and compare each element in A to each element in B, but there's got to be a better way!

    Read the article

  • Reverse - Link Cell to Textbox

    - by Daniel
    I have 5 worksheets and a textbox on each worksheet. I would like all the textboxes to display the same value, which would be whatever the user enters in the first textbox. So once a value is entered in textbox1 on sheet1, textbox2 on sheet2, etc. will be populated with the same value. I'm looking for something like changing linkedcell =WorksheetName!textbox1 although I'm pretty sure that is incorrect.

    Read the article

  • running a macro from an add-in

    - by every_answer_gets_a_point
    i have an add-in called book1. inside the addin there is a module called module1 which has a sub called addin1 i would like to run the macro addin1 from a different workbook i am trying to call this macro like this: Call Addin1 but that's not working and i tried: Call book1.xlam.Module1.AddIn1 which is not working either does anyone know how to run a macro that is within an add-in ?

    Read the article

  • Store #VALUE! #NUM! #REF! in variable.

    - by Ommit
    So a simple version of what I'm trying to do. Say I know there is an error in cell(1,1), furthermore I know it is either #num!, #ref! or #value!, I want to be able to store the respective error message in a variable, so I can print it to a different sheet. This is what I tried and it clearly failed. Sub FindAndPrintErrors dim Store as string If IsError(Range("A1"))) = True Then Store = Range("A1").value 'it breaks here' end if range("B1") = Store end sub I know I can do this but I wonder if there is a better way. Sub FindAndPrintErrors2 dim Store If IsError(Range("A1"))) = True Then temp = Range("A1").value 'it breaks here' if temp = "error 2029" then store = "#num!" ' and so on' end if range("B1") = Store end sub

    Read the article

  • Event(s) for opening the workbook AND worksheets

    - by KeyMs92
    I'm looking for an elegant solution to trigger an event for opening the workbook as well as opening different worksheets. I don't need seperate operations for each worksheet: they all trigger the same method. I know I can use the events Workbook_Activate / Workbook_Open and Workbook_SheetActivate at the same time but I don't know if this is 'the official way' to do it. Perhaps there's a way to do this with one event. I was also wondering if it is relevant in this matter where I put the code. I now have all the code inside "ThisWorkbook" and not in a "Module"...

    Read the article

< Previous Page | 17 18 19 20 21 22 23 24 25 26 27 28  | Next Page >