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....
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...
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?
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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 |...
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...
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...
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...
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...
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:...
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...
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...
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...
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...
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...
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...
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(...
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...
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!$...
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...
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...
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...
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...
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...
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...
-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...