VLookup On Another Workbook

Trying to make a macro that will take an input (a 13 digit number) from cell A7, A8, A9 and so forth until a blank cell is reached, and run a vlookup with this against another workbook. However, I'm just getting the #N/A error and I can't work out w...
more »

2017-01-17 20:01 (2) Answers

Referencing an open excel workbook

fairly new to VBA in excel and having an issue with trying to reference an already-opened workbook. The code below works well until I get to the line where I try to activate the ProductionBook. I get the "subscript out of range" error here which lead...
more »

2017-01-17 19:01 (1) Answers

Getting value from user

I am new to VBA. I have recorded macros but haven't written any code myself. I am attempting to get input from a user and place it into a cell. My attempted code is below. Sub test () Dim myvar as string myvar = "testname" 'Put item in cell End s...
more »

2017-01-17 17:01 (3) Answers

Overflow when using DateAdd Excel VBA

I currently have an Excel sheet with a series of dates and a cell in a different sheet with the date when we last ran this macro. I need to write a macro which checks if today's date is at least 32 days after the day we last ran the macro. If so, ...
more »

2017-01-17 16:01 (0) Answers

VBA Sorting of Data

The problem i run into is that sometimes entire headers and data values are missing in the dataset and therefore using the last row in the script the data is shifted up by one. For example, if i removed H11:H12 completely on sheet1 then the values...
more »

2017-01-17 16:01 (0) Answers

Create a color vector in VBA

I am writing a macro that checks through multiple years worth of data sheets to find specifically colored cells. Unfortunately, humans suck and have not consistently chosen the same cell color throughout the years (they may all be the same to the hu...
more »

2017-01-17 15:01 (1) Answers

Excel: get hyperlink from a linked url

Sorry for the impenetrable question title. I have a workbook with a load of links in a table, generated by the HYPERLINK function as follows: =HYPERLINK(CONCAT("http://google.com/search?btnI=I%27m+Feeling+Lucky&q=",[@[Patent No]],"&sourceid=...
more »

2017-01-17 13:01 (1) Answers

How to detect open Excel files in a macro Excel?

How can I detect all open Excel files and run through them, in order to lock their sheets? I have an Excel file containing the macro. Once it is launched, it would go through all my open excel files, and lock their sheets by setting a password. Jus...
more »

2017-01-17 10:01 (1) Answers

Using an output of aformula in another

Someone here posted this formula as an answer to the qustion how can one use a formula to know how many cells were used to create a merged cell: Public Function MergeSize(r As Range) As Long MergeSize = r(1).MergeArea.Cells.Count End Function ...
more »

2017-01-17 10:01 (1) Answers

Autommatically execute a macrop

I want to write a VBA macro which triggers another macro after changing the value of a cell. This is what I came up with. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Target.Worksheet.Range("n7")) Is Nothing Then...
more »

2017-01-17 09:01 (1) Answers

Reading from already open COM (serial) port?

I am trying to control a bench multimeter (GW Instek GDM-8251A) from excel/vba. I could not find a description of the protocol so I need to reverse engineer it. The communication method is serial over usb. So I have loaded in VBA the module from th...
more »

2017-01-17 04:01 (0) Answers

VBA I can't work Exists method with Dictionary

Dim memberDic As Object: Set memberDic = CreateObject("Scripting.Dictionary") Set memberDic("Team A") = CreateObject("Scripting.Dictionary") memberDic("Team A")("Tanaka") = 1 memberDic("Team A")("Watanabe") = 2 memberDic("Team A")("Shimizu") = 3 ...
more »

2017-01-17 03:01 (0) Answers

How to add more documents into Excel file

I want to add more files into my excel file. This macro works if multiselect is false, however if I try it with multiselect = true, I get an error message in row: if attachment = false then exit sub. An error message was type mismatch. Sub i...
more »

2017-01-16 23:01 (1) Answers

VBA used save range as input for diagramm

after a match operation in VBA - I get a range variable Set rngSel = ActiveSheet.Range(Cells(3, 28).Offset(0, startIdx - 1), Cells(3, 28).Offset(0, endIdx - 1)) It tells me that the data that I want to visualise in a bar chart are between A3 and...
more »

2017-01-16 21:01 (1) Answers

Excel VBA Save path/name from cells with variables

I am using cells as the file path and filename to save a copy of my workbook. Here's the code am using now but it puts spaces in between each cell. Note only the ActiveSheet.Range cells will have the possibility of being blank Dim NewWb As Workbook...
more »

2017-01-16 19:01 (1) Answers

Disabling "cut" from excel with vba

I've read many threads on this topic, but the code I have found there doesn't seem to work. I am trying to disable the "cut" function from an excel spreadsheet and I would like the icon to grey out. I have been using this code: Sub WorkSheet_Activ...
more »

2017-01-16 17:01 (1) Answers

Excel VBA input box letters only and max length

I want an input-box that only allows 1-3 characters in length and only letters A-Z. I've tried a few different ways but cant seem to get anything to work I am using this code now but it only restricts length Sub Change_Ticket_Initials() D...
more »

2017-01-16 14:01 (5) Answers

Passing an user form result to vba code variable

I have a code that counts the files in a folder if they contain a specific string on their name. For example: If I want it to count the files with close on their name (Close_26_03_2003.csv). Currently the code reads the value of a cell in the shee...
more »

2017-01-16 11:01 (1) Answers