All Questions
Tagged with vba microsoft-excel-2016
136
questions
0
votes
2
answers
546
views
Transpose Group of Rows to Single Row | MS EXCEL
I need to Transform Some Uneven Group of multiple rows to single row
I Tried to Extract the Required data from multiple row to multiple Column. But all data placed under different Rows (same as input ...
- 115
0
votes
0
answers
53
views
Is there a way to stop previous day's values from being recalculated after new data comes through on the current day
My Excel formula depends on data I receive from a website every morning. My problem is, when new data comes, data from previous day recalculates & affects current day's value as it is also ...
1
vote
1
answer
584
views
Excel: Split a 2 column range (A:B) into multiple smaller columns with a pre-set number of rows
I'm struggling with a problem; I'm trying to split two columns (A&B) containing anywhere from 100-1,000 rows of information which need to remain together, into multiple columns containing a pre-...
- 21
1
vote
1
answer
116
views
VBA code to enable three Cells, each in its own worksheet, equal to each other always
I have three cells in three worksheets: Cell A1 in Sheet1, Cell A2 in Sheet2 and Cell A3 in Sheet 3.
I would like to have values in the three cells always equal to each other. This means that an ...
- 113
0
votes
1
answer
435
views
Excel VBA How do I set active cell to be only a cell with X data?
Dim NA As String
NAFinder = 9
NA = ActiveSheet.Range("F9").Select
For i = 0 To NARows
NA = ActiveCell.Offset(i, 0).Value
If NA = &...
0
votes
0
answers
764
views
How to prevent crashing my 64bit VBA application when opening a workbook containing 32bit VBA code
I am writing a VBA application to preprocess excel files that I receive periodically from a client. These files contain 32 bit VBA code (which I don't care about), and I am running Excel 64bit.
When ...
- 1
0
votes
0
answers
254
views
Can Excel only display the results from a FILTER, ISNUMBER, SEARCH formula and not the entire array of searched data?
I am creating a spreadsheet that can search an entire "Control" data table using two search boxes.
Search TextBox1 searches by UPI
Search TextBox2 searches by Zip
The two search boxes are ...
- 13
4
votes
3
answers
11k
views
Excel - copy sheet to another workbook WITHOUT names and links
I would like to copy sheets from one workbook to another, which is very convenient. However, I've noticed sadly, that even if one sheet is copied, the other stuff from the source workbook comes either....
- 133
0
votes
2
answers
2k
views
Excel VBA - Checking Cell Value in Relation to Another Cell
I want to highlight a row of cells in columns B-K if that part has less than 5 QOH (Column J). I also want it to only highlight the row if the part is in the subinventory "W1" or "...
user1462614
0
votes
1
answer
459
views
VBA - Setting Side Borders in Varying Columns and Rows
Everyday I run a list, it is always different. It will have different amounts of rows with different values in them every day.
I have coded it so I have horizontal borders exactly as I want, but now ...
user1462614
1
vote
2
answers
663
views
VBA Excel - Checking Text Similarity in Same Column
Image 1
Image 2
I am trying to check for text similarity for the text in the "Line-Side Loc" in Column J. If the text in column J isn't the same, I would like a thick bottom border applied ...
user1462614
-1
votes
1
answer
130
views
Unable to see the cells in the Excel workbook
I am unable to understand this worksheet which was written by someone else. There are certain columns which are not visible unless activated. This activation occurs when I change a value in particular ...
- 1
0
votes
1
answer
132
views
How can I delete a column in sheet 2 based on sheet 1?
I have two different sheets: "Input"(sheet2) and "Instruction"(Sheet1)
I want to delete the whole column in "Input" sheet corresponding to the value in the cell(row 34, ...
- 5
0
votes
1
answer
818
views
Conditional formatting for specific group of values/rows based on cell value(s)
I am having some data table that is separated in lets say "blocks" of rows. As you can see on the image, starting from Column D (A1) there are some values in first row, and then 5 rows empty ...
- 121
1
vote
1
answer
488
views
MsgBox based on 2 values in different cells
I am completely a VBA novice and am trying to achieve the following, without success.I would really appreciate your help.
What I need to achieve:
If someone selects for each row Action = "Delete&...
- 23
0
votes
1
answer
48
views
Error from custom function
Function IFBLANK(param1 As Variant, param2 As Variant) As Variant
If param1 = "" Or param1 Is Null Then
IFBLANK = param2
Else
IFBLANK = param1
End If
End ...
- 107
0
votes
1
answer
36
views
Macro to skip filtered rows
I have the following macro which does the following:
Get the file names from a folder on Windows - this is only done if the list and folder is sorted A to Z
Combines the name with the folder name
...
- 1
0
votes
2
answers
140
views
Parse out specific number in filepath
I pasted several filepaths from a folder into one column in Excel. I want to parse out a varying eleven-digit number found in every filepath. What is the easiest way to do this?
Here are some sample ...
0
votes
1
answer
984
views
Automatically update formulas to link to a new sheet in Microsoft Excel 2016?
I'm creating a project management worksheet to generate automated reports on various projects the charity I work for is running.
Currently I have a sheet per project, and a data collation sheet which ...
- 1
0
votes
1
answer
19
views
Writing Code within a Range
I am writing an excel program for tracking where tools at our workplace are located. I am trying to move data from one page to another. I have VLookup to access the information but I want to be able ...
- 1
1
vote
0
answers
164
views
Deleting table rows in Excel VBA by cell value - doesn't delete all rows, why?
I created this macro in Excel 2016, and the problem persists in Excel 2019.
My spreadsheet has a macro to delete all rows in which a cell in a specific column contains the word 'Closed'. The macro ...
3
votes
1
answer
315
views
Transpose unique rows to columns while IF statement conditions are met but do not consolidate
Current State:
A B C D E
FG Component FieldKey Cost$ Qty
7120 7120 1220 $4.71 9,783
7120 9907062 1220 $7.80 4,061
7120 ...
- 45
0
votes
0
answers
111
views
Excel: Dynamic change cell with Date/Time when any other cell is changed in the worksheet
I am trying to decypher a formula which will stamp auto Date/Time or some text when I change any random cell (more instances) inside some certain range (for instance A1:K45).
What I did is for one ...
- 121
0
votes
0
answers
39
views
How to replace formulas into values but with possibilty to remain referenced to its original source (dynamically)
I would like to have the possibility to convert some formula (in my case CONCAT) into values, but with the possibility to select those values and not to have formula shown, and also to be able to ...
- 121
0
votes
1
answer
23k
views
Excel: Is it possible to select multiple values in Drop-down list? (without VBA)
Is there any possibility to mark more values in drop-down list and to have them in one single row?
This is the way I found but still it is not inside a single row...
My DDT
And this is actually ...
- 121
1
vote
1
answer
2k
views
How to Copy Range of Filtered Data and Paste it to new work sheet in Excel VBA (without Using Clipboard)
I want to Copy ColumnA and B to New sheet Range A and B (Source Sheet Filter Applied in ColumnH)
This code is Recorded one, When i use this Random Error comes. Because i have 5 submacro and When i ...
- 115
1
vote
0
answers
118
views
How can i compare 2 columns row by row in excel and then extract unique information?
My Question(s):
1) Being able to compare Column A word in Column B and highlight the difference but doing this comparison row by row between the 2 columns.
Iam working with a large data set of 48k+ ...
- 11
1
vote
1
answer
299
views
Excel, list comments then hyperlink to original comment location
I can use the function called ListComments() from https://www.myonlinetraininghub.com/working-with-comments-in-vba.
'
' Written by Philip Treacy
' https://www.myonlinetraininghub.com/working-with-...
- 67
0
votes
1
answer
347
views
Why does my VBA function not evaluate the source cell that returns a HYPERLINK based on an IF statement
I have a cell with an IF function which either returns an empty value, or a hyperlink, based on whether another cell is empty or not
=IF($R7="","",HYPERLINK(CONCAT('Global Values'!$B$1,$R7),"LINK"))
...
1
vote
1
answer
357
views
Excel Macro not going in the order it's supposed to
I have written a macro which is supposed to run an add-in on each worksheet.
The Add-in is a Sage specific Add-in. I'm sure the sendkeys should work with whatever sendkeys I put in?
However, it runs ...
0
votes
1
answer
2k
views
Excel VBA - Copy & Paste macro loop pastes each file over the last
I have a question regarding a macro that's not performing in the way expected. I took a script I found online to cycle through a number of files in the same directory and perform the same action on ...
- 23
0
votes
0
answers
499
views
EXCEL: Macros Disabled Error Msg and Double Pressing ENTER
This XLS built with a user defined function (UDF) and some other VBA works and has worked for a couple of years without issue. That is until a new image of Win10 was loaded onto my work computer. So ...
- 143
0
votes
1
answer
32
views
How to turn a recorded Macro into an automated Macro in VBA when data come from another sheet?
I have recorded the following macro to sort data in the range "B3:U47" based on the values of the column "C3:C47".
Sub Sorting_Finance()
ActiveWorkbook.Worksheets("Finance").Sort.SortFields.Clear
...
- 11
1
vote
1
answer
53
views
How to turn a recorded Macro to an automated Macro in VBA?
I have recorded the following macro to sort data in the range "B3:U47" based on the values of the column "C3:C47".
Sub Sorting_Finance()
Range("B3:U47").Select
ActiveWorkbook.Worksheets("...
- 11
0
votes
1
answer
639
views
How to paste specific cells in .bat file?
I have this Code that Exports all my Excel values into .bat Format, i am trying to Export all the values except the headers. This is the Code :
Public Sub Export_File_as_BAT()
Dim wbkExport As ...
- 3
0
votes
1
answer
327
views
How to highlight the rows relating to the "Count Of" in a pivot table?
I have a table with values, and next to it a pivot table showing count of certain fields (same sheet).
I want to be able to click the field or count value on the pivot table (either number or field ...
1
vote
2
answers
2k
views
MS Excel - Separate text based on keyword
I need to separate text based on a keyword to different columns. the original text is in one column but I want to split the intended subtext into different columns. The problem in this case is I get ...
0
votes
3
answers
6k
views
Excel "Can't find Project or Libary" - but which one?
Using Office 2016. I have an Excel file with VBA. Whenever I open it, I get the error "Can't find Project or Library". Excel immediatlely closes.
I opened Excel and, without opening the file, looked ...
- 233
1
vote
0
answers
150
views
Application.OnKey only for right ctrl in VBA
Is there any way to apply Application.Onkey method only on right (or left) ctrl (or alt, shift) key. I have been reading documentation and can not find it. Code will be run in Excel 2016.
- 499
1
vote
1
answer
713
views
"Are you sure..." pop-up when saving file in Word
How can I add pop-up "Are you sure you want to save this file?" when I press ctrl+s in Word 2016, with VBA.
I was able to do it in Excel 2016 by adding Application.OnKey "^s", "Macro_Name" command ...
- 499
0
votes
2
answers
1k
views
"Dir(path) <> vbNullString" gives false negatives if path is an empty folder or contains only subfolders
(I made a similar question recently. I've deleted it because I realised I was wrong about what's causing the error.)
I have a spreadsheet containing cells with file paths in them. I wanted to ...
0
votes
1
answer
73
views
Possible to check if a VBAProject is clicked?
VBA excel -- When a VBAProject is clicked or double-clicked, it will trigger a macro. <<<< Is there any way to do that? How to determine if my VBAProject is clicked or double-clicked? ...
- 187
2
votes
1
answer
516
views
VBA - How to find a string in combobox names?
Some of my combobox names start with "TBSum...". The objective is to search for all comboboxes that has this string "TBsum" and color those boxes with back_Color and fore_Color. My code below, doesn'...
- 187
0
votes
1
answer
265
views
Check two dates using Excel VBA code IFERROR, but still it's yielding a Error 13
My code is reporting:
Run Time Error 13:
Type mismatch.
How can that be if I use an iferror function?
Please note, the value in payroll_Start or payroll_Last should be a date but it could ...
- 187
0
votes
1
answer
51
views
VBA - Cannot create dropdown list for UserForm9.ListBox1
Need help to create dropdown list in UserForm9.ListBox1. My code below... The error I get is "Run Time Error 380 - Object doesn't support his property or method." -- which doesn't make sense to me ...
- 187
1
vote
1
answer
381
views
VBA - ListBox cannot populate
omg, spent 4 hours trying to debug this, but to no avail. Please help? My code is below. The objective is straightforward: Search in the database under column B (employee's name), and then ...
- 187
0
votes
0
answers
36
views
clear cell content when value of another cell on another sheet is equal
I have a master sheet where all the data is copied from other sheets.
I need to clear the content of the master sheet from the column which is a Ctrl+F value of the other sheet E2 column.
How can I ...
- 1
0
votes
1
answer
104
views
VBA - How to loop procedures within the same macro?
How to loop procedures? I don't like "calling" sub because it's too volatile/crash-prone and it flashes my screen... so how to loop procedures within the same macro...? Example:
Sub LoopBase()
...
- 187
0
votes
0
answers
208
views
VBA - Function not working to retrieve file name . .
My code below does not work for Case #6 "FILE NAME". Getting an error #NAME? . . . I cannot figure out why I have the error. Please help? Also separate question, why my letter "p" not capitalized ...
- 187
0
votes
1
answer
1k
views
TextBox strings to multiple rows
I have a an ActiveX Multi-Line TextBox1 embedded in a Work Sheet. It has values (separated by line breaks/carriage returns) that I'm trying to get into multiple cells (A1:A5 in this case, but however ...
- 15