Search Results

Search found 6455 results on 259 pages for 'excel vba guy'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 3/259 | < Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

  • excel date range help please

    - by Mark
    I need help with either a formula or a macro to help automate a grade sheets dates. We have class every monday or wednesday only. I would like to vlookup from a input table of each quarters date range (example Sept. 10 - Oct 24 and the code auto insert the date of every monday and wednesday in a row at the top of my grade sheet. Every year I use the same excel workbook I built to average and rate the grading with no problem, however i can't seem to get this one right. Any help would be greatly appreciated. Currently I have to enter each date by hand. Thanks Again for any help.

    Read the article

  • Excel chart on one column of date/times

    - by Segfault
    Hello, I have a list of date/timestamps, and I would like to plot these to a chart in Excel. What I'm looking for is a chart that shows "events per hour" or something similar. Can this be done easily if I just have one column of data (the list of timestamps)? I'm using excel 2007 and looking at two data sets. One is 56k events and the other 750, both over the span of a few days.

    Read the article

  • Set default expand/colapse state on pivot tables

    - by CLockeWork
    The Setup I have a pivot table in tabular form pulling data from an Analysis Services Cube. I want to calculate the number of days between two dates, but the setup will only allow me to pull in all date elements, not just the date. I’ve been able to deal with this easily enough by just grouping all the columns: The Problem The default state for the expand/collapse buttons in the image above is often collapsed, but that means the dates I need aren’t there and you have to open the group and manually expand them. This also happens in some random ways (as shown in the image) where only some rows expand. The Question I need a way to set these sections to always be expanded, so that the user never has to open the group to expand the rows. Ideally I’d like to avoid VBA because our end users often block it, but if that’s what’s needed then so be it. Is there a way to set my pivot table to never collapse it’s predefined groups? Note the end user is using Excel 2010

    Read the article

  • Excel not properly recalculating values

    - by gms8994
    I have an excel sheet with values in it (this sheet is generated by a custom perl script, but I don't think that's where the problem lies). In it, I have a formula: =sum(indirect(concatenate(address(6,column()),":",address(17,column())))) The purpose of this formula is to give me the SUM() of the cells in the current column, between rows 6 and 17. In Gnumeric Spreadsheet, as soon as I open the file, this works. But in Excel (both 2003 and 2007), opening the file gives #VALUE! errors in the fields with this formula, stating that the INDIRECT call with the values $B$6:$B$17 will result in an error. Here's the kink in the issue. If I edit the field (via F2), and make no changes, and hit enter, the values update. Also, it seems, if I save the file as .xlsx (Excel 2007 format), the values update upon opening. Unfortunately, I'm not sure that creating an xlsx is a possibility with the modules that I'm using, and many of our clients probably wouldn't be able to use it anyway. Any suggestions? Editing 200+ files every month for each client isn't going to be feasible, so if there's something I'm missing, please let me know.

    Read the article

  • Excel Help: Data Input Help

    - by B-Ballerl
    Everyday I download data from a site that will have rows each filled with individual data for clients. I'm able to input the data into excel as a whole but after that I'm having trouble figuring out how to put it into a chart. For example Web visits time. So say Client 1 stayed for 5 min increasing his total time on the site to 20 min and Client 2 stayed for 0 min keeping his time of 10 min and they were both registered on new years eve, and R1's last login was today and R2's was yesterday. (R for some reason repersents Client, no idea why...). Client 3 hasn't been on since he registered keeping his total at 4 min So my data would look something like this for Today (20110104) R1,20101231,20110104,20 R2,20101231,20110103,10 R3,20101231,20101231,4 And this for the day before (201101030), R1,20101231,20110102,15 R2,20101231,20110103,10 R3,20101231,20101231,4 I get about 200+ client rows each day where even the names of the Client list are changing. Is it possible to import the data each day and fill it in a excel sheet where the Client number is off on the left hand side in a table, and the amount of time (Whole Number ex. 4) each day it spends on the site extend to the right under it's specific date see Picture? I've manage to create a manual sheet but have been unsucessful at getting excel to do any of it for me. Here are two pictures:

    Read the article

  • Redimming arrays in VBA

    - by Mike
    I have 3 arrays of data, that are filled by reading off of an excel sheet, some of the points of data are missing and as such have just been entered into excel as "NA" so I want to look through my array and find each instance of these NA's and remove them from the array since the information is useless. I need to update all three arrays at the same time. Sub group_data() Dim country(), roe(), iCap() As String Dim i As Integer For i = 1 To 3357 country(i) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("C1").Offset(i, 0) roe(i) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("AP1").Offset(i, 0) iCap(i) = Workbooks("restcompfirm.xls").Worksheets("Sheet1").Range("BM1").Offset(i, 0) Next i End Sub So if I find a "NA" as one of the values in roe or iCap I want to get rid of that piece of data in all there arrays.

    Read the article

  • Exploring the Excel Services REST API

    - by jamiet
    Over the last few years Analysis Services guru Chris Webb and I have been on something of a crusade to enable better access to data that is locked up in countless Excel workbooks that litter the hard drives of enterprise PCs. The most prominent manifestation of that crusade up to now has been a forum thread that Chris began on Microsoft Answers entitled Excel Web App API? Chris began that thread with: I was wondering whether there was an API for the Excel Web App? Specifically, I was wondering if it was possible (or if it will be possible in the future) to expose data in a spreadsheet in the Excel Web App as an OData feed, in the way that it is possible with Excel Services? Up to recently the last 10 words of that paragraph "in the way that it is possible with Excel Services" had completely washed over me however a comment on my recent blog post Thoughts on ExcelMashup.com (and a rant) by Josh Booker in which Josh said: Excel Services is a service application built for sharepoint 2010 which exposes a REST API for excel documents. We're looking forward to pros like you giving it a try now that Office365 makes sharepoint more easily accessible.  Can't wait for your future blog about using REST API to load data from Excel on Offce 365 in SSIS. made me think that perhaps the Excel Services REST API is something I should be looking into and indeed that is what I have been doing over the past few days. And you know what? I'm rather impressed with some of what Excel Services' REST API has to offer. Unfortunately Excel Services' REST API also has one debilitating aspect that renders this blog post much less useful than it otherwise would be; namely that it is not publicly available from the Excel Web App on SkyDrive. Therefore all I can do in this blog post is show you screenshots of what the REST API provides in Sharepoint rather than linking you directly to those REST resources; that's a great shame because one of the benefits of a REST API is that it is easily and ubiquitously demonstrable from a web browser. Instead I am hosting a workbook on Sharepoint in Office 365 because that does include Excel Services' REST API but, again, all I can do is show you screenshots. N.B. If anyone out there knows how to make Office-365-hosted spreadsheets publicly-accessible (i.e. without requiring a username/password) please do let me know (because knowing which forum on which to ask the question is an exercise in futility). In order to demonstrate Excel Services' REST API I needed some decent data and for that I used the World Tourism Organization Statistics Database and Yearbook - United Nations World Tourism Organization dataset hosted on Azure Datamarket (its free, by the way); this dataset "provides comprehensive information on international tourism worldwide and offers a selection of the latest available statistics on international tourist arrivals, tourism receipts and expenditure" and you can explore the data for yourself here. If you want to play along at home by viewing the data as it exists in Excel then it can be viewed here. Let's dive in.   The root of Excel Services' REST API is the model resource which resides at: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model Note that this is true for every workbook hosted in a Sharepoint document library - each Excel workbook is a RESTful resource. (Update: Mark Stacey on Twitter tells me that "It's turned off by default in onpremise Sharepoint (1 tickbox to turn on though)". Thanks Mark!) The data is provided as an ATOM feed but I have Firefox's feed reading ability turned on so you don't see the underlying XML goo. As you can see there are four top level resources, Ranges, Charts, Tables and PivotTables; exploring one of those resources is where things get interesting. Let's take a look at the Tables Resource: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables Our workbook contains only one table, called ‘Table1’ (to reiterate, you can explore this table yourself here). Viewing that table via the REST API is pretty easy, we simply append the name of the table onto our previous URI: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1') As you can see, that quite simply gives us a representation of the data in that table. What you cannot see from this screenshot is that this is pure HTML that is being served up; that is all well and good but actually we can do more interesting things. If we specify that the data should be returned not as HTML but as: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1')?$format=image then that data comes back as a pure image and can be used in any web page where you would ordinarily use images. This is the thing that I really like about Excel Services’ REST API – we can embed an image in any web page but instead of being a copy of the data, that image is actually live – if the underlying data in the workbook were to change then hitting refresh will show a new image. Pretty cool, no? The same is true of any Charts or Pivot Tables in your workbook - those can be embedded as images too and if the underlying data changes, boom, the image in your web page changes too. There is a lot of data in the workbook so the image returned by that previous URI is too large to show here so instead let’s take a look at a different resource, this time a range: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15') That URI returns cells A1 to C15 from a worksheet called “Data”: And if we ask for that as an image again: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15')?$format=image Were this image resource not behind a username/password then this would be a live image of the data in the workbook as opposed to one that I had to copy and upload elsewhere. Nonetheless I hope this little wrinkle doesn't detract from the inate value of what I am trying to articulate here; that an existing image in a web page can be changed on-the-fly simply by inserting some data into an Excel workbook. I for one think that that is very cool indeed! I think that's enough in the way of demo for now as this shows what is possible using Excel Services' REST API. Of course, not all features work quite how I would like and here is a bulleted list of some of my more negative feedback: The URIs are pig-ugly. Are "_vti_bin" & "ExcelRest.aspx" really necessary as part of the URI? Would this not be better: http://server/Documents/TourismExpenditureInMillionsOfUSD.xlsx/Model/Tables(‘Table1’) That URI provides the necessary addressability and is a lot easier to remember. Discoverability of these resources is not easy, we essentially have to handcrank a URI ourselves. Take the example of embedding a chart into a blog post - would it not be better if I could browse first through the document library to an Excel workbook and THEN through the workbook to the chart/range/table that I am interested in? Call it a wizard if you like. That would be really cool and would, I am sure, promote this feature and cut down on the copy-and-paste disease that the REST API is meant to alleviate. The resources that I demonstrated can be returned as feeds as well as images or HTML simply by changing the format parameter to ?$format=atom however for some inexplicable reason they don't return OData and no-one on the Excel Services team can tell me why (believe me, I have asked). $format is an OData parameter however other useful parameters such as $top and $filter are not supported. It would be nice if they were. Although I haven't demonstrated it here Excel Services' REST API does provide a makeshift way of altering the data by changing the value of specific cells however what it does not allow you to do is add new data into the workbook. Google Docs allows this and was one of the motivating factors for Chris Webb's forum post that I linked to above. None of this works for Excel workbooks hosted on SkyDrive This blog post is as long as it needs to be for a short introduction so I'll stop now. If you want to know more than I recommend checking out a few links: Excel Services REST API documentation on MSDNSo what does REST on Excel Services look like??? by Shahar PrishExcel Services in SharePoint 2010 REST API Syntax by Christian Stich. Any thoughts? Let's hear them in the comments section below! @Jamiet 

    Read the article

  • Exploring the Excel Services REST API

    - by jamiet
    Over the last few years Analysis Services guru Chris Webb and I have been on something of a crusade to enable better access to data that is locked up in countless Excel workbooks that litter the hard drives of enterprise PCs. The most prominent manifestation of that crusade up to now has been a forum thread that Chris began on Microsoft Answers entitled Excel Web App API? Chris began that thread with: I was wondering whether there was an API for the Excel Web App? Specifically, I was wondering if it was possible (or if it will be possible in the future) to expose data in a spreadsheet in the Excel Web App as an OData feed, in the way that it is possible with Excel Services? Up to recently the last 10 words of that paragraph "in the way that it is possible with Excel Services" had completely washed over me however a comment on my recent blog post Thoughts on ExcelMashup.com (and a rant) by Josh Booker in which Josh said: Excel Services is a service application built for sharepoint 2010 which exposes a REST API for excel documents. We're looking forward to pros like you giving it a try now that Office365 makes sharepoint more easily accessible.  Can't wait for your future blog about using REST API to load data from Excel on Offce 365 in SSIS. made me think that perhaps the Excel Services REST API is something I should be looking into and indeed that is what I have been doing over the past few days. And you know what? I'm rather impressed with some of what Excel Services' REST API has to offer. Unfortunately Excel Services' REST API also has one debilitating aspect that renders this blog post much less useful than it otherwise would be; namely that it is not publicly available from the Excel Web App on SkyDrive. Therefore all I can do in this blog post is show you screenshots of what the REST API provides in Sharepoint rather than linking you directly to those REST resources; that's a great shame because one of the benefits of a REST API is that it is easily and ubiquitously demonstrable from a web browser. Instead I am hosting a workbook on Sharepoint in Office 365 because that does include Excel Services' REST API but, again, all I can do is show you screenshots. N.B. If anyone out there knows how to make Office-365-hosted spreadsheets publicly-accessible (i.e. without requiring a username/password) please do let me know (because knowing which forum on which to ask the question is an exercise in futility). In order to demonstrate Excel Services' REST API I needed some decent data and for that I used the World Tourism Organization Statistics Database and Yearbook - United Nations World Tourism Organization dataset hosted on Azure Datamarket (its free, by the way); this dataset "provides comprehensive information on international tourism worldwide and offers a selection of the latest available statistics on international tourist arrivals, tourism receipts and expenditure" and you can explore the data for yourself here. If you want to play along at home by viewing the data as it exists in Excel then it can be viewed here. Let's dive in.   The root of Excel Services' REST API is the model resource which resides at: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model Note that this is true for every workbook hosted in a Sharepoint document library - each Excel workbook is a RESTful resource. (Update: Mark Stacey on Twitter tells me that "It's turned off by default in onpremise Sharepoint (1 tickbox to turn on though)". Thanks Mark!) The data is provided as an ATOM feed but I have Firefox's feed reading ability turned on so you don't see the underlying XML goo. As you can see there are four top level resources, Ranges, Charts, Tables and PivotTables; exploring one of those resources is where things get interesting. Let's take a look at the Tables Resource: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables Our workbook contains only one table, called ‘Table1’ (to reiterate, you can explore this table yourself here). Viewing that table via the REST API is pretty easy, we simply append the name of the table onto our previous URI: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1') As you can see, that quite simply gives us a representation of the data in that table. What you cannot see from this screenshot is that this is pure HTML that is being served up; that is all well and good but actually we can do more interesting things. If we specify that the data should be returned not as HTML but as: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Tables('Table1')?$format=image then that data comes back as a pure image and can be used in any web page where you would ordinarily use images. This is the thing that I really like about Excel Services’ REST API – we can embed an image in any web page but instead of being a copy of the data, that image is actually live – if the underlying data in the workbook were to change then hitting refresh will show a new image. Pretty cool, no? The same is true of any Charts or Pivot Tables in your workbook - those can be embedded as images too and if the underlying data changes, boom, the image in your web page changes too. There is a lot of data in the workbook so the image returned by that previous URI is too large to show here so instead let’s take a look at a different resource, this time a range: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15') That URI returns cells A1 to C15 from a worksheet called “Data”: And if we ask for that as an image again: http://server/_vti_bin/ExcelRest.aspx/Documents/TourismExpenditureInMillionsOfUSD.xlsx/model/Ranges('Data!A1|C15')?$format=image Were this image resource not behind a username/password then this would be a live image of the data in the workbook as opposed to one that I had to copy and upload elsewhere. Nonetheless I hope this little wrinkle doesn't detract from the inate value of what I am trying to articulate here; that an existing image in a web page can be changed on-the-fly simply by inserting some data into an Excel workbook. I for one think that that is very cool indeed! I think that's enough in the way of demo for now as this shows what is possible using Excel Services' REST API. Of course, not all features work quite how I would like and here is a bulleted list of some of my more negative feedback: The URIs are pig-ugly. Are "_vti_bin" & "ExcelRest.aspx" really necessary as part of the URI? Would this not be better: http://server/Documents/TourismExpenditureInMillionsOfUSD.xlsx/Model/Tables(‘Table1’) That URI provides the necessary addressability and is a lot easier to remember. Discoverability of these resources is not easy, we essentially have to handcrank a URI ourselves. Take the example of embedding a chart into a blog post - would it not be better if I could browse first through the document library to an Excel workbook and THEN through the workbook to the chart/range/table that I am interested in? Call it a wizard if you like. That would be really cool and would, I am sure, promote this feature and cut down on the copy-and-paste disease that the REST API is meant to alleviate. The resources that I demonstrated can be returned as feeds as well as images or HTML simply by changing the format parameter to ?$format=atom however for some inexplicable reason they don't return OData and no-one on the Excel Services team can tell me why (believe me, I have asked). $format is an OData parameter however other useful parameters such as $top and $filter are not supported. It would be nice if they were. Although I haven't demonstrated it here Excel Services' REST API does provide a makeshift way of altering the data by changing the value of specific cells however what it does not allow you to do is add new data into the workbook. Google Docs allows this and was one of the motivating factors for Chris Webb's forum post that I linked to above. None of this works for Excel workbooks hosted on SkyDrive This blog post is as long as it needs to be for a short introduction so I'll stop now. If you want to know more than I recommend checking out a few links: Excel Services REST API documentation on MSDNSo what does REST on Excel Services look like??? by Shahar PrishExcel Services in SharePoint 2010 REST API Syntax by Christian Stich. Any thoughts? Let's hear them in the comments section below! @Jamiet 

    Read the article

  • Interacting with IE using sendkeys from Excel

    - by Thomas Egan
    I'm trying to write an application which uses values from excel and then switches to a web application using sendkeys. The problem I am having is that I cannot used sendkeys ("{ENTER}") or sendkeys ("^o") as I don't have the access for that. I'm trying to automate a very trivial admin task. I've thought about using the mouse to interact with the links as well as pausing and waiting for the user to just hit return but so far have been unable to come up with a solution. Do you think there is anyway around this? I have some VBA knowledge (enough to get me this far) but not a great deal.

    Read the article

  • Run VBA macro when condition is met

    - by Pieter
    Hello all, I'm creating a spreadsheet to train my numerical skills. Now, I use VBA macros to generate a new problem once the current one has been correctly solved. To do so, I still have to press a button in the worksheet, which costs time and is annoying. Is there a way that I can execute a macro when a certain condition is met? for instance: if A1 = "correct!" then <run macro> else <do nothing> Also, let me know if you're interested in the spreadsheet, I'd be happy to share it with you guys. best, Pieter

    Read the article

  • VBA Excel macro: use Range to act on a different worksheet

    - by David Oneill
    I am very much a beginner when it comes to VBA programming. I have a Macro that hides or shows columns based on the value in one cell: Sub HideColumnsMacro() Range("b8:o8").EntireColumn.Hidden = False v1 = Range("b2").Value + 1 If v1 < 12 Then With Range("b8") Range(.Offset(0,v1), .Offset(0, 12)).EntireColumn.Hidden = True End With End If End Sub I want to be able to get this same functionality when I change a cell on a different sheet. Is there a way I can tell this Macro to act on this sheet, when it is run from a different one?

    Read the article

  • Trim Cells using VBA in Excel

    - by Greg Reynolds
    I have what seems like a simple problem with some data in Excel. I have a lot of data with leading spaces pasted from a web table, and I would like to get rid of the initial space. I cribbed the following code (I am completely new to VBA), but it doesn't seem to work. When I step through it in the debugger it looks like an infinite loop. Any help would be appreciated! Sub DoTrim() For Each cell In Selection.Cells If cell.HasFormula = False Then cell = Trim(cell) End If Next End Sub

    Read the article

  • Highest populated dimension of an array vba

    - by Ommit
    Say i have an single dimension array (to keep it simple). Is there a simple way to tell how many entries are populated, or the highest dimension of populated entries, other than to loop through and count them? I know Ubound finds the highest dimension of the array but that's not what I need. Is there something like Ubound but it only find populated entries, or the highest dimension populated? Also, what if the array is multidimensional. I'm working in excel vba.

    Read the article

  • Excel VBA combox box disable

    - by Chase
    Hi all, I am trying to enable/disable a combobox based on the value or state of a second combobox in Excel 2007. I think my code should look something like this: Sub DropDown266_Change() If DropDown266.Index = 2 Then DropDown267.Enabled = False End If End Sub However, I am getting a run time error '424' saying an object is required. I am sure this is a very simple change, but I can't seem to figure it out. Let me know if you need more details. Thanks, Chase

    Read the article

  • Excel 2003 - VBA for looping through every cell in a row to provide attributes/formatting

    - by Justin
    say I want to make the first row of the excel ss something like this: .Rows("1:1").Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With only I want each individual cells to have the outline, not the entire selection. how can i say for each cell in row 1, do the above idea Thanks!

    Read the article

  • Excel 2003 VBA : how to paste a shape after selection

    - by Justin
    Just wondering how I can paste an object after I have selected it: sheet1.shapes("MyShape").select With Selection basically jsut wondering how to duplicate a shape object, or any object really. Eventually I am looking to use code to copy a shape object like above from Excel, and paste it into an access form automatically. Thanks!

    Read the article

  • Export to Excel from ASP.NET produces files unreadable with Excel Viewer

    - by MainMa
    Hi, I want to output some dynamic data from an ASP.NET website to Excel. I found that the easiest way which does not require to use Excel XML or to install Excel on server machine is to output data as a table and specify application/vnd.ms-excel type. The problem is when I do so and try to open the file in Excel Viewer, I receive the following error message: Microsoft Excel Viewer cannot open files of this type. Then nothing is opened. Even an easiest code such as: protected void Page_Load(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=Example.xls"); Response.ContentType = "application/vnd.ms-excel"; StringWriter stringWriter = new System.IO.StringWriter(); HtmlTextWriter htmlTextWriter = new System.Web.UI.HtmlTextWriter(stringWriter); dataGrid.RenderControl(htmlTextWriter); Response.Write("<html><body><table><tr><td>A</td><td>B</td></tr><tr><td>C</td><td>D</td></tr></table></body></html>"); Response.End(); } produces the same error. What can cause such behavior? Is it because the Viewer cannot read such files, but full Excel version can?

    Read the article

  • Excel Help: Userforms

    - by B-Ballerl
    I have developed a macro that does a whole bunch of things for me based on a few things. (Importing files). The file names are dated dd_mm_yyyy and right now I enter them into a sheet where the macro can call the information. Not really wanting this I designed a userform where the user could enter the "dd", "mm", "yyyy" and how many consecutive days of files there were. Ex. Say 28_06_2011.txt 29_06_2011.txt there would be one consecutive day. I want to be able to call the information entered in the user-form (day, month, year, and consecutive days) to use in the macro and have been unsuccessful because I don't know how to call that information. Is it similar to referring to a range in a worksheet? Thanks in Advance for any Help.

    Read the article

  • vba: what does ReDim Preserve do and simple array question

    - by every_answer_gets_a_point
    i am looking at someone else's vba excel code. they are doing ReDim Preserve dataMatrix(7, i) in both loops. what does this do? also, it seems like the second loop just overwrites the data in the first, loop, is that correct? Dim dataMatrix() As String Worksheets.Item("ETS").Select Do While Trim(Cells(r, 1)) <> "" Debug.Print "The line: ", Trim(Cells(r, 1)), r r = r + 1 dataMatrix(1, i) = Trim(Cells(r, 1)) ''file name dataMatrix(2, i) = Trim(Cells(r, 2)) ''sample type dataMatrix(3, i) = Trim(Cells(r, 3)) ''sample name dataMatrix(4, i) = "ETS" '' dataMatrix(5, i) = Trim(Cells(r, 5)) ''Response dataMatrix(6, i) = Trim(Cells(r, 6)) ''ISTD Response dataMatrix(7, i) = Trim(Cells(r, 10)) ''Calculated Conc i = i + 1 ReDim Preserve dataMatrix(7, i) Loop r = 5 Worksheets.Item("ETG").Select Do While Trim(Cells(r, 1)) <> "" Debug.Print "The line: ", Trim(Cells(r, 1)), r r = r + 1 dataMatrix(1, i) = Trim(Cells(r, 1)) ''file name dataMatrix(2, i) = Trim(Cells(r, 2)) ''sample type dataMatrix(3, i) = Trim(Cells(r, 3)) ''sample name dataMatrix(4, i) = "ETG" dataMatrix(5, i) = Trim(Cells(r, 5)) ''Response dataMatrix(6, i) = Trim(Cells(r, 6)) ''ISTD Response dataMatrix(7, i) = Trim(Cells(r, 10)) ''Calculated Conc i = i + 1 ReDim Preserve dataMatrix(7, i) Loop

    Read the article

  • How do I delete duplicates between two excel sheets quickly vba

    - by MainTank
    I am using vba and I have two sheets one is named "Do Not Call" and has about 800,000 rows of data in column A. I want to use this data to check column I in the second sheet, named "Sheet1". If it finds a match I want it to delete the whole row in "Sheet1". I have tailored the code I have found from a similar question here: Excel formula to Cross reference 2 sheets, remove duplicates from one sheet and ran it but nothing happens. I am not getting any errors but it is not functioning. Here is the code I am currently trying and have no idea why it is not working Option Explicit Sub CleanDupes() Dim wsA As Worksheet Dim wsB As Worksheet Dim keyColA As String Dim keyColB As String Dim rngA As Range Dim rngB As Range Dim intRowCounterA As Integer Dim intRowCounterB As Integer Dim strValueA As String keyColA = "A" keyColB = "I" intRowCounterA = 1 intRowCounterB = 1 Set wsA = Worksheets("Do Not Call") Set wsB = Worksheets("Sheet1") Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value) Set rngA = wsA.Range(keyColA & intRowCounterA) strValueA = rngA.Value If Not dict.Exists(strValueA) Then dict.Add strValueA, 1 End If intRowCounterA = intRowCounterA + 1 Loop intRowCounterB = 1 Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value) Set rngB = wsB.Range(keyColB & intRowCounterB) If dict.Exists(rngB.Value) Then wsB.Rows(intRowCounterB).delete intRowCounterB = intRowCounterB - 1 End If intRowCounterB = intRowCounterB + 1 Loop End Sub I apologize if the above code is not in a code tag. This is my first time posting code online and I have no idea if I did it correctly.

    Read the article

  • Aggregating Excel cell contents that match a label [migrated]

    - by Josh
    I'm sure this isn't a terribly difficult thing, but it's not the type of question that easily lends itself to internet searches. I've been assigned a project for work involving a complex spreadsheet. I've done the usual =SUM and other basic Excel formulas, and I've got enough coding background that I'm able to at least fudge my way through VBA, but I'm not certain how to proceed with one part of the task. Simple version: On Sheet 1 I have a list of people (one on each row, person's name in column A), on sheet 2 I have a list of groups (one on each row, group name in column A). Each name in Sheet 1 has its own row, and I have a "Data Validation" dropdown menu where you choose the group each person belongs to. That dropdown is sourced from Sheet 2, where each group has a row. So essentially the data validation source for Sheet 1's "Group" column is just "=Sheet2!$a1:a100" or whatever. The problem is this: I want each group row in Sheet 2 to have a formula which results in a list of all the users which have been assigned to that group on Sheet 1. What I mean is something the equivalent of "select * from PeopleTab where GROUP = ThisGroup". The resulting cell would just stick the names together like "Bob Smith, Joe Jones, Sally Sanderson" I've been Googling for hours but I can't think of a way to phrase my search query to get the results I want. Here's an example of desired result (Dash-delimited. Can't find a way to make it look nice, table tags don't seem to work here): (Sheet 1) Bob Smith - Group 1 (selected from dropdown) Joe Jones - Group 2 (selected from dropdown) Sally Sanderson - Group 1 (selected from dropdown) (Sheet 2) Group 1 - Bob Smith, Sally Sanderson (result of formula) Group 2 - Joe Jones (result of formula) What formula (or even what function) do I use on that second column of sheet 2 to make a flat list out of the members of that group?

    Read the article

  • Excel vba: error hiding calculated field in Pivot table

    - by Patrick Honorez
    I have written several Subs to show/hide fields in a PivotTable. Now I am trying to do the same with a calculated field, but I get an error when hiding it. I took my code from the recorder and the recorder's code also halts on the last line. I googled the error message, without serious result. Sub PrRemove() 'remove PR Dim pt As PivotTable Set pt = ActiveSheet.PivotTables("MyPivot") pt.PivotFields("MyField").Orientation = xlHidden '<- here is the error End Sub The same code works fine if MyField is a normal field (not a calculated one). I am using Excel 2007 with SP2. Any clue ?

    Read the article

  • Excel vba -get ActiveX Control checkbox when event handler is triggered

    - by danoran
    I have an excel spreadsheet that is separated into different sections with named ranges. I want to hide a named range when a checkbox is clicked. I can do this for one checkbox, but I would like to have a single function that can hide the appropriate section based on the calling checkbox. I was planning on calling that function from the event_handlers for when the checkboxes are clicked, and to pass the checkbox as an argument. Is there a way to access the checkbox object that calls the event handler? This works: Sub chkDogsInContest_Click() ActiveSheet.Names("DogsInContest").RefersToRange.EntireRow.Hidden = Not chkMemberData.Value End Sub But this is what I would like to do: Sub chkDogsInContest_Click() Module1.Show_Hide_Section (<calling checkbox>) End Sub These functions are defined in a different module: 'The format for the the names of the checkbox controls is 'CHECKBOX_NAME_PREFIX + <name> 'where "name" is also the name of the associated Named Range Public Const CHECKBOX_NAME_PREFIX As String = "chk" 'The format for the the names of the checkbox controls is 'CHECKBOX_NAME_PREFIX + <name> 'where "name" is also the name of the associated Named Range Public Function CheckName_To_SectionName(ByRef strCheckName As String) CheckName_To_SectionName = Mid(strCheckName, CHECKBOX_NAME_PREFIX.Length() + 1) End Function Public Sub Show_Hide_Section(ByRef chkBox As CheckBox) ActiveSheet.Names(CheckName_To_SectionName(chkBox.Name())).RefersTo.EntireRow.Hidden = True End Sub

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >