Questions tagged [excel]

61013 questions
1

votes
2

answer
12

Views

Use Font.Color as Condition in If Statement

I am currently trying to use the font color as a condition to check against in an if statement. It seems incredibly easy, but VBA doesn't seem to be able to do it. I can't show my actual code as it has proprietary information in it, but I have even tried a simple version of my code with no avail....
B. Moore
0

votes
4

answer
19

Views

User defined column letter in a Range call

Asking to user the letter referencing a column ; then using the answer to get the range of this a cell with in this column at some row. But the 'Range' refuses to recognize the letter input. What am I missing in the following simple two lines ? UserCol = Application.InputBox(' Please enter the colum...
Studix
0

votes
0

answer
17

Views

Why VBA Runtime error 13 Type mismatch while starting DDE conversation

I have this code: Public channel As Long Sub Button1_Click() MsgBox 'all is OK' ' Application.ActiveCell.Font.Bold = True channel = Application.DDEInitiate('Excel', 'System') MsgBox 'Conversation started' End Sub Why DDEInitiate expression gives error 13?
TomR
0

votes
1

answer
19

Views

VBA Worksheet Loops

I'm currently working with a workbook containing 34 different tabs. I'm trying to extract Monthly Data from each of the tabs and Transpose it into daily figures for each specific city. I have put all the dates within the year 2019 as columns in order to present it as daily figures. (See example in...
David Gorgan
1

votes
1

answer
4.8k

Views

Macro to save only the active worksheet

In the below code it saves the complete workbook. I want to save only the active worksheet. Sub sbVBS_To_SAVE_ActiveWorkbook() ActiveWorkbook.Save End Sub
1

votes
2

answer
49

Views

If user skips rows to input a numeric value, force user to type in data in the following available row

I have a sheet and the user input Starts in A7 and calculations are inserted with a macro to B7 all the way through I. I got all that worked out but what i need is: the user is going down typing in data in Column A, lets say they input data Starting from A7, through A11, then skip A12 and type int...
0

votes
2

answer
16

Views

How to find maximum value in a range while certain condition is met

I have two columns, column '№' has numbers 7 and 8 (basically speaking 7 is a layer and 8s are sub layers, 7 forms group of 8s), column 'Value' shows critical level for each 8 (these levels range from 1 to 4). I need to find MAX value in a column 'Value' corresponding to the certain group of 8s an...
MDoskarin
0

votes
0

answer
5

Views

It seems that the cells references in my range are not working

Based on the text ('SNV') present in column L of the 'HiddenSheet' worksheet, I would like to select and copy cells in columns 1 to 6 for all rows for which the 'SNV' text is present in column L. Then I would like to paste the values of the copied cells in the SNVReports worksheet. I have the code b...
Sylvain.A
1

votes
2

answer
436

Views

Excel - Pull data from a cell on a different sheet and hyperlink

I know how to pull data from a separate worksheet in excel by using the formula =Sheet2!A2. That pulls whatever data I have in the A2 cell from my Sheet2. But is there away to pull the data from that cell and hyperlink it to the cell by using a formula? I know you can manually link it but I'm trying...
Cameron Cole
1

votes
3

answer
582

Views

Returning a column number from a table using the =COLUMN function

I have the following function in Excel that returns the column number of a table based on the column header. The table is called Config and the table column header is value. The below will return to me the column number in excel. =COLUMN(Config[Value])-COLUMN(Config)+1 Could anybody let me know how...
Geminiflipflop
1

votes
2

answer
49

Views

Copying a source sheet to another workbook where source sheet name changes

I'm trying to copy the 2nd sheet from Workbook 2 into Workbook 1 (an open workbook from which I run the macro). The name of the second sheet changes from time to time. The code copies the second sheet, under the assumption that the name never changes. Dim wb1 As Workbook Set wb1 = ActiveWorkbook Dim...
noobaccountant
1

votes
1

answer
99

Views

VBA check if object exists before set

What is the best way to check if object exists before using set on it ? I have many workbooks, they contain checkboxes and optionbuttons. I have an array that contain the list of all possible names of checkboxes and optionbuttons that the different workbooks may have to make my question clear, let u...
JustGreat
1

votes
1

answer
48

Views

Mark all cells which do not include two given words

I am writing a code which should mark in a range, in red, all cells which do not include the words 'bel' and 'hsa'. Sub Check() Range('c2:c49').Select For Each cell In Selection If (cell.Value 'bel' Or cell.Value 'hsa') Then cell.Interior.Color = RGB(255, 0, 0) End If Next cell End Sub It works if...
Marco D.
1

votes
3

answer
32

Views

Using Replace to only replace a small portion of a string

I am reading data from an text file, and I am trying to format it to match a Defined Name for a cell. The defined name is 89NL_10ETH_A and the string is 89NL 10ETH A but with a bunch of spaces after it. I used Replace() to convert spaces into '_', but all of the spaces at the end are converted to un...
Geoffrey
1

votes
1

answer
38

Views

VBA Worksheet Variable

I can't seem to use Worksheet variables as I'd expect them to. Function Test() Test = Application.ThisCell.Worksheet.Range('A1').Value End Function The above function works as expected, returning the value of cell 'A1' in the same worksheet as the cell the function was called from. Function Test() D...
Eptagon
1

votes
1

answer
21

Views

VBA - Variable Worksheet

I have an input sheet in Excel where the use enters a number of account details. Once the form is filled in they press a button that generates an input file using VBA. The first step is that a new worksheet is generated and the name of this sheet in a combination of the clients name and the a hard c...
Carlos80
1

votes
1

answer
36

Views

Converting cell values to snake_case

I have some data cleansing task. I have a column which start from H6 and further down from their. The column contains data which was supposed to be in snake_case, but is not the case. The cell values are of the form: With camel case: 'CamelCase' With spaces: 'Spaced Value' With some initial call cap...
anir
1

votes
2

answer
35

Views

Copying Type String Array to Range in Excel - Invalid Qualifier

I want to transfer the contents from part of the a 'Type' array to an excel range (see example below), but get 'Invalid Qualifier' error. Any help would be appreciated? Public Type typDetails firstName As String lastName As String End Type Dim userDetails(100) As typDetails Public Sub test() userDet...
Alex
1

votes
1

answer
37

Views

Pasting VBA Array into Excel Range

I know this question has been asked multiple times, but I am kinda stuck adopting the suggested solutions to my problem. I have an array(0 to 4), that gets filled multiple times in a loop and should be pasted each time into a new line in excel. Expected Output: A | B | C |...
Maeaex1
1

votes
2

answer
53

Views

Counting number of leading zeros in cell in Excel

Trying to figure out how to count the number of leading zeros in an excel cell. The value within the cell can be comprised of both numbers and letters or just numbers or just letters. The column is formatted as a text column. So far I have been able to use =MIN(FIND({1,2,3,4,5,6,7,8,9},A1&'123456789...
jufg
1

votes
2

answer
33

Views

Use Round function within VBA function

This function returns multiple lookup matches within one cell: Function NBlookup(lookupval, lookuprange As Range, indexcol As Long) Dim result As String Dim x As Range result = '' For Each x In lookuprange If x = lookupval Then result = result & ' ' & x.Offset(0, indexcol - 1) & x.Offset(0, 5) E...
Bluesector
1

votes
1

answer
50

Views

Retrieving username from computer

I would like to get the actual username of the computer that my code is being run on. I am currently using the code user = Environ('USERNAME') This gives me the actual log-in name (ex: jdoe) instead of John Doe. I want to get John Doe. If you click the start button on Windows it shows the full nam...
Rick Jeffries
1

votes
1

answer
47

Views

Union not picking up all ranges

I'm having some challenges with a Union not working in a big macro. It's supposed to join together a few ranges but it only seems to pick up the first range. To help isolate the challenge I've trimmed all the code down to this: Dim copiedrange As Range Dim SrcWB As Workbook Dim SrcWS As Worksheet Se...
Jossy
1

votes
2

answer
31

Views

How to lookup a value from another column and display Row number?

I'm setting up an excel file for the operators. They will scan the Barcode from the product and it will populate in one of the column of the excel sheet. I have tried using LOOKUP formula in excel but it doesn't seem to be working right. COL A : Data from Database COL B : Data from Scanner COL C:...
user7377353
1

votes
1

answer
37

Views

How to fix hh:mm format

I have an Excel file imported from a specific information system. I have been working with its automation through VBA code. However i encountered a problem while dealing with hh:mm cells. I can't sum them up, I tried formatting them to hh:mm, I also tried to format my output cell as [HH]:MM, none of...
Yassine Lachgar
1

votes
2

answer
63

Views

How to Make Cells “Blank,” not “Empty” in VBA

I using Labview to generate a Excel report that essentially pastes an array into the spreadsheet. There are gaps in the spreadsheet, for example: 1 2 3 1 2 3 But because I am inserting an array into the spreadsheet, the gaps are empty, but they aren't blank. When I run vba code checking each cell us...
dboolean
1

votes
2

answer
78

Views

Shapes (worksheet) vs. Controls (userform)

I am trying to call a worksheet placed label, but I'd like to dynamically call it by providing name string. I've tried many suggestions, this one gave me most hope: Can I reference an object using a string? I have successfully achieved desired effect in the past, but those applications were all user...
JohnT
1

votes
2

answer
305

Views

Replacing a string of text when only knowing a certain amount of characters

I have a list of PO numbers. They look something like (4010059877-TUR36036133 or TUR6039716## 4010073239). I need to be able to narrow the cell down to only the PO number which is the 4010059877 number. The only consistent part about the part I want to exclude is the 'TUR########'. I have worked on...
Matt Lane
1

votes
1

answer
77

Views

If NOT ActiveSheet.Range() Is Nothing keeps erroring out when it finds nothing

I have a sub that reads a text file and imports data to a Defined Name cell. There is some data in the text file i don't need, so if the sub cannot find a matching cell it is just suppose to ignore it. But, when the function finds a Defined Name that doesn't exist it throws an 1004 error. Putting O...
Geoffrey
1

votes
2

answer
99

Views

Using .bat file and task scheduler for execution of .vbs file [duplicate]

This question already has an answer here: How to set recurring schedule for xlsm file using Windows Task Scheduler 5 answers My Below Code is Running properly. 1.) Can someone help me know how to use task scheduler to schedule this VBS file? 2.) Also i would like to understand writing the .bat fil...
Kaustubh Ursekar
1

votes
1

answer
35

Views

Add multiple sheets in existing excel using pandas in Python

My code is reading SQL queries from text file and executing them one by one in python.I am trying save result of queries in the same excel but in different tabs/worksheets import pyodbc as hive import pandas as pd filename =r'C:\Users\krkg039\Desktop\query.txt' fd=open(filename,'r') sqlFile=fd.read(...
Anurag Chand
1

votes
2

answer
34

Views

Excel formula - How to separate a formula value in a cell into 2 different cells?

In my Excel sheet I have a column which gets the transposed data from a row of a different sheet. =TRANSPOSE(DATA!L170:CC170) An example of a cell's data is 0/300 and is consistent throughout the column. Now I plan to have 2 new columns next to the first column to show the numbers separate: | 0/300...
hjh93
1

votes
2

answer
25

Views

How can I expand a dynamic range with blanks between cells?

I want to define a dynamic range that will expand the rows and columns of my range whenever a new row or column is inserted. My current formula does not want to expand to cell $T$13. My headers start in row $M$7. How can I adjust my formula? Formula Being Used =OFFSET(Sheet1!$M$8,0,0,COUNTA(Sheet1!$...
Jose
1

votes
2

answer
49

Views

UDF to extract specific data from a generic string

I'm trying to create a function like =Extractinfo('A2','Name'), which can extract name, phone and email id from raw data, one function for all 3 extractions, I already have a function to extract email id Raw Data 'Name':'Ram','Phone':'9898989898','Email':'[email protected]' 'Name':'Raju','Phone':'23232...
sunil
1

votes
2

answer
44

Views

python efficient way to append all worksheets in multiple excel into pandas dataframe

I have around 20++ xlsx files, inside each xlsx files might contain different numbers of worksheets. But thank god, all the columns are the some in all worksheets and all xlsx files. By referring to here', i got some idea. I have been trying a few ways to import and append all excel files (all works...
yc.koong
1

votes
6

answer
80

Views

Extract numeric portion from an alphanumeric string in Excel 2010

I would like to extract all the numbers from an alphanumeric string in excel. I have an excel sheet with list of alphanumeric strings as shown below and I would like to extract all the numbers from the alphanumeric string and store it in a new cell I already tried the below formula found online but...
SELVA
1

votes
1

answer
38

Views

Get Total Hours From Excel TEXT Function

I have a column in my worksheet that is a subtraction of 'End Time' and 'Start Time', giving me the total number of hours:minutes:Seconds during. Sample Input: Start Time: 15/04/2019 1:38:00 PM End Time : 18/04/2019 3:00:00 PM Basic subtraction gives me the right value i.e. 73:22:00 However for...
Noman Amir
1

votes
1

answer
18

Views

Table under table in excel by sas

How can I generate on sas a table under a table on excel? I try to explain better my problem. I have to run a procedure every week. This procedure generate to me a table that I have to copy and past on excel. The sequent week I run and I copy and past the new table gained under the last one so I can...
user11284374
1

votes
5

answer
8.3k

Views

Function for detecting duplicates in Excel sheet

I need a function that can detect duplicates in a specified Excel column. I have this one but it does not work properly. It cannot distinguish between the value '46.500' and the value '46.5000'. The countif function probably compares cells as numbers. These cells are formatted as text and I have eve...
Plasmuska
-1

votes
0

answer
14

Views

Averaging a Dynamic range in Excel

I have a list of values that looks like this. Every 30min the top row in my data is shifted down and a new latest value is inserted. I'd like to be able to take the average of all the values in the model column from 18:30 to the top most cell each day (so just the latest cell that has an 18:30 value...
novawaly

View additional questions