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....
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

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...
SuperLoz
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
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,...
Perry Sugerman
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
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...
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
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...
rohrl77
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
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
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...
DJ Villareal
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
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...
DevonRyder
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...
30 Degrees North
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...
Harsh Vardhan
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...
Belladonna
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...
jedu
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
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

View additional questions