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...
-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...
-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?
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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(...
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...
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...
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(...
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...
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(...
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...
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" -...
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)
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...
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...
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);...
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...
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...
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...
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
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...
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...
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...
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...
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...
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...