Free Microsoft Excel 2013 Quick Reference

Vba code doesnt work Results

So I understand that you can enter in a Custom List in Excel to be used with the autofill feature. I have entered a custom list of roman numerals ("i." and "ii." and "iii." and "ix.") and so on.

Using the autofill - draging the handle - it works perfectly. or doing it using vba also works by using (or similar to):

Now I also understand that you can use the this bit of code to select only the visible cells within a range selection that you have selected:

Now if I use this code with numbers such as 1,2,3 and select a range with hidden rows, it works just fine.
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Trend:=False
But I want to take it one step further and use my custom list.
If I change 1 to "i." - you would think it would work since using autofill manually works - but it doesnt work.

Any ideas?

I want to make a button in my excel sheet, and i write the macro in the microsoft excel objects, Sheet1, where the button is. That doesnt work, but if i write it in Module1, it works.

Why doesnt it work when written in Sheet1. I know this is a very fundamental question which i propably should have been able to find an answer to, but i have now tried, and cant find that information. I'm sorry.

I want several buttons in my "sheet1", and to me it makes sense that i should write the macros for all the buttons which is placed in sheet1, in the corresponding microsoft excel objects.

As you may have figured out, i'm not completely familiar with the different purposes of excel objects and modules.

I have done some VBA coding before, but thats a long time ago now, and i need help to get started.


I have multiple Userforms with labels etc, all controls are similarily named (so I could do this) and I can refer to the Userform dynamically but the Label caption/Controls don't change, any idea's how to get them working.

I have zero experience with VBA or any programming for that matter.

this is the Code I use which works but the captions don't change
Thanks in advance for any help.

Public Sub UpdateLabelCaption()
Dim oUserForm As Object

    Set oUserForm = UserForms.Add(FormName)
    FormName = "Userform2"

    oUserForm.Label1.Caption ="Whatever"

End Sub

The code below looks for a repeated row in the first column, and it inserts a new color row before the next row value. eg:
insert color row
insert color row
insert color row
Sub insertRowColor()
Dim r As Long
With ActiveWorkbook.ActiveSheet
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(r, 1).Value <> Cells(r - 1, 1).Value Then
       rowselect = r & ":" & r
       Selection.Interior.ColorIndex = 5
    End If
Next r
End With
End Sub
Now instead of using the first column as the search, i would like it to look for a text in the first row eg. "some text", take that column number, and then work through that column by looking for a repeated rows, eg:

columnA  columnB    SomeText   
2                              1
3                              1
4                              1
insert color row
3                              4
insert color row
1                              2
2                              2
insert color row

Sub insertRowColor()
Dim r As Long
Dim c As Range
With ActiveWorkbook.ActiveSheet
If r.Value = "SomeText"
For r = Cells(Rows.Count, c).End(xlUp).Row To 2 Step -1
    If Cells(r, c).Value <> Cells(r - 1, c).Value Then
       rowselect = r & ":" & r
       Selection.Interior.ColorIndex = 5
    End If
Next r
end if
End With
End Sub
what i have doesnt see to work, any ideas? thanks


Please can someone help me.

I am a little stuck on selecting radio buttons on a website in VBA.
The source code reads.
<INPUT TYPE=RADIO NAME="timeradio" VALUE="2"> 
I need to check the 3rd radio button and i cannot get my head around it.
This is what I have so far and it doesnt work.

Public IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Document.all.Item("timeradio")(3).Checked = True
Please Please Can anyone help.


I have created an application that exports the name, address, postal code
from another program into excel. I need to remove duplicate records using
vba so the user doesnt have to remove duplicates manually. I have tried
using the Advanced Filter macro:

Range("B1:B10").AdvancedFilter xlFilterInPlace, unique:=True1"), Unique:=True

This code works fine if I know each time how many records there are, but as
my application is dynamic and the number of records is always changing this
code doesnt do exactly what I want it to. Any suggestions. Thanks.

I have a spreadsheet that needs to be sent to multiple people. I have created a button that when clicked will send it to all these people. The only problem is that I have used code where I am using a loop that iterates after it has sent to one person. The file i am sending is 6MB and this takes a while for each person. I want it so it sends to all people at once, without going through a loop. This can be done in email programs by using a comma to seperate each address e.g.,, This doesnt work in vba though. The mail client is lotus notes.

Any help would be great,



Good Afternoon,

I am hoping to get a resolution to my current predicament.

To set the scene;

1- Daily i receive serveral hundred emails regard marketing campaign statuses
2- They are moved to a folder called TST
3 - I have some vba to retrieve those emails into excel, sort them into failed or passed campaigns and then provide some reporting.
4 - I then want to move all the emails from tst into a processed folder

I am currently up to point 4 and have some code in place and it works....well sort of.

My code only seems to move a half sometimes not event that off the emails in the tst folder.

Here is my code :

Dim olApp As Outlook.Application
Dim olNamespace As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim olMail As Outlook.MailItem
Dim lngRow As Long
Dim DFolder As Outlook.MAPIFolder

Set olApp = New Outlook.Application
Set olNamespace = olApp.GetNamespace("MAPI")
Set olFolder = olNamespace.Folders("")
Set olFolder = olFolder.Folders("Inbox")
Set DFolder = olFolder.Folders("Camp_Processed")
Set olFolder = olFolder.Folders("tst")

lngRow = 2

For Each olMail In olFolder.Items
With ActiveSheet
.Cells(lngRow, 1) = olMail.SenderName
.Cells(lngRow, 2) = olMail.Subject
.Cells(lngRow, 3) = olMail.Body
lngRow = lngRow + 1
End With


For Each olMail In olFolder.Items
olMail.Move DFolder

Set olMail = Nothing
Set olFolder = Nothing
Set olNamespace = Nothing
Set olApp = Nothing

Any help,guidance etc would be greatly appreciated.

Thanks in advance.


Hey all! I have a situation that I really need fixed! (Maybe another way around 2 situations)

I have a workbook that gets info from Access. Since Access connected to MySQL via linked tables doesnt work if your offline. I have another excel workbook getting data from MYSQL and Access getting the data from Excel (b/c excel does store data after being offline)

My situation is that I need to put a password on the excel file. When I do Access will not open it! So I have tried opening the workbook with vba and then opening my access and collecting the data.

The problem is that I get an error when running this code!

Dim x as new excel.application
Dim w as excel.workbook

With w "Filename:="MYFile", Password:="My password"


x.activeworkbook.close false
End With
It still asks me for a password

and when I add the

It gives me the error of

Run-time error '1004':
"Method 'Open' of object 'Workbooks' failed

Hi guys
I am dealing with survey data at the moment and i am interested in extracting data of 5 identical columns from 3 excel files and then aggregate them into one excel file.

below is the code im working on, fairly new to VBA so my apologies if my code doesnt make sense to you.

Sub extraction()

Dim a As Range
Dim r As Range
Dim wbSource As Workbook
Dim wbDestination As Workbook

Dim cumrow As Long

Set wbSource = Workbooks.Open("C:Data extraction for ElieenIVS_Trips.xlsx")
Set wbDestination = Workbooks.Open("C:Data extraction for Elieen1.xlsx")

cumrow = 0

lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Cells(Rows.Count, lastcol).End(xlUp).Row
Set r = Cells("AH1:AQ1").Resize(Rows.Count) ' This is your column

' Copy it to appropriate location on destination sheet
wbDestination.Sheets(1).Cells("AH1:AQ1").Resize(Rows.Count) = a
wbDestination.Sheets(1).Cells("AH1:AQ1").Resize(Rows.Count) = r
cumrow = cumrow + lastrow

End Sub

thanks in advance for your help.

Kind regards

I have a macro which i use to paste data from excel to powerpoint. The macro itself works and pastes data but it doesnt paste the full range area.

The range area is D17:V81. However only D17:Q81 is pasted into Powerpoint.

I have tried making the range area being copied bigger but it didnt help. Any ideas. Code pasted below

Sub PowerpointPL()

Dim RegCounter As Integer
Dim CountryCounter As Variant
Dim pp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim RegName As String
Dim CountryName As String

Dim PL_top As Integer
Dim PL_left As Integer
Dim Pl_width As Integer

' set P&L Locations

PL_top = 60
PL_left = 40
Pl_width = 500

'Application.ScreenUpdating = False

' Create Powerpoint Object

Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True
Dim slidecount As Integer

RegCounter = 1

' Outer loop to run through all regions
CountryCounter = 1

For Each CountryCounter In ThisWorkbook.Names("l_entity2").RefersToRange.Value

ThisWorkbook.Names("vba_CountryNo").RefersToRange.Value = CountryCounter
CountryName = CountryCounter

' Copy Country P&L as a picture

ThisWorkbook.Names("vba_CountryPL").RefersToRange.CopyPicture _
Appearance:=xlScreen, Format:=xlPicture

ThisWorkbook.Names("vba_CountryPL").RefersToRange.CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
slidecount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(slidecount + 1, 12)

pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pp.ActiveWindow.Selection.ShapeRange.Top = PL_top
pp.ActiveWindow.Selection.ShapeRange.Left = PL_left
pp.ActiveWindow.Selection.ShapeRange.Width = Pl_width
pp.ActiveWindow.Selection.SlideRange.Shapes.AddTextbox(msoTextOrientationHorizontal, 31.125, 20.5, 646.375, 28.875).Select
pp.ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=1, Length:=0).Select
With pp.ActiveWindow.Selection.TextRange
.Text = CountryName & " P&L"
With .Font
.Name = "Arial"
.Size = 24
.Bold = msoTrue
End With
End With

Next CountryCounter

Application.ScreenUpdating = True

End Sub

Please reference this thread solved previously :

I have added one sheet to my workbook as a summary since my last thread, and am trying to adapt the code you have already provided me with no luck. So I have gone with a very basic format.
The sheet is named "Team Member Dashboard"

I am running into one issue, in getting the test if Text function to work. As this sheet doesnt have the same basic structure as the others, and 8 different criteria and matching colouring tests, it all went haywire ....

Below is one section of the code, and I am just changing the ranges and repeating the process all the way down the sheet. It still wont change the text cells to white on black though? The entire workbook still works perfectly apart from this one sheet that I added, any advice would be greatly appreciated?

I tried the cell.type test, but it errored saying "not accepted."

The sheet i have added is the Team Member Dashboard, and each of the rows in the table has the relevant colour coding in rows 29:42.

Private Sub
Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range, Cell As Range
Set MyRange = Range("b2:m2")
ActiveSheet.Unprotect Password:="Virgin11"
For Each Cell In MyRange
        If Cell.Value <= [i29].Value Then
            Cell.Interior.ColorIndex = 3
        End If
        If Cell.Value >= [f29].Value Then
            Cell.Interior.ColorIndex = 45
        End If
        If Cell.Value >= [d29].Value Then
            Cell.Interior.ColorIndex = 27
        End If
        If Cell.Value >= [b29].Value Then
            Cell.Interior.ColorIndex = 4
        End If
        If Cell.Value = "" Then
            Cell.Interior.ColorIndex = 2
        End If
        If Cell.Value = vbString Then
              Cell.Interior.ColorIndex = 1
              Cell.Font.ColorIndex = 2
        End If

Hi all,

Basically I am recording a macro to cut & paste several columns of text into
the same sheet at the next available FREE column.

However whilst this works fine, every time I click the macro from the button I
have assigned, it overwrites the previous data i.e. it doesnt assign the
columns to the next blank available column.

I am presuming I would have to set up some form of array & maybe use offset

This is an example piece of code that I have just typed (but the principle is the same for my code):
 Sub CopyColumn()

    Application.CutCopyMode = False
End Sub

So I want to copy all formulas & cell formating but no actual cell values.

I am new to VBA so dont really know how to achieve anything beyond basic code.

Can someone perhaps point me in the right direction?

Thanks in advance.


I am building a benchmarking tool which allows the user to input values, and benchmark the performance against the existing values.

The values inputted are a range and can be from 10% to 20% to 0% to 100%.

The results are plotted on a chart.

My vba code currently displays out all the values in between the upper and lower ranges, so the range can vary. This is why the chart needs to be plotted via vba.

My problem is this:

The original values may begin at 20% and the new values begin at 40%, however when the results are plotted, both series will be plotted at the start of whatever series was plotted first, rather than at the correct position.

So in this example, I would want the first point on series 1 plotted at 20% and the first point on series 2 to be plotted at 40%, but at the moment they are both at 20%.

I have tried to give them unique x values via the .XValues property, but this doesnt seem to work...

Any thoughts?

hi all

Can someone point me to good resource to understand how Excel VBA works...... also when I open a spreadsheet it doesnt show any worksheet tabs.... yes its in excel 2003 and its coded for normal users ie hidden and menu style buttons

cheers G

Hi all,

I have the following simple vba code in order to open/import 1 txt file at one time HTML Code: 
Sub Macro2()
    Workbooks.OpenText Filename:="documentsQase69.txt", Origin:=xlMSDOS, StartRow:= _
        5, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=True, Comma:=True _
        , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
End Sub
which is what I want to do by selecting a location instead of running the code for each and every file. Please, note that I do not have a specific number of txt files. They can be 30 or 300.

I am looking to find a code in order to tweak it according to the above, so I found the following one:

HTML Code: 
Sub marble()
Dim z  As Long, e As Long
Dim f As String, g As String
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "documents"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.txt")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
g = Mid(Sheets("Sheet1").Cells(e, 1), 1, 31)
ActiveSheet.Name = g
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Sheets("Sheet1").Cells(1, 2) & Sheets("Sheet1").Cells(e, 1), _
        .Name = "deep"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 5
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
Next e
MsgBox "collating is complete."
End Sub
which unfortunately doesnt do what I can get with the 1st code.

In general, for a same procedure (which works well) but for .xls files I have this code:

HTML Code: 
MyPath = "documents" ' This is a default location
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "*.xls", vbNormal)
    If Len(strFilename) = 0 Then Exit Sub
' The code instructs to open each and every workbook within the aforementioned location
    Do Until strFilename = ""
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "" & strFilename)
            Set wsSrc = wbSrc.Worksheets(1)
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

            wbSrc.Close False
        strFilename = Dir()
Is there any way to fix the first code working in the same way as the above works? I am looking around, I havent found anything which could do the above one with txt - working as the first code.

Lastly, I found this code:

HTML Code: 
Sub LoadPipeDelimitedFiles()
    Dim idx As Integer
    Dim fpath As String
    Dim fname As String

    idx = 0
    fpath = "documents"
    fname = Dir(fpath & "*.txt")
    While (Len(fname) > 0)
 ' In the following piece of code, I get error

        idx = idx + 1
        Sheets.Add  ' Here it shows an error!
       Sheets("Sheet" & idx).Select

' In the above piece of code, I get error

        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & fpath & fname, Destination:=Range("A1"))
            .Name = "a" & idx
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileOtherDelimiter = "|"
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            fname = Dir
        End With
End Sub
but still doesnt work for all the txt files. It stops after the first one or two and returns error - probably it is an issue of renaming but still I cant get it work

I'm new in VBA world so I have easy noob questions.

I was looking to trim all cells in a ws quickly.. (without looping the 65000+ cells with a "for each in"...). there is no formulas any cells at this point.

And I found an interresting post;

Originally Posted by shg You code is entering an R1C1 formula without telling Excel to set the FormulaR1C1 property.

It's also assuming that the used range always starts in row 1, which may not be true.

Here's an option, assuming there are no formulas on the sheet:
Sub x()
    With ActiveSheet.UsedRange
        .Value = Evaluate("if(row(" & .Address & "), trim(" & .Address & "))")
    End With
End Sub
Fisrt, it totally works for my needs but I'm the kind of guy who wants to understand what im copy pasting...


Why does the if(row(" & .Address & ") affect the .Address in the trim()?
So the code below just copy the trimmed value of the first range cell into every cells...
Sub x()
    With ActiveSheet.UsedRange
        .Value = Evaluate("trim(" & .Address & ")")
    End With
End Sub

Range().Value should returns a string right? So why the this doesnt work?

Sub x()
    With ActiveSheet.UsedRange
        .Value = Trim(.Value)
    End With
End Sub
My point is that I will need to do various types of .Value manipulations within a defined Range further in the macro...
So what would be the best way? Is the evaluate() with if() and my string manipulator the only easy way?

Thanks a lot

I am trying to auto sum or sum the totals for Hours, QTY and Amount
in the row below the data. i already have the code to insert a new row on date change, my next step is to place "Daily Total" in Range("A") of the inserted Row, and then sum/autosum the other columns with values.

Data example is as follows

Date Campaign Outcome Rate Hours QTY Amount
22/09/2011, #####, Activity, 20.00, 5 , , 100.00
22/09/2011, #####, Collection, 0.50, , 1, 0.50

23/09/2011, #####, Activity, 19.00, 1 , , 19.00

24/09/2011, #####, Activity, 20.00, 2 , 40.00
24/09/2011, #####, Collection, 0.50, , 2, 1.00
24/09/2011, #####, Collection2, 1.00, , 4, 4.00

appologies for the spacing of the data, unsure how to post data correctly.. as paste doesnt seem to work.. i have seperated columns using comma's if it helps.

The code i have so far...

Dim lastRow, chkRw As Integer
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

For chkRw = lastRow To 4 Step -1
    If Range("A" & chkRw) <> Range("A" & chkRw + 1) Then
       Range("A" & chkRw + 1).EntireRow.Insert shift:=xlDown
    End If

Any help would be greatly appreciated

Hi guys,
I want to start by saying thanks for an awesome forum with tons of useful info. I am new to using VBA and userforms and I have found this site invaluable. Ok now to my question.

Some Background
I am making an index for the controlled documents of our department at work. I made a userform to automate the process and help eliminate the confusion from a document being editable by 10+ people. The index consists of four sheets, the first is a "home page" explaining the use and with a couple buttons for navigation. The second is the actual database or index of the controlled documents. Its columns consist of document number, revision number, category (I will explain this further), Description, Date of last revision, Author, and a spot to hyperlink the file. The category consists of ten options which correspond to the 4 digit document number ( ie. 0-999 corresponds to category 1, 1000-1999 correspond to category 2 etc.)

What I want to happen
So what I want to happen is when the user selects a category from the top combobox in the userform it triggers code that finds the next available document number in that category. Here is an example;

There is currently 26 documents in category 4 (so the highest document number in that category is 4026)

When the user selects category 4 from the top combobox I want the userform to display 4027 in the document number ( same thing as MEP) txtbox or label.

That brings me to my next question, what would be more appropriate a editable txtbox or a label. (it doesnt need to be editable)

Below is my userform and the colored stuff is where Im not sure which box would be better to use.

What I have tried so far

So far I have messed around with the WorksheetFunction Max but have no success because I dont know what syntax to use or how to limit it to a certain range of numbers. I think it could be done using some if...then statements but I hope there is a more efficient way. Please let me know if you have any ideas and if you could write up some example code It would be very helpful. For reference the document numbers are stored in column A of sheet "Document Index" or sheet2. Like I said before I am a terrible code writer and have not used VB since an intro class several years ago so I am basically starting from scratch and re-learning.

Thanks in advance for any insight provided.


Hello everyone,

Looking at Mr. Ron's VBA script examples (, I modified one accordingly for one of my office work.
I am a recruiter for a company and maintain a database of all candidates in excel. Every time a candidate gets accepted or rejected, I have to send him an e-mail of acceptance or rejection. To avoid the repetitive task of manually logging in and typing the text, I thought of making my life easy by working on excel macros.

I would be very thankful if someone may have a look at the spreadsheet I created. I would like you to modify the code a bit if you can spare some time for me in the following manner.

1)I created a list box in column "I" for status. If you select Accept, it sends an acceptance e-mail which is shown in column J along with a time-stamp in column K.
Same thing happens if you select Reject in Status (I column).

The first option in the drop down menu is a BLANK value, incase I do not want to send any emails to particular candidates.

Now what I would is if I add more rows for candidates and want to send an email to only those, how can that be done?
In the current code, it is resending all candidates again.

Is it possible to add a dialogue box which would ask the user :- Would you like to resend "acceptance" or "rejection" email [depending on what you selected in column J] to [persons name] from column A?"

If selected "yes" then resend the email, if selected "no" then do not do anything and proceed to the next row. It would be great to have an additional button of "NO TO ALL" along with "Yes: or "No".

Suppose I had earlier sent emails to 30 candidates and added only a few candidates at the end of the sheet. Now if I only want to send email to those new ones, if we run the code, I do not want to click 30 times on "No" button when I get a pop up which says " Would you like to resend " acceptance" or rejection email to "XYZ" candidate"?

Rather there should be a button of "No to all" by which it sends email only to the newly added candidates and doesnt ask me to resend email to the above already sent candidates.

I would be very thankful if you can help me.

Best regards,


No luck finding an answer? You could always try Google.