Questions tagged [vba]
45963 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
Looping an Offset “up” a column
I'm looking to loop up a column, offsetting by one row and adding 1 to the value of the row below:
Example:
A
1.....8
2.....7
3.....6
4.....5
I've built the code (shared below) to identify and fill the correct value of the bottom row (based off another spreadsheet) and filled the cell above. But str...
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...
0
votes
0
answer
8
Views
How to set the “collate” property of a Printer
I am running Ms-Access reports through my application in vba. When a user wishes to print a report, my application exposes its own print dialog to choose the content and format of the report. In some cases the user may decide to create multiple copies of the report. The report is generating tickets,...
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
2
answer
66
Views
Use VBA to enable a text box when a check box is checked
I am new to VBA and I'm trying to create a form in Access where a text box is enabled or disabled based on whether a check box is checked or unchecked.
So if the 'Survey Requested?' box is checked the 'Date Survey Requested' box is enabled for the user to enter a date.
I have the following code:
Pri...
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
34
Views
MS Access DoCmd.Transferdatabase when source has a password
I need to import tables from various databases on a monthly basis. Once the tables are imported, the databases are archived and not looked at again.
I have the following VBA code which works fine when a DB is not password protected:
Private Sub ImportTheData(ByVal dbImport As String)
DoCmd.SetWarnin...
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
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
1
answer
52
Views
How object passed as an argument ByVal should behave
I am studying/learning the bahavior of ByVal and ByRef when it comed to working with a call object. So I created this class PersonModel
Private Type TPerson
firstName As String
lastName As String
End Type
Private this As TPerson
Public Property Get firstName() As String
firstName = this.firstName
En...
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
3
answer
61
Views
MS Access VBA, How to Send an Email with Outlook?
I am working with an Access application within Acess 2016. Part of the application outputs to a PDF file via the DoCmd.OutputTo method. I then want to either send this PDF attached to an email I build out in code, or open a new Outlook email with the file attached.
My issue is that when I click the...
1
votes
1
answer
36
Views
Export Powerpoint comments to Excel
I am trying to create a macro to export Powerpoint comments into Excel, with columns for different headings such as author, slide number etc.
Tried using the code I have for Word for this macro, which works fine, however being a novice at VBA I don't know how to customise this code for Powerpoint
Su...
1
votes
3
answer
47
Views
How to separate words from single column
I am trying to read data from a text file using excel/VBA. However when i import the file all the words are in the same column. The text is generated from PDF while preserving the table layout. So the words are space delimited, but the spacing is not consistent. I want code to run through cells and...
1
votes
3
answer
96
Views
Is there a way to interrogate the status of AS400 jobs from VBA?
I'm trying to provide a user interface in Access to display the status of scheduled AS400 jobs, to show them as either pending, running, or complete.
I've inherited some code which schedules several jobs, which runs fine - but there's no way the user can see what the job status is, and they sometime...
1
votes
1
answer
33
Views
MS Access collection object value is being overwritten
I have a global collection object in a form that I am using to store IDs.
Dim newCollection As New Collection
When a user hits a btn
Private Sub btn_Click()
Dim key As Variant
newCollection.ADD Me.ID, CStr(ID)
For Each key In newCollection
Debug.Print key
Next key
End Sub
When a user is at ID 1 in...
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
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...