excel vba 2010

Remove all special characters and punctuation from excel sheet

I have a workbook that has 4 worksheets all of which have dates on row 5, stating at column D. In the cell next to the date the person will put either 'ET','LT','EG','1ET', '2LT' or they will leave it blank. I am reading on how VBA works at the moment but I have just started so very new to this...

When I run a macro , macro has to open work book in specified folder and apply formula then save as text file.once compled goto the next file in the folder but when I run the macro its showing error "Run-time error '5': Invalid procedure call or argument" after first file is processed error in -...

Set rst = CreateObject("ADODB.Recordset") Set sourceRange = qt.ResultRange.SpecialCells(xlcellTypeVisible) arr = sourceRange.Value When the spreadsheet is not filtered arr (an array) is a two-dimensional range with number of rows and columns as expected. I use this arr to populate rst to put...

Hello, I built this piece of code which is intended to rename each worksheet in XYZ Workbook and split them into new workbooks. Indeed it is created a new workbook for each tab. However each of them is a full copy of the source workbook and not just of the sheet. What I'm missing here? Sub...

Hello, I have the following code that I use to send emails with specific sheets as attachments. It works fine but I need now to send sometimes multiple attachments. I can see that there are some topics about this, but I'm struggling to fit some answers on my code. Is there a way to make this...

I have a table where I need to find the elements present in different samples. For every sample, the no of iterations is a variable - I can have two rows of sample 1 and 3 rows of sample2 or 5 rows of sample4. the number of columns which are the elements can also be different. I have considered...

I have a table where I need to find the elements present in different samples. For every sample, the no of iterations is a variable - I can have two rows of sample 1 and 3 rows of sample2 or 5 rows of sample4. the number of columns which are the elements can also be different. I have considered...

Hi, I only have very limited VBA knowledge. Can you please look at the information I have and advise me. I am trying to have excel change the name of a spreadsheet via a messagebox. I copied the below in a module. The spreadsheet I am trying to rename is Template. thanks so much. Sharon Sheets...

I am trying to access a table that contains two words in the table name, my code works when there is only one name to the table. No matter what table naming convention it errors out. What am I doing wrong? here is my code: Sub OpenTable() Dim cn As Object Dim rs As Object Dim i...

Good Morning All: I am hoping someone will have an answer for me on my latest obstacle. I am trying to do a couple of different things: 1. I am running a simple Find Macro that takes the value (in this case, a reference number) from cell H7 on sheet1, and searches through a series of...

I have written a sub to look for columns that are missing according to a spec. If the column is missing insert a new column is according to spec. If the column exists but is in the wrong column move it to where it should be. The issue I am having is if the column exists but has to be moved it is...

Hello all, In colums A and B I have a series of dates.Then in column C I place the following function =(A1-B2)/362.25 This gets the age difference placed in colums C. I then drag down to fill in the rest of Col C to get the rest of the ages. Is there a way I could do this without having to...

Hi all, I'm a VBA newbie, I wrote the vba below and it works, but I loose the leading zeros as well as zeros after decimals whenever the string contains any numbers. I need to keep the zeros because they are ICD9 codes. I have hundreds of tables like this, so it is not feasible to use the...

Hi All I cannot get my head around this one. I need to copy everything in a worksheet after row 1 (as it contains headers) from one worksheet to another (say CSVin to CSVout). I only to copy rows from CSVin where two criteria are met: - Column 'A' starts with the letter 'C' (customer codes...

I have a leave calendar which is highlighting as per the start and end dates using conditional formatting formula =SUMPRODUCT((B3<=$I$19:$I$150)*(B3>=$E$19:$E$150))=1 I want to show name mentioned in cell in front of start date and end date instead of Green highlight in...

My code was unable to call BCD when user input valid txtWeekNo in userform. If txtWeekNo is invalid or empty, it will execute sub BCD. Below is my code and can i know what's wrong with my code? Thanks Option Explicit Dim LColumn As Integer, Acolumn As Integer, i As Integer, LRow As Integer, a...

I have a multiple elements they are as below ws.Cells(rw, 5) = .getElementsByTagName("tr").Item(2).innerText ws.Cells(rw, 5) = .getElementsByTagName("tr").Item(3).innerText ws.Cells(rw, 5) = .getElementsByTagName("tr").Item(4).innerText And so on... If in these elements if any line contains...

Hi, I'm trying to figure out how to temporarily disable my macro for like 5 seconds and then have it continue running the rest of the script. I tried using the OnTime function (shown below), but it caused the following error message to pop up "Cannot run the macro...

I am writing code to change the read the formatting of the cells in my worksheets. I got the code working using for loops but it takes too long running this way. So I am trying to rewrite the code using arrays . On the worksheet I am on now I have two tables that I have to loop through. Table 1...