Questions tagged [vba]
Visual Basic for Applications; a subset of Visual Basic used in all Microsoft Office applications. Consider using this tag together with the tag for the specific application in question. Good questions on VBA generally contain some code and asks for solution of specific issues. Questions asking for entire solutions (please give me the code / how to do this and this with VBA) are off topic and will be closed as too broad.
3,757
questions
0
votes
1
answer
811
views
How to Automate Outlook to Count Emails [closed]
Gentlemen, I have a graph made in Python where it shows the count of how many emails were received for "Refunds", "Received", "Account Closure". However, this "data&...
- 3
0
votes
1
answer
136
views
Auto Update Date & Timestamp in a specific cell irrespective of any data range (With or Without VBA)?
Would anyone be able to help me with this issue?
I have data based on the categories (as per spent) in specific columns & cells, i.e.,
C4:C25, F4:F25, I4:I10.
However, I want the "Updated ...
1
vote
0
answers
595
views
Excel macro / VBA: How to export a range of rows to a PDF
I have an Excel worksheet that contains customer requests. I'd like to export a list of each customer's requests to different PDF sheets. For example, Column A has the customers name (CustA, CustB, ...
0
votes
1
answer
102
views
When an Ecel workbook with LAMBDA function must be saved as macro enabled?
I am looking for the LAMBDA function to replace the below VBA function for evaluating strings:
Function Eval(Ref As String)
Eval = Evaluate(Ref)
End Function
I made a minimum function in the Name ...
0
votes
1
answer
237
views
Keep VBA code working if I run it from a button in another sheet, or replace the code with formula
I'm trying to sort the date in an excel sheet, I want to click on a button to do this, but button is not at the same sheet
I assigned the macro in a button in the same sheet, it worked very well, but ...
- 45
0
votes
2
answers
1k
views
Create a macro that automatically updates time & date but doesn't change when I type in other cells
At my job we have this CSR sheet that we use for self service.
The idea is you fill each row as a call is taken every day
and then email it to our boss.
I put an example of the sheet at the bottom.
...
0
votes
0
answers
60
views
segregate the Test data in cell A and B and create a new sheet with their individual combo as listed in row 8
The example has been updated through the image provided below. Hope this helps
0
votes
1
answer
272
views
Stop Excel Power Query from adding previously deleted rows
I have a power query that pulls data from several Excel workbooks that are added daily into a folder.
These workbooks are generated automatically and I group it in a single table for analysis.
I have ...
-2
votes
1
answer
420
views
Consolidate first letter in single cell [duplicate]
Have been looking for a while and can't find a solution.
I am trying to find a way to consolidate the first letter in single cells using a formula/vba. I am new to excel and vba but slowly learning.
...
- 1
0
votes
0
answers
1k
views
Excel VBA - Expand and collapse specific column and row groups on multiple identical sheets
I have a spreadsheet with multiple sheets, each sheet contains the same table with a number of column sections grouped and a number of row sections grouped (the same columns and rows grouped in each ...
1
vote
1
answer
113
views
VBA input box not yielding to the user input
I have a VBA code to receive input from user and set the input a filter criteria, however, when the user input the value, and the function is executed, the filter criteria will be blank. I am having ...
0
votes
1
answer
97
views
MS Access Runtime closes database when there is a mirror error. How to Ignore?
There is a MS Access Database. Sometimes there are pop ups errors: "The value you entered isn't valied for this field" and then a Macro error. But that's OK, I just click "Stop All ...
- 105
1
vote
1
answer
221
views
Use Cell references within SUM function (Excel)
I am trying to create a function in one sheet, that sums up information on another sheet and as I will be running this multiple times with different datasets, I need this to be flexible. I am trying ...
0
votes
1
answer
524
views
Outlook VBA - go to specific public folder wthout giving the complete folder name
I usually need to go to some specific folders to see the containing emails.
These folders are in Outlook exchange public folders.
I have managed to write a script that I can enter the full name of the ...
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
0
votes
1
answer
214
views
Compile Error: Syntax error
Can someone please let me know where the error is in the below code?
Is it that the list is too long? I don't know why I'm getting the syntax error.
Grateful for any help you can provide.
Private Sub ...
- 13
0
votes
2
answers
734
views
Merge Rows and Sum Single Value in Excel
I want to merge rows with mostly similar data but sum their quantity values, using the unique "id" value while keeping the rest of the data in the row intact.
The raw output looks like this:
...
- 3
0
votes
1
answer
449
views
VBA code to auto hide rows based upon text in cell A1
I cannot seem to get my code for this sheet to work properly.
I have code in there functioning fine which will hide rows based upon whether cells in a range are empty.
However I would like to ...
- 1
0
votes
3
answers
753
views
Formatting cells to 000-000-0000-0000 in MS Excel
I would like to format cells in excel to the following format: 000-000-0000-0000. This is only an example and the zeros can be replaced with other numbers, but the hyphens need to stay.
I think regex ...
- 121
0
votes
1
answer
1k
views
Excel VBA: How to use RegExp.replace to remove characters from a string?
In VBA Excel I am working with Regular Expressions.
I have enabled Microsoft VBScript Regular Expressions 5.5 and am trying to use RegExp for the following.
Input: "234;BB-154"
Expected ...
- 336
0
votes
0
answers
34
views
Turn link into URL when URL contains "="
I am using the following VBA function to turn a link into a URL
Function GetURL(cell As Range)
GetURL = cell.Hyperlinks(1).Address
End Function
I get #VALUE as a result
I believe it is because ...
0
votes
1
answer
78
views
overflow error when to assign value in a cell
Assign value 300000 in the cell(1,1) :
Sub test()
ActiveSheet.Cells(1, 1) = 300000
End Sub
Now replace 300000 with 500*600:
Sub test()
ActiveSheet.Cells(1, 1) = 500 * 600
End Sub
Why it ...
- 131
0
votes
0
answers
310
views
Excel VBA group by issue
ive been working on a group by function however ive run into an issue, because when the macro comes across a sheet with one row then it throws an exception at the commented line below when really it ...
2
votes
1
answer
157
views
VBA - Dynamic range skipping rows
For X = 9 To 36
For Y = 8 To 22 Step 14
If WS1.Cells(X, 1) = "TOT" Then X = X + 2
Buy= WorksheetFunction.SumIfs(Calls, Hour, WS1.Cells(X, 1), Data, WS1.Cells(Y, 2), Service, Number) + ...
- 21
0
votes
0
answers
732
views
How to copy and paste without overwriting in Excel using vba
I'm trying to paste a set of columns from one sheet to another. I want to paste the column WITHOUT deleting or overwriting on it. Can anyone suggest how to achieve this using vba.
Sub copy_data()
...
- 1
2
votes
0
answers
228
views
Comparing one street address with a column of street addresses to find a match
I have two data sources with street address names, cities, and zips that I'm trying to find which addresses have the highest match percentages and return the maximum value. I have a VBA code that ...
- 21
0
votes
2
answers
124
views
How can set integer in column as text instead of #####?
The value in column E is shown as below , all integer with Scientific way:
I want to format it as text,all integer number shown as text format:
With Worksheets("Sheet1").Range("E:E&...
- 131
0
votes
2
answers
38
views
Why can't print the row number in sheet 'DataSet'?
I want to print the row number in sheet DataSet with vba:
Sub cal()
Dim x As Integer
Worksheets("DataSet").Activate
x = ActiveSheet.Range("A65535").End(xlUp).Row
...
- 131
0
votes
1
answer
275
views
Hiding all rows whose visible cells are all empty but may have non-empty values in hidden cells
I'm trying to code a VBA macro that hides rows whose visible cells are all empty. This means, that rows whose visible cells are all empty but which also still have hidden cells with non-empty values ...
- 115
0
votes
1
answer
1k
views
VBA variable and formula
Sub Test()
Dim ABC As Integer
ABC = [A1+(B2*C3)] 'Works fine
Dim i as Long
Dim XYZ As Integer
For i = 1 to 100
XYZ = [A & i +(B & (i + 1) * C & (i + 2))] 'Not ...
0
votes
0
answers
226
views
How can I declare current page in vba macros word?
I want to know how to declare between current page, next page and a counter.
I have this code:
Dim docNew As Document
With ActiveDocument
Set Rng = .GoTo(What:=wdGoToNext)
Rng.Collapse ...
0
votes
0
answers
29
views
Use Range on non adjacent Rows for graphs in subroutine
I am trying to set up several graphs with different non-adjacent rows for a line diagram (xlLineMarkers), but i cannot figure out how to hand over the defined ranges from a different sub.
Code as ...
1
vote
1
answer
113
views
Making the VBA code affect a specific range of pages
I have this very simple code
Sub SortByTime()
'
' SortByTime Macro
' Sort by Time
'
' Keyboard Shortcut: Ctrl+Shift+M
'
Range("C4:J43").Select
ActiveWindow.SmallScroll Down:=-36
...
- 45
0
votes
1
answer
366
views
How to put a Textbox on all word pages (No Header)?
I try to add a text box on each page to be able to modify in my own way (later) a book where it has "correct answers on that page (the pages will be on a specific page that I will not know until ...
0
votes
2
answers
2k
views
Move data from multiple columns into one and remove duplicates
I currently have a situation wherein I require the unique data from multiple columns to be moved/identified in one single column.
The data currently looks like this:
Apple
Microsoft
Dell
U.S
U.S
...
0
votes
1
answer
225
views
Excel VBA find next cell if its empty add text
I have a worksheet with many buttons, If there is date in a field i want it to go to the next line, if there is not i need it to return a N/A or a 0.00. my code works if i just do this with one cell ...
0
votes
0
answers
45
views
Macro that copy pastes a fix range into another sheet and takes into consideration a cell value
I'm pulling from Yahoo Finance the historical stock prices. You can only get with Power Query 100 lines. I have downloaded the history in one sheet and I have the Power Query with the last 100 days in ...
1
vote
0
answers
19
views
Excel 2019 Using VBA to colour sets of rows of cells w. two specific alternating colours, using unique value to identify which rows make a set [closed]
Colour rows of cells w. two alternating colours?
Windows 10
Excel 2019
I have a spreadsheet that contains 31009 rows of data across 10 columns.
The rows make up unique sets of data that belong ...
- 77
0
votes
1
answer
135
views
Excel - list comments then hyperlink to original comments
I used the code listed in this post below including one extra line in comment within the same post to add the hyperlink. However, the hyperlink only works when the tab name has no space in it (i.e., ...
- 1
0
votes
1
answer
211
views
format date returns wrong result
I use an autofilter macro to get values from today - 3 months, but while the result seems to be correct, the formatted date is shown wrong:
Sub LastThreeMonths()
Dim strDateFirst As String
...
0
votes
0
answers
221
views
Count dependents in other sheets VBA
I want to count the number of dependents of a cell in same sheet as well as in other sheets using VBA.
I have used below code and results are in the picture.
enter image description here
Sub tst()
Dim ...
- 55
0
votes
1
answer
36
views
getting run time error when un-protecting the selected range
here i have getting run time error when i am un-protecting selected range by (UnProtectRange)-VBA and selected range is not unprotected
i have used (ProtectRange)-VBA Macro to protect range by ...
- 1
0
votes
0
answers
67
views
How to Insert an autofilter if cell in row range contains value
I'm trying to create a function that inserts a filter within a cell range if a cell contains a value
Here is my code:
Sub FilterFunc()
Dim i As Long, lastCol As Long
Dim rng As Range, cell As ...
0
votes
1
answer
370
views
Excel - Transfer inputs from one sheet to another sheet to do calculations and returns a value
I'm just wondering if I could link the two sheets in Excel where you can transfer inputs from Sheet 1 to Sheet 2 to do some calculations and then return a value back to Sheet 1.
See the attached ...
- 1
0
votes
1
answer
328
views
Excel VBA: Flipping cells horizontally along with their formatting
I am trying to write a code which will flip my cells horizontally via Virtual Basic along with cells formatting (I need background colour to flip too).
What I mean by flip is this:
I came with the ...
- 554
0
votes
1
answer
439
views
Macro to create folder\subfolder\file.xlsm via Shell(cmd /c md)
I found this amazing code by nixda ... which creates a folder\subfolder structure named with values from a worksheet's cells.
Creating folders and sub-folders with a VBA macro .
Would it be possible ...
- 1
0
votes
1
answer
2k
views
MS Excel: generate a table with the number of rows depending on the input variables
So I want to make a matlab/r-like tool in excel that mimics more code driven calculations than the more common excel worksheet applications typically contain
This workbook should contain:
input sheet
...
- 103
0
votes
1
answer
120
views
Range Definition for Autofilter in VBA Excel
I was wondering, what the correct way was to define a Range for an Autofilter.
When recording a Macro it comes back with:
Range("$A$4:$AS$600").AutoFilter Field:=1 Criteria1:= "Apples&...
0
votes
0
answers
199
views
Excel freezes when running a macro which works on another sheet
when i run the below code on a sample document with a few columns, it works fine but as soon as i run it on a larger document excel just freezes
SampleDoc
Sample Doc code
Sub RCOps_Compare()
...
- 1
0
votes
1
answer
51
views
Strange errors from Excel, when running VBA-macros
The macro stops before execution, points to a Trim(...) call (a VBA built-in function) and says:
Extra tag: 'Can't find project or library'
- 9,469