Questions tagged [excel]

38166 questions
0

votes
2

answer
17

Views

Excel Macro to copy worksheet to new worksheet paste values only

I am working on a Macro in Excel that will make a copy of the current worksheet and paste the values into a new worksheet. The worksheet name would be the same just with a number after it [ie Sheet, Sheet1(2)] My code does this correctly except that it copies and pastes everything to Sheet1(2). I...
Big D
-3

votes
0

answer
16

Views

React excel upload, edit, save

I'm using React and I need a library and by using it I want to upload an excel from the computer, edit it in the browser and can save the edited version to the computer or to a remote computer(cloud) in excel format. Which library provides those capabilities? Right now, I don't need macro functional...
o10575258
-2

votes
0

answer
18

Views

predict value of a New Product based on others?

I have the following dataset, Each row is a car with 5 Values, and in the end his price I will like to add a new car row with the 5 values, and get the Price calculated/predicted based on the learned from the previous rows. Can this be done in excel or python?
JonTargaryen
4

votes
5

answer
162

Views

Using OR logic on an array as argument in Sumproduct

I'm having a fairly large dataset where I need to combine multiple entries into a single value. My dataset contains data on the combination of two datasets, each using their own ID's and keys. I thought of using a Sumproduct() function like this: =SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--('Raw...
Luuklag
1

votes
0

answer
8

Views

Using VBA - Error closing some instances of Acrobat

I have this code that will go though a column of URL's that open a PDF file. My code opens the URL, saves it as a PDF using send keys, opens the file, searches the file for words from a column from excel, if it finds a match, it then places the word in the cell next to the URL. After this, it goes t...
D. Esposito
1

votes
3

answer
5.6k

Views

C# not reading excel date from spreadsheet

I uploaded an excel spreadsheet using Microsoft.Office.Interop.Excel. When I try to read cells with a date value in order to insert it into my data set, its not being recognized as a date and it comes up as a random number? this is the way that I refer to the cell: Excel.Range startDate = objsheet.g...
user2140832
1

votes
2

answer
368

Views

Excel Sorting “mixed data”

I have 3 columns of data that need sorting into a specific order. The column to sort on are alpha numberics but contain letters and numbers. The best I can do using the standard Excel sort function on the ribbon bar is: Ideally the output should be: AS you can see the Excel sort function isn't doing...
Jeff Haynes
1

votes
3

answer
2.6k

Views

Is it more efficient to read from an Excel file or an CSV file?

I need to write a quick program (using Java since its the only language I am really comfortable with) that takes an Excel file (or CSV) and parces through the data adding information that might be missing. The problem Im having is that I cant decide how to start, it feels like manipulating an Excel...
1

votes
1

answer
221

Views

How to specify range instead of offset , excel VBA?

I want to give a range instead of offset in the following code. following code copies the color from range(C5:F11) to the offset i set but i want to specify a range like range(M5:P11). I tried it simply replacing offset by range but it does not work properly. Please help Sub MatchColors2() For Each...
Shan S
1

votes
2

answer
1.4k

Views

vba replace nested if statement

Is there a way to make this more efficient? I find it a bit ugly. I want to verity if there is a next "deck", if so, execute some for it, until deck8 here is my code: If deckNum > 0 Then 'Execute code for deck 1 If deckNum > 1 Then 'Execute code for deck 2 If deckNum > 2 Then 'Execute code for deck...
user2895741
1

votes
2

answer
345

Views

Advanced filter that compares multiple columns

I have a table. A | B | C | D | E 1 ID | Value 1 | Value 2 | Value 3 | Value 4 2 1 | 15 | 7 | 18 | 12 3 2 | 6 | 19 | 11 | 20 4 3 | 24 | 10 | 12...
davimusprime
1

votes
1

answer
9.8k

Views

Move worksheet to a new window

I was wondering if it is possible to avoid the general behavior of MS Excel 2010 when opening a new document while one instance of the program is already running. Basically what happens is that the program opens the new spreadsheet in the current environment, as a new workbook. What I would like to...
Gianluca
1

votes
1

answer
440

Views

Creat a dynamic Loop in VBA with array

I want to create a loop for time series in VBA , so that the LINEST would be done in every row, data for LINEST have to be dynamic, e.g. =Linest(C6:N6; Q1:AB2; TRUE) ==> result array of thre next row =Linest(C7:N7; Q1:AB2; TRUE) ==> result array of three next row =Linest(C8:N8; Q1:AB2; TRUE) ==> res...
user2983871
1

votes
1

answer
5.3k

Views

importing an excel file to datagrid on delphi

I've tried this code on .NET -to import an excel file to devxpress datagrid- and it worked fine but i have to create a procedure doing the same job on delphi now. i dont know much about delphi so i need some ideas how to do that. public static DataTable ImportExcelXLS(string FileName, bool hasHeader...
user2764568
0

votes
0

answer
7

Views

Why does the ImportXML Method leads to a runtime error 9?

The Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = [Namensfilter].Row And Target.Column = [Namensfilter].Column Then Dim Displayname As String: Displayname = [Namensfilter].Value + "*" Dim Map As XmlMap Set Map = ActiveWorkbook.XmlMaps(1) Dim urlXML As String urlXML = "htt...
despa migros
1

votes
2

answer
578

Views

How can I use wildcards with MyVlookup function?

When calling the below function in Excel by using =MyVlookup(B1,Sheet1!A:A,1) I get what I want. Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant Dim cl As Range For Each cl In c.Columns(1).Cells If UCase(Lval) = UCase(cl) Then MyVlookup = cl.Offset(, oset - 1) Exit Functio...
Maple
1

votes
2

answer
1.8k

Views

Excel Generating a formula based sequence based on levels [closed]

What I am trying to achieve is the following ID Name Level 1 ABC 1 11 ABC 2 11001 ABC 3 1100101 ABC 4 110010101 ABC 5 11001010101 ABC 6 11002 ABC 3 1100201...
user1799524
1

votes
4

answer
88

Views

Get number of days for the last two dates for every cells with same ID in Excel

I have data like shown below. ID Date 8215 3/11/2013 8215 3/11/2011 8215 3/12/2009 8215 3/10/2007 18150 3/1/2013 18150 3/4/2011 18150 3/4/2009 18150 3/1/2008 18150 3/6/2006 Desired Results: ID Num_days 8215 731 18150 641 I would like to get the...
Cryssie
1

votes
3

answer
5.1k

Views

Delete Row from Array

I am trying to go through an array to find duplicate entries in a single column of that array and delete the entire row. I am getting figuring out rangeStart, rangeEnd, and lastrow above this and that part is working fine. data = Range(rangeStart, rangeEnd) For i = lastrow - 1 To 2 Step -1 If data(...
CongdonJL
1

votes
1

answer
984

Views

Export to Excel doesn't work for Excel 2013

We're using a deprecated ActiveX control (OWC) in a web application. Even though the control is deprecated, the control still works without any problems except that the "Export to Excel" button does not work with Excel 2013 (works on other versions of Excel). Under other versions of Excel, the Acti...
user46372
1

votes
3

answer
12.6k

Views

Autosize column width using poi

I am appending data to the existing excel using POI. But on appending the data, the excel headers which are already present before the editing are compressed to small areas. Can someone please suggest me how to avoid this? I have applied even autoSize after appending data, but of no use. FileInputSt...
rick
1

votes
2

answer
105

Views

What is a Compact way to Refer to a Set of Non-Contiguous Rows?

The below code seems inelegant and wasteful. Is there a more compact notation for referencing all those rows? Sub HideEBRedData() 'un/hide EIRP Budget rows with redundant path data Application.ScreenUpdating = False Set EIRPBudget = Sheets("EIRP Budget") With EIRPBudget .Rows(29).Hidden = Not .Rows(...
jmaz
1

votes
1

answer
739

Views

What is the line-continuation character in vba? [closed]

How to write a string manipulation in multiple lines like this in VBA? ( " _" is not working here) szStr= szStr & "1=" & szVar1 & ";50=" & szVar50 & _ ";79=" & szVar71 & ";7971=" & szVar7971 & _ ";7423=" & szVar7423 & ";9523=" szVar9523 & _ ";1138=" & szVar1138 & ";109=" & szVar109 & _ ";9526=" & sz...
Nadaraj
0

votes
0

answer
16

Views

VBA: Paste function causes 1004 error after the paste range is cleared

I have a problem with my code, when i try copy some piece of data from one sheet ("Import") to another ("TIs"). There is a range in the second sheet, that is cleared before the data is copied. When I try to paste it, I get a Error 1004 "Application-defined or Object-defined error" Sub Data() Sheets(...
ketzul
0

votes
1

answer
16

Views

Getting number of duplicates before removing them

I just want to get the number of duplicates removed when using the .RemoveDuplicate function in excel vba. Is there anyway to get the precise number of how many duplicates are removed when using the excel function? Code: rangeofws.RemoveDuplicates Columns:=1, Header:=xlYes rangeofws is just a range...
DSM
0

votes
0

answer
8

Views

How can i automate delete e-mails in Outlook with excel

Given an excel, with a column. This column conatins strings. The puspose: If a receveid e-mail's subject does not contains one of the string which are in the excel column -> delete the e-mail. For example: Excel column: "asd" "fgh" Received e-mails subject: 1st e-mail subject: "Holiday in Canada" -...
johndoel
0

votes
0

answer
3

Views

How to get in Excel the Top 10 results from a list without duplicates?

I have a list of four columns in Excel, for which I would like to get the Top 10 results, in an ordered fashion (see right side of the screenshot below). I am using =IF(11 - RANK(B120; B$120:B$140)
luftikus143
0

votes
0

answer
6

Views

How to determine default cell margin values in relation to used font

Sometimes a full (gross) cell width is needed, and it needs to be somehow calculated/determined. For instance, when you want to set height of a merged cell so all text could be visible (for the printed version), you need to calculate exact combined width (all cells) so you can calculate a number of...
user11076460
1

votes
2

answer
1.8k

Views

Excel: select column in range by column index - no vba

I have cell A1, and then below 4 columns. Cell A1 right now contains number 2. 2 | A | B | C | SELECTED | | 12 | 44 | 88 | 44 | | 43 | 55 | 99 | 55 | | 54 | 66 | 11 | 66 | Which formula should I put into 'Selected' column to display there values of the s...
LucasSeveryn
1

votes
1

answer
1.2k

Views

perl: save workbook with Win32::OLE not possible

I've following simplified script (i'm not able to change the Win32:OLE) use warnings; use strict; use Cwd; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; my $excel = Win32::OLE->new('Excel.Application'); my $workbook = $excel->Workbooks->Add; my $worksheet = $workbook->Worksheets(1);...
MemLeak
1

votes
1

answer
128

Views

Applying same method to multiple objects

I am curious to know whether there is a way to apply the same method to multiple objects. In other words, I'm looking for something that is, in a way, the inverse of a "With" statement. I know that using "With" statements, I can apply multiple methods to a single object: With ITEM_RNG .ClearContents...
teepee
1

votes
2

answer
6.5k

Views

How to set warnings off in excel application from access vba

I'm opening excel workbook from access, after writing contents from access to excel, i'm using xlobj.save to save the workbook. Excel application is giving some warnings says this workbook already exists do u want to replace it. How to disable such warnings from access. I'm using DoCmd.SetWarnings...
vuyy1182
1

votes
1

answer
4.8k

Views

VBA - Multiple series to a chart

I'm trying to make add two series to a single XYscatter chart via loop. Currently, my code creates two charts. The X values are constant but the Y values change so I added them to an array to preserve them. Sub test() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Sheet1") Dim thearray(9) As Dou...
docjay
0

votes
0

answer
3

Views

Finding size from SKUs

A2: ad-m-trxswft-r2-hs-bbb-12 A3: mr-drifter-9.5 A4: arc-m-thoriumar-hdy-g2-hb-xl A5: arc-w-satoro-arz-ls-bk-m A6: arc-m-XS B2: 12 B3: 9.5 B4: XL B5: M B6: XS I would like to figure out how to get the size: B2,B3,B4,B5,B6 from the end of SKUs by using Excel formula. Thank you Jun
Jun Kim
0

votes
0

answer
6

Views

Excel stops running protected workbook, runs fine if unlocking it

I have a tool, which uses VBA. As the tool has company secrets, the project is password protected to avoid the info getting into the wrong hands (to some extent). Therefore, as soon as anyone wants to "view code", they have to put in the password. The tool has a simple GUI, which just loads values f...
Anima
1

votes
1

answer
2.8k

Views

How to do Excel's “Convert to Number” in Java

I am reading data from Excel using Apache POI. I want to convert some cells to number while reading in Java as following: Input 01.0234500 Output 1.02345 Input 412206362371 Output 4.12206E+11 Input 1234.201400002345 Output 1234.2014 When I am using "Convert to Number" in Excel, it works fine. But I...
Anup Ganatra
1

votes
1

answer
8.2k

Views

Conditional formatting for cells when cell above has the same value

Is it possible to apply conditional formatting to a whole sheet such that whenever the cell above the current cell has the same value (either text or number) as the current cell, the current cell will be formatted yellow fill/blue font? For example, when cell: A1 is blank A2 AAA A3 AAA A4 AAA A5 i...
user3045580
1

votes
2

answer
2.7k

Views

Find consecutive cells based on a value

I have a bank of data that is updated monthly. I need to analyze said data to determine first all values below 64, then I need to look for two consecutive instances of this event. Once found, I would like a value returned to indicate this vendor is a problem. The second piece of the puzzle is that...
user3758610
1

votes
1

answer
3.3k

Views

How use the combobox keydown event without selecting the listed item

I have in my Excel VBA project, a combobox which uses a range of cells as it's list of items. I have used a filter in it, so that whenever a value is entered, the list shrinks down to the items containing the string, and dropdown list is shown. However, the problem appears, when the dropdown is show...
Prantosh
1

votes
3

answer
990

Views

Delete rows from table if entries duplicate another table

Been a while since I worked with VBA so bear with me as I may be a little bit rusty. I have 2 tables. One is what I call a "Deleted" list with a few columns and the other is a more detailed list with all details. What I want to do is look up items on the "Deleted" list (see Table 1) and if all the...
bmgh1985

View additional questions