Search Results

Search found 4866 results on 195 pages for 'excel ranges'.

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 61/195 | < Previous Page | 57 58 59 60 61 62 63 64 65 66 67 68  | Next Page >

  • MS Excel - Macros for consolidating values from multiple sheets into a single sheet

    - by SpikETidE
    Hi everyone... Consider i have 4 workbooks with the following structure... 1. Main.xlsx Name Jan Feb Mar A B C 2. Jan.xlsx Name Jan A 3.3 B 6.4 C 5.3 3. Feb.xlsx Name Feb A 1.3 B 3.4 C 5.5 4. Mar.xlsx Name Mar A 1.3 B 3.4 C 5.5 I need to combine them like 1. Main.xlsx Name Jan Feb Mar A 3.3 1.3 1.3 B 6.4 3.4 3.4 C 5.3 5.5 5.5 And i need to automate the process... And i guess i can do this with macros...? Can anyone suggest some way with which i can proceed with the macro? Thanks for your time....

    Read the article

  • How can I copy Data from one sheet to another Sheet in Excel 07 Through Macro

    - by Mwaseem Alvi
    Hello, I am using MS Office 2007. Please let me know that how can I copy whole data from sheet one to sheet two. I want to copy the whole data from row 5 to onward in sheet two. The whole scenrio is given below in detail. Sheet one: Copy the data from column B and Row 3 Sheet Two: Paste the Copied Data in Column B and Row 3 Sheet One: Copy the whole data from Column B to Column G and Row 5 to onward Sheet Two: Paste whole copied data in sheet two from last filled row to onward Data dont overwrite on any row or column. Every data will be add in sheet two from sheet one when macro will be run. Thanks

    Read the article

  • (EXCEL)VBA Spin button which steps through in an sql databases date time

    - by Gulredy
    I have an sql Database table in MySQL which have lots of rows with varied date time values. For example: 2012-08-21 10:10:00 <-- with these date there are around 12 rows 2012-08-21 15:31:00 <-- with these date there are around 5 rows 2012-08-22 11:40:00 <-- with these date there are around 10 rows 2012-08-22 12:17:00 <-- with these date there are around 9 rows 2012-08-22 12:18:00 <-- with these date there are around 7 rows 2012-08-25 07:21:00 <-- with these date there are around 6 rows If the user clicks on the SpinButton1_SpinUp() or SpinButton1_SpinDown() button then it should do the following: The SpinButton1_SpinUp() button should filter out those data from an sql table which is the next after what we are currently on now. Example: We have currently selected: 2012-08-21 15:31:00. The user hits the SpinUp button then the program selects those date from the database, which is the next higher value like this one: 2012-08-22 11:40:00. So the user hits the SpinUp button the data which is selected in the database will change from those with date: 2012-08-21 15:31:00 to those with date: 2012-08-22 11:40:00 The SpinButton1_SpinDown() will do exactly the reverse of the SpinUp button. When the user hits the SpinDown button the data which is selected in the database will change from those with date: 2012-08-21 15:31:00 to those with date 2012-08-21 10:10:00 So I think the date which we are currently on, should be stored in a variable. But on button hit not every bigger or lower data should be selected in the database, only those which are the closest bigger or the closest lower date. How can I do this? I hope I described my problem understandable. My native language is not english, so misunderstandings can occur! Please ask if you don't understand something! Thank you for reading!

    Read the article

  • MS Excel - Copying columns from multiple worksheets into a single worksheet based on some condition

    - by SpikETidE
    Hi everyone... Consider i have 4 workbooks with the following structure... 1. Main.xlsx Name Jan Feb Mar A B C 2. Jan.xlsx Name Jan A 3.3 B 6.4 C 5.3 3. Feb.xlsx Name Feb A 1.3 B 3.4 C 5.5 4. Mar.xlsx Name Mar A 1.3 B 3.4 C 5.5 I need to combine them like 1. Main.xlsx Name Jan Feb Mar A 3.3 1.3 1.3 B 6.4 3.4 3.4 C 5.3 5.5 5.5 And i need to automate the process... Can someone enlighten me about the possible options that i have to do this...? Thanks for your time....

    Read the article

  • Excel VBAa: Sum invoice by client id with copying result to new worksheet

    - by Melkior
    Hi, i have strange problem doing reporting: i have numerous clients with different issued invoices. Problem comes to the point when there are invoices in minus and plus: Column A consists of client unique IDs, Column B invoice number, column C invoice amount A | B | C 0010019991 | 1800149471 | 162.00 0010019991 | 1800136388 | 162.00 0010019991 | 1600008004 | -36.00 0010021791 | 1800132148 | 162.00 0010021791 | 1800145436 | 162.00 0010021791 | 1600007737 | -12.00 0014066147 | 1800119068 | 1,684.80 0014066147 | 1800123702 | 1,684.80 0014066147 | 1600007980 | -1,300.80 0014066147 | 1600007719 | -1,286.40 I need to remove rows with negative invoices in a way that amount is summed with invoices which are not with negative amount. So that final result would look like: A | B | C 0010019991 | 1800149471 | 126.00 0010019991 | 1800136388 | 162.00 0010021791 | 1800132148 | 150.00 0010021791 | 1800145436 | 162.00 0014066147 | 1800123702 | 782.40

    Read the article

  • Error while creating a table style in excel

    - by Rashmi Pandit
    Hi, I am using the following function to create a TableStyle: Public Function CreateTableStyle() ActiveWorkbook.Unprotect Dim objTS As TableStyle On Error Resume Next Set objTS = ActiveWorkbook.TableStyles("MyTableStyle") On Error GoTo err_CreateTableStyle If Not objTS Is Nothing Then Exit Function End If Set objTS = ActiveWorkbook.TableStyles.Add("MyTableStyle") With ActiveWorkbook.TableStyles("MyTableStyle") .ShowAsAvailablePivotTableStyle = True .ShowAsAvailableTableStyle = False End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Font .FontStyle = "Bold" .TintAndShade = 0 .ThemeColor = xlThemeColorDark1 End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = -0.249946592608417 End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlTotalRow).Font .FontStyle = "Bold" .TintAndShade = 0 .ThemeColor = xlThemeColorDark1 End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlTotalRow).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = -0.249946592608417 End With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow1).Font.FontStyle = "Bold" With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow1).Interior .Color = 16764828 .TintAndShade = 0 End With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow2).Font.FontStyle = "Bold" With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow2).Interior .Color = 16777164 .TintAndShade = 0 End With ActiveWorkbook.Protect Exit Function err_CreateTableStyle: Call Common.ErrRaise(Erl, "Common", "CreateTableStyle", "CreateTableStyle") End Function At the line below: With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Font .FontStyle = "Bold" I am getting an error: Run-time error '1004' Unable to set the FontStyle property of the Font class. Can someone please identify the issue? I am not able to figure why it is not letting me set the property.

    Read the article

  • Output a NULL cell value in Excel

    - by Peter
    I have an IF statement. If a cell = n, then do something, else output NULL =IF(A1=5, "Success", NULL) // #NAME? =IF(A1=5, "Success", "NULL") // NULL (as in text, not actually NULL!) =IF(A1=5, "Success", "") // blank but not NULL =IF(A1=5, "Success", 0) // zero value but not NULL

    Read the article

  • Overriding events of excel sheet using VBA

    - by Rashmi Pandit
    Hi, I need to programmatically override the following events of a worksheet: BeforeDoubleClick SelectionChange BeforeRightClick I have been able to override the OnActivate event using the following code: sheet.OnSheetActivate = "MyOwn_Activate" Private Sub MyOwn_Activate() myForm.Show End Sub I have implemented BeforeDoubleClick on similar lines: sheet.OnDoubleClick = "My_BeforeDoubleClick" Private Sub My_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ... End Sub However, an 'argument not optional' error is thrown at run-time when user double clicks a cell on the sheet. Can someone please suggest how should I pass the paramters? In addition, I am not able to find event names for SelectionChange & BeforeRightClick. I tried: sheet.BeforeRightClick = "My_BeforeRightClick" sheet.SelectionChange = "My_SelectionChange" But, both the above lines do not work. Any help/ suggestion is greatly appreciated. Thanks :)

    Read the article

  • Excel document incorrect format

    - by Jim
    I have a macro enabled work book and i change the name of the .xlsm file to [FileName].xlsm.zip and then i unzip i get some folders I then put these extracted folders in to another folder and zip it back and rechange the extension to the previous xlsm format i now try and open but i get an unreadable error. I am not changing any content here just extracting and zip it back. What could be the problem?

    Read the article

  • Excel LINEST() in C#

    - by Newbie
    Hi am searching to find a proper implementation of LINEST function in C# given a set of x and y, but I've had no luck. Y X 1 10 2 20 3 12 4 15 etc. Please help. Thanks

    Read the article

  • Updating Excel Cell with Non-Numeric Data in C#

    - by kbo206
    I have a query that is ExcelQuery = "Update [Sheet1$] set CITIZEN_ID = #" + value + " where CITIZEN_ID = " + value; As you can see, I'm essentially just appending a "#" onto the CITIZEN_ID field. value is a int/numeric value. So if I had "256" in the CITIZEN_ID column it would be converted to "#256" When I execute this I get an OleDbException Syntax error in date in query expression so I surrounded part of the query in single quotes like this, ExcelQuery = "Update [Sheet1$] set CITIZEN_ID = '#" + value + "' where CITIZEN_ID = " + value; With that I get yet another OleDbException this time with, Data type mismatch in criteria expression. I'm guessing for some reason the CITIZEN_ID fields don't want to take anything besides a plain number. Is there any way I can remedy this to get that pound symbol in? Thanks!

    Read the article

  • Excel VBA: Sum invoice by client id with copying result to new worksheet

    - by Melkior
    Hi, i have strange problem doing reporting: i have numerous clients with different issued invoices. Problem comes to the point when there are invoices in minus and plus: Column A consists of client unique IDs, Column B invoice number, column C invoice amount A | B | C 0010019991 | 1800149471 | 162.00 | 2010-03-12 0010019991 | 1800136388 | 162.00 | 2010-02-12 0010019991 | 1600008004 | -36.00 | 2010-03-15 0010021791 | 1800132148 | 162.00 | 2010-03-12 0010021791 | 1800145436 | 162.00 | 2010-02-12 0010021791 | 1600007737 | -12.00 | 2010-03-15 0014066147 | 1800119068 | 1,684.80 | 2010-03-12 0014066147 | 1800123702 | 1,684.80 | 2010-02-12 0014066147 | 1600007980 | -1,300.80 | 2010-02-15 0014066147 | 1600007719 | -1,286.40 | 2010-03-15 I need to remove rows with negative invoices in a way that amount is summed with invoices which are not with negative amount. So that final result would look like: A | B | C | D 0010019991 | 1800149471 | 126.00 | 2010-03-12 0010019991 | 1800136388 | 162.00 | 2010-02-12 0010021791 | 1800132148 | 150.00 | 2010-03-12 0010021791 | 1800145436 | 162.00 | 2010-02-12 0014066147 | 1800123702 | 782.40 | 2010-02-12

    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

  • Need help nesting an Excel calculation

    - by Frank
    Here's what's currently happening: Z8: 100 Z9: =((Z8*W2)+Z8) Z10: =Z9*X2+Z9 Z11: =Z10*Y2+Z10 I start with a value of 100 and then add data from W2, X2 and Y2. This works, but it spans across three cells. I need it to fit into one. I'm drawing a blank on nesting the equations to fit into the one. Help?

    Read the article

  • Excel VBA to check autofilter for data

    - by cav719
    I need help checking for autofiltered rows not including the header. I want it to give a message box "No records found." then exit sub or continue with copy paste if there are rows beyond the header row. I know I need an If/Else entry after the filter to check for data but I'm having trouble figuring how to check. This code is being done from a UserForm I created. Here is my script: Private Sub Searchbycompanyfield_Click() If CompanyComboBox1.Value = "" Then MsgBox "Please enter a Company to begin search." Exit Sub End If ActiveSheet.Range("$A:$H").AutoFilter Field:=1, Criteria1:=EQDataEntry.CompanyComboBox1.Value, Operator:=xlOr Cells.Select Selection.Copy Sheets("Sheet2").Select Range("A5").Select ActiveSheet.Paste Call MessageBoxYesOrNoMsgBox End Sub Any help would be greatly appreciated.

    Read the article

  • Excel VBA ComboBox2 doesn't get the right content

    - by Marc
    Hi, I'm having a problem with the content of a combobox. On my userform, there are 3 comboboxes. Depending on the chosen item from combobox1, combobox2 should display either set 1 or set 2. The same will be happening with the content of combobox 3, which depends upon the combination of chosen items from combobox 1 and 2. However, I'm running into problems with combobox 2, which is always populated by set 2, even if I select the item in combobox 1 that should generate set 1 in the second combobox. This is the code I used: Private Sub UserForm_Initialize() With ComboBox1 .Clear .AddItem "In contrast" .AddItem "Eng?" .AddItem "Trillers" .AddItem "Natuur(lijk)" .AddItem "Muziektrafiek" End With If ComboBox1.Value = "In contrast" Then GoTo LineComboBox1Set1 End If If ComboBox1.Value = "Eng?" Then GoTo LineComboBox1set2 End If If ComboBox1.Value = "Trillers" Then GoTo LineComboBox1set2 End If If ComboBox1.Value = "Natuur(lijk)" Then GoTo LineComboBox1set2 End If If ComboBox1.Value = "Muziektrafiek" Then GoTo LineComboBox1set2 End If LineComboBox1Set1: With ComboBox2 .Clear .AddItem "Op verkenning" .AddItem "Gehoord? Gezien?" .AddItem "On stage" .AddItem "Creabende" .AddItem "Ingeblikt" End With LineComboBox1set2: With ComboBox2 .Clear .AddItem "Op verkenning" .AddItem "Gehoord? Gezien?" .AddItem "On stage" .AddItem "Creabende" .AddItem "Ingeblikt" .AddItem "Speak up" .AddItem "In de kijker" End With Can anyone help me on this one? Thanks a lot in advance!! Kind regards, Marc

    Read the article

  • Excel Hyperlink mass update

    - by IMHO
    I have a spreadsheet with thousands of rows. Each row contains a hyperlink with a path. The path is not valid, however easily fixable by replacing first part of it with correct value. Example: current hyperlink: F:\Help\index.html Needed: P:\SystemHelp\index.html The problem is that standard Find/Replace does not "see" content of hyperlinks. Is the only way to write a macro or is there another way to do it?

    Read the article

  • Excel VBA pass array of arrays to a function

    - by user429400
    I have one function that creates an array of arrays, and one function that should get the resulting array and write it to the spreadsheet. I don't find the syntax which will allow me to pass the array of arrays to the second function... Could you please help? Here is my code: The function that creates the array of arrays: Function GetCellDetails(dict1 As Dictionary, dict2 As Dictionary) As Variant Dim arr1, arr2 arr1 = dict1.Items arr2 = dict2.Items GetCellDetails = Array(arr1, arr2) End Function the function that writes it to the spreadsheet: Sub WriteCellDataToMemory(arr As Variant, day As Integer, cellId As Integer, nCells As Integer) row = CellIdToMemRow(cellId, nCells) col = DayToMemCol(day) arrSize = UBound(arr, 2) Range(Cells(row, col), Cells(row + arrSize , col + 2)) = Application.Transpose(arr) End Sub The code that calls the functions: Dim CellDetails CellDetails = GetCellDetails(dict1, dict2) WriteCellDataToMemory CellDetails, day, cellId, nCells Thanks, Li

    Read the article

  • Using "wildcards" in a vlist array to delete rows in Excel

    - by KMinner
    Good Morning All, I'm trying to setup a vba macro to delete all user IDs out of a spreadsheet that do not start with designated prefixes (e.g. US, A1, VM, etc). The below block of code was found on the Code Library and looks to be what I need but there is one problem: When I enter in UserID prefixes into the vlist fields, it treats them as absolute rather then a part of the string that I want to keep. Is there a way to incorporate wildcards into a vlist? Sub Example1() Dim vList Dim lLastRow As Long, lCounter As Long Dim rngToCheck As Range, rngFound As Range, rngToDelete As Range Application.ScreenUpdating = False With Sheet1 lLastRow = Get_Last_Row(.Cells) If lLastRow > 1 Then vList = Array("US", "A1", "EG", "VM") 'we don't want to delete our header row With .Range("A2:A" & lLastRow) For lCounter = LBound(vList) To UBound(vList) Set rngFound = .Find( _ what:=vList(lCounter), _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=True) 'check if we found a value we want to keep If rngFound Is Nothing Then 'there are no cells to keep with this value If rngToDelete Is Nothing Then Set rngToDelete = .Cells Else 'if there are no cells with a different value then 'we will get an error On Error Resume Next If rngToDelete Is Nothing Then Set rngToDelete = .ColumnDifferences(Comparison:=rngFound) Else Set rngToDelete = Intersect(rngToDelete, .ColumnDifferences(Comparison:=rngFound)) End If On Error GoTo 0 End If Next lCounter End With If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete End If End With Application.ScreenUpdating = True End Sub

    Read the article

  • Excel, VBA Vlookup, multiple returns into rows

    - by Sean Mc
    Very new to VBA, so please excuse my ignorance. How would you alter the code below to return the result into rows as opposed to a string? Thanks in advance.... data Acct No CropType ------- --------- 0001 Grain 0001 OilSeed 0001 Hay 0002 Grain function =vlookupall("0001", A:A, 1, " ") Here is the code: Function VLookupAll(ByVal lookup_value As String, _ ByVal lookup_column As range, _ ByVal return_value_column As Long, _ Optional seperator As String = ", ") As String Application.ScreenUpdating = False Dim i As Long Dim result As String For i = 1 To lookup_column.Rows.count If Len(lookup_column(i, 1).text) <> 0 Then If lookup_column(i, 1).text = lookup_value Then result = result & (lookup_column(i).offset(0, return_value_column).text & seperator) End If End If Next If Len(result) <> 0 Then result = Left(result, Len(result) - Len(seperator)) End If VLookupAll = result Application.ScreenUpdating = True End FunctionNotes:

    Read the article

  • Calculate time in Excel

    - by Sakthivel
    Hi friends, i have two columns in xl sheet A row have this format "8:25 PM EDT" and B row have this format "10:12:15 AM" , Now i want to add one hour to the B column if the A column contains the text "EDT" , Please help me to find out the solution Thanks in advance.

    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 | 57 58 59 60 61 62 63 64 65 66 67 68  | Next Page >