Questions tagged [excel-vba]

17107 questions
1

votes
1

answer
478

Views

Outlook VBA find last Row in Excel Worksheet

I'm writing a function in Outlook VBA that involves reading content from an excel workbook. The part I'm struggling with is finding the last row in a column (column A in this example). While the 1st line in the highlighted block correctly displays the content of A1 cell in given worksheet, the secon...
T4E_Windrunner
1

votes
1

answer
826

Views

Can I get VBA equivalent of DateTime.Now.Ticks?

In VB within .NET I can use DateTime.Now.Ticks.ToString("x") to get a string ready for a multipart form boundary e.g. 8d4b7556f42792c I'm trying to get the same thing in VBA within Excel. In Excel VBA I can use DateTime.Now to get now in the format: 19/06/2017 20:54 That's not really helpful though,...
jamheadart
1

votes
1

answer
599

Views

How to do image url validation in excel 2016?

Is there a way to get reported Valid or invalid in excel if an image URL gives a 404 error? Without opening each image? eg checking header for 404? here's some snippets I've tried Function URLExists(url As String) As Boolean Dim Request As Object Dim ff As Integer Dim rc As Variant On Error GoTo End...
Rocket Spaceman
-1

votes
0

answer
19

Views

VBA running multiple macros concurrently

So I'm writing music in Excel right now. Yes yes, not the right program, etc. Unfortunately, I can only play one note at a time. Great for hot cross buns, less great for anything mild complex - like a left hand and a right hand on a piano at the same time. I was thinking that if I ran multiple conc...
Selkie
0

votes
0

answer
8

Views

Excel Userform textbox data validation

I'm creating an Excel Userform. I want to validate two textbox values. if textbox1.text= 1 to 5 then textbox2.value =>1 and 6 and 11 and 16 and
Yogesh
0

votes
0

answer
3

Views

VBA creating a User Defined Function, but that read data from another woorksheet within the same workbook

Hello Friends from Stack Overflow I was wondering if you could help me solve my issue this is the structure of my workbook one sheet called "export" has all the roomKeys per hotel (they can be repeated) (2k lines) one sheet called "delete" has all the roomkeys to be deleted (100 lines) what I wanted...
1

votes
1

answer
6.9k

Views

Can you “Merge Down” in Excel?

Excel has a "Merge Across" function built in that allows you to highlight a range and then automatically merge all the cells in each row, but NOT merge the rows themselves (for reports, mostly). Can you do the same on columns?
Devin Lamothe
1

votes
2

answer
5.1k

Views

Excel VBA - list all VBA environmental variables

How can I get excel to list me all available environmental variables on my "temp" sheet? Below code doesnt return anything for me... Sub ListEnvironVariables() Dim strEnviron As String Dim i As Long For i = 1 To 255 strEnviron = Environ(i) If LenB(strEnviron) = 0& Then Exit For Debug.Print strEnviro...
Armitage2k
1

votes
0

answer
19

Views

Displaying Duplex Printing Option with Excel

I'm trying to print a bunch of tabs in Duplex in Excel. Ok, fun times. This needs to be done in VBA, due to a lot of people needing this. More fun times. Excel doesn't have Duplex printing as an option in VBA (But Access does for some reason). You can access the option by going to File -> Print ->...
Selkie
1

votes
1

answer
2.2k

Views

adding multiple labels and textboxes to an Excel userform during runtime using vba

I'm creating an inventory management tool with Excel VBA. I've created code that gathers a list of names from a drop down box on Internet Explorer and puts them into an array. What I need to do is something similar to vba create several textboxes comboboxes dynamically in userform, but I would be dy...
Lou
0

votes
0

answer
3

Views

Previous Entries VBA

I can't find an answer to this so I apologize if it has been asked before. I currently have an excel spreadsheet set up that is tied to other workbooks. This secondary workbook has supervisors attached to employees and their shift. I have workbook 1 set up on a lookup so when I type the employee's...
Leslie Mccarroll
-1

votes
0

answer
7

Views

Zip all files in the directory from VBA (filepath to be taken from cell range)

Is there any way to input files in a better way as i got this code from rondebruin.nl, here the file-list are variant type, that is being - i need to choose the path of the folder from a dialog box every time, can it be automated somehow, like to place the file path in cell and the name of zip in ad...
Directionsky
0

votes
2

answer
16

Views

How to automatically insert a copied row after a specific value in a cell with VBA

I have a table that contains a set of bundles that have to be broken down into their components. For this I'm looking for VBA instructions that will copy any row that contains the tag "-edubnd" at the end of the 'sku' cell (please see table bellow for example) twice underneath itself. I have created...
Tim E
-1

votes
2

answer
46

Views

In SAP scripts, is it possible to select a column by name instead of by row position?

I have a script that successfully creates a layout variant. I choose the columns that I would like to be in the report by choosing the row number of where the column name is in the list, like this: session.findById("wnd[1]/usr/tabsG_TS_ALV/tabpALV_M_R1/ssubSUB_DYN0510:SAPLSKBH:0620/cntlCONTAINER1_LA...
Joe Messingschlager
0

votes
1

answer
11

Views

VBA Use Active Row Number in a Selection

I am trying to write a macro that will take my active cell's row number and use it in a selection. E.G. if my active row is A10 I want the macro to select A1:N10
Michael Pandola
0

votes
0

answer
4

Views

Match value Sheet1 with Sheet2, Copy put in Rows

I am looking for a macro VBA. On Sheet1, I have two columns ColumnA (Which can have the same DOC-Number) and ColumnB (different Spar parts) and Sheet2 I have only one column (Invoice-Number NO duplicate). I would like, if columnA Sheet1 matches with ColumnA sheet2 then copy all Spar parts from Sheet...
Kenwood1
1

votes
3

answer
14.1k

Views

How to find multiple strings in VBA

Assume an Excel sheet contains the following values in a random column: VARIABLE X AbbA AddA bbAA ccbb KaaC cccc ddbb ccdd BBaa ddbB Bbaa dbbd kdep mCca mblp ktxy Now the column should be searched for several words and word-phrases at the same time, for example the following: (1) "bb" (2) "cc" (3...
EDC
0

votes
0

answer
5

Views

PasteSpecial method of Range class failed in for loop

Writing macros to copy cells in a different workbook in a specific format.Getting error at different lines everytime I run the code I tried with unhide cells, selection `For i = 1 To lastrow If IsEmpty(ThisWorkbook.Sheets("Summary").Range("A" & i).Value) = False Then If ThisWorkbook.Sheets("Summary"...
DeepaB
1

votes
1

answer
2.3k

Views

excel vba shoDataForm Run-time error '1004' ShowDataForm method of Worksheet class failed

ActiveSheet.ShowDataForm Returns Error 1004 I tried Range("B4:D4").Select ActiveSheet.ShowDataForm It didn't Help
yesitsme
1

votes
2

answer
77

Views

Create function but only for use in VBA environment (not in workbook formulas)

This is new to me - I've created a function that I use for updating a SQL Server table via VBA. It takes in a range of cells and it returns a Long of how many records were affected. So, I would like this function to be available throughout my workbooks for use within VBA, but don't want it to show u...
John Bustos
1

votes
2

answer
64

Views

Why can't I use a single “Cells” reference as a “Range” parameter

This question: Copy from one workbook to another made me realize that while I knew how to fix the issue described, I didn't know why it caused an error. Range("A1").Select is fine. Range(Cells(1,1),Cells(1,1)).Select is fine. Range(Cells(1,1).Address).Select is fine. Range(Cells(1,1)).Select return...
nwhaught
1

votes
1

answer
37

Views

range as object in a cell and vlookup

Is there any way I can store a range as an object handle in excel, and then do a vlookup on the handle? For example: in cell D1 =create_range(A1:C10), and then in cell E1 =vlookup_range("foo",D1,2,FALSE).
NickD1
1

votes
2

answer
240

Views

Create a new row for each cell containing a value

I have a table similar to this: ID | Item 1 | Item 2 | Item 3 | Qty 1 | Qty 2 | Qty 3 1 | | X | X | | 3 | 4 2 | X | | X | 2 | | 1 3 | | | X | | | 9 I need to find a way to create a new row for each item pe...
MBrewer
0

votes
0

answer
4

Views

Creating Hyperlink by Concatenating Link Template with Text in Cells - Excel VBA

I am trying to turn a column that has text in it into a hyperlink. The columns will have information which I want to add at the end of the link template. For example, let's say: "/stackoverflow.com/" is my link template Range("A2") = "a_two" and Range("A3") = "a_three" I want to convert cell A2 and...
kkrk
1

votes
2

answer
229

Views

Populate cell in Excel wtih substring of another cell

In Excel, I have a CSV in which column A is populated with category like strings, such as: ABCDE/FGHI/JKL/MNOPQR I need to auto populate column C with the value of the substring of everything after the last occurrence of the "/". In this example, it would have to be populated with "MNOPQR" Is there...
katharey
1

votes
4

answer
10.3k

Views

Format column in excel with vba to date format

I am trying to reset the formatting of my excel sheet, the problem is that I have 4 columns which should be date format. How can I find all columns which contain "DATE" in header (Such as : last machined date, assembly date, order date etc..) and change this format to date? Note: Needs to be dynamic...
skatun
1

votes
3

answer
5.6k

Views

Display popup for a time period in Excel

I am trying to generate in Excel VBA a popup that automatically closes after a given WaitTime in seconds. I have consulted the thread "VBA Excel macro message box auto close" as well as this and this links. I have tried to apply the method from the StackExchange thread cited; my code is the followin...
Daneel Olivaw
0

votes
0

answer
6

Views

Excel 2011 for Mac - outputting PDF fails if File->Print selected beforehand

I have written a Macro-Enabled Workbook in Excel for Windows, and am now trying to get it to work in Excel 2011 for Mac. One thing my workbook does is allow the user to create a PDF of whatever sheets they want. My code works fine on Excel 2011 under normal circumstances. However, if I go to File...
Matt
1

votes
1

answer
18.2k

Views

Copy and paste format including colour of cells VBA Excel

wbTarget.Sheets("Sheet1").Range("A1:W79").Value = wb.Sheets(wsSource.Name).Range("A1:W79").Value I have this code which works pasting the values of wb.Sheets(wsSource.Name) to wbTarget sheets. However, it only pastes the value and not the format/color. How do I paste it including the fonts, colour o...
Desmond
1

votes
2

answer
191

Views

How do I turn off the View | Reading pane for Outlook folder?

I have this code to create a folder in Outlook from Excel. How do I set the View | Reading pane to 'off'? Set olFolders = olSourcefolder.Parent.Folders olFolders.Add "Audits-Actuals"'how do i set the reading pane to off?
Joe Patrick
1

votes
3

answer
2.1k

Views

Subtract a specified number of hours from a given date/time to get a new date/time

I'm trying to figure out how to calculate a date/time by subtracting a given number of hours from a given due date/time, so that I know when I need to start a process step on a product component in order to have it finished in time to ship to the customer. For example, the product needs to be ready...
Glenn G
1

votes
3

answer
1k

Views

Range.SpecialCells: What does xlCellTypeBlanks actually represent?

The Range.SpecialCells method can be used to return a Range object meeting certain criteria. The type of criteria is specified using an xlCellType constant. One of those constants (xlCellTypeBlanks) is described as referring to "Empty cells" with no further elaboration. Does anyone know what definit...
Daniel McCracken
1

votes
3

answer
351

Views

Excel: Compare two ranges and delete duplicate cell values

I have two ranges of data: Range1 is a list of available project names Range2 is a list of project names that are in use I'm trying to write VBA code that will compare these two ranges, and if any value exists in Range1, but not in Range2, then I want to delete that value from Range1. I have the fol...
pwm2017
1

votes
1

answer
50

Views

I am trying to copy and rename a sheet but I get subscript out of range error

I am trying to copy Sheet2 to Sheet3 and rename it to Result but I keep getting subscript out of range error (which happens because that sheet doesn't exist which probably means that the copying isn't happening). I tried using Copy as shown in the code below but the subscript out of range error kee...
dankmemes
1

votes
2

answer
61

Views

Can't find any way to get request headers

I'm trying to get the request headers using vba. In other languages like python there is a very easy way to get that .headers. However, in case of vba I'm getting stuck. I know though there is a way in vba to get response headers by using .getAllResponseHeaders. How can I get request headers using v...
robots.txt
1

votes
3

answer
33

Views

unintended autonomous changing a1 / r1c1 notation in vba excel 2010

i have an excel/vba issue which seems to occur in excel2010 but not excel2016. for me it is a non comprehensible conversion between a1 and r1c1 notation. i have a range that is dynamic Dim rng As Range rng = Application.Range("worksheet!A4:A" & _ Worksheets("worksheet").Range("A" & rows.Count).End(x...
error on line 1
1

votes
1

answer
28

Views

Setting a Worksheet using both the worksheet name as defined in the object explorer with the workbook definition

So there are a few ways to set a worksheet in Excel. set WS = Sheets("Name Here!") set WS = WB.Sheets("Name Here!") set WS = shNameOfTheSheetObject However, there doesn't seem to be a way to do: set WS = WB.shNameOfTheSheetObject I'd like to know how, if it's possible, I can get this type of referen...
Selkie
1

votes
5

answer
14.5k

Views

Process for updating Excel add-in?

I have an Excel 2003 add-in deployed in one of our departments that occasionally needs updated. What currently happens is that I publish the new version from my machine to a central location, this updates a version number stored in a database that the add-in checks each time any of the modules with...
Lunatik
1

votes
0

answer
11

Views

Copying sheets to existing workbook, workbook name is cell.value

Very new here. Having a hard time trying to debug. I am currently trying to make the macro work as the following: Create new sheets and rename it to cell. Value in range, and if there is a workbook (also named as the sale cell.Value) in the same folder as the macro, copy this sheet into the workbook...
Daisy
1

votes
2

answer
2.4k

Views

Snapshot of data structures, VBA excel [duplicate]

Possible Duplicate: What's the fastest way to export all excel add-in modules to .bas files? Is it possible to do snapshot of all used modules and data structures in VBA Excel and recover it some time after? The reason is reproducing error after crashes ADDED. Yes, in essence I want to serialize cl...
nikaan

View additional questions