Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Advanced Filter works manually but not in VBA?

Hello,

I have setup an advanced filter that is extremely temperamental, the problem seems to be that it ignores the criteria range when the code is read in VBA. The advanced filter code is stored in Module1 and the code is called by a command button on a worksheet. I can get the filter to recognise the criteria if I go through the advanced filter tool on the toolbar OR if I step through the exact same vba code using F8. However it doesn’t recognise the criteria and the advanced filter does not work properly if I call the code from the command button on the worksheet. I’ve read that this has happened before but I’m stumped as how to sort it out. I’m using excel 2007 in compability mode with 2003, and the exact same thing happens in 2003.

Has anyone got any ideas why this might be the case?


Post your answer or comment

comments powered by Disqus
Hello everyone,

I am relatively new to Macro, so rather from writing it, I use Macro recording.

After pressing the hot key (Crtl + Shift + P), the first advanced filter works.

It is based on A B C D columns and generates 4 new columns, F G H I, based on the criteria of E1 and E2.

The second filter does not work.

Second advanced filter is based on the unique of Column G and generate into Column K.

However, this filter only generates the header (the column title) but no data. When I do it manually, there are header as well as filtered data.

Does anyone catch any mistake in the code? I have researched online for a long time, but I still cannot figure out why it works manually but not in macro.

Thank you for the kind considreation,

Mucc

[Sub NEWFILTER()]
'
' NEWFILTER Macro
' Filter
'
' Keyboard Shortcut: Ctrl+Shift+P
'

Selection.AutoFilter
    Columns("A:D").AdvancedFilter Action:=xlFilterCopy, 
    CriteriaRange:=Range("E1:E2"), 
    CopyToRange:=Range("F1"), 
    Unique:=False
 
Selection.AutoFilter
    Range("G1:G10").AdvancedFilter Action:=xlFilterCopy, 
    CopyToRange:=Range( _"K1"), Unique:=True
 
  
End Sub


Background: An area containing data is manually hi-lited and designated for
copying into WorkSheet A (W/S-A). W/S-B is the intended destination.

Under the manual mode, the selected area is pasted into W/S-B without a hitch.

The paste process in W/S- B was carried out while a Macro was being recorded.

When the Macro was subsequently Run, it failed at Activesheet.Paste

Question: Why does the process work manually, but not from a Macro?

I have written a small macro to format raw data from an accounting system to
a useable format in excel. The accounting system stores dates as dd.mm.yyyy.
As Excel wont recognise this as a date, the code I have written to convert
the date columns is simply:
Columns("E:E").Replace What:=".", Replacement:="/"
Columns("E:E").NumberFormat = "dd/mm/yyyy;@"
However, any date that wouldn't make sense in an american format
(mm/dd/yyyy) stays stored as text until you click in the actual cell. All my
settings are up as English UK, why is this happening? I have even tried
adding tricks into the VBA code that work to convert these date fields
manually, but don't in the VBA code! An example is typing "1" into an empty
cell, then copying, and paste special multiply across all the date range.
Works manually, but not in VBA!
The line where the error starts is the 'replacement' line. If I do this
manually excel immediately recognises the entire column as dates. If I do
this with VBA code it doesn't. Any ideas?
Thanks.
--
JM

I have written a small macro to format raw data from an accounting system to
a useable format in excel. The accounting system stores dates as dd.mm.yyyy.
As Excel wont recognise this as a date, the code I have written to convert
the date columns is simply:
Columns("E:E").Replace What:=".", Replacement:="/"
Columns("E:E").NumberFormat = "dd/mm/yyyy;@"
However, any date that wouldn't make sense in an american format
(mm/dd/yyyy) stays stored as text until you click in the actual cell. All my
settings are up as English UK, why is this happening? I have even tried
adding tricks into the VBA code that work to convert these date fields
manually, but don't in the VBA code! An example is typing "1" into an empty
cell, then copying, and paste special multiply across all the date range.
Works manually, but not in VBA!
The line where the error starts is the 'replacement' line. If I do this
manually excel immediately recognises the entire column as dates. If I do
this with VBA code it doesn't. Any ideas?
Thanks.
--
JM

Hello All,
i am new to this Forum,
please help me with this problem
i am actually BOXI(Business Objects) from our tool i wrote VBA code to save all of our reports in the format of excel.

With xlsapp.Workbooks(1).ActiveSheet.PageSetup
.PrintHeadings = False
.PrintQuality = 600
.Orientation = xlLandscape
.Zoom = 75
End With

with the above code, i am trying to set to fit one page, it is alright in my system, but when i am viewing from other system the excel report is not fit into one page can any one have any idea

That above code is working in one system but not in another why?

Thanks in advance
regards
bujji

I need urgent help please.
Kindley thanking you in advance.

Hi below is the code that I was given by some years ago. It works perfectly in 2003 version of Excel but not in 2007.

As I am not too familiar with this code I need urgent help.
The end goal of the code (which is attached to a command button) is to check the current sheet for any cells that have green or red cell as a result of conditional formatting which is caused by an incorrect answer in the cell.

Ie: If any cells display red or green, the the cell eaither contain incorect data or no dat has been input into the cell.
When clicking the command button, the code is to check the sheet and if there are no red or green cells then open the next sheet for access.

Below is the code wich sits in a module.
The "FunctionGetStrippedValue" right down the bottom of the code seems to be the area where the debugging takes me to.

Function ActiveCondition(Rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
Dim Temp As Variant
Dim Temp2 As Variant

If Rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For Ndx = 1 To Rng.FormatConditions.Count
Set FC = Rng.FormatConditions(Ndx)
Select Case FC.Type
Case xlCellValue
Select Case FC.Operator
Case xlBetween
Temp = GetStrippedValue(FC.Formula1)
Temp2 = GetStrippedValue(FC.Formula2)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
CDbl(Rng.Value) = Temp And _
Rng.Value CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value > Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Temp = Rng.Value Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlGreaterEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value >= Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlLess
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Else
If Rng.Value < Temp Then
ActiveCondition = Ndx
Exit Function
End If
End If

Case xlLessEqual
Temp = GetStrippedValue(FC.Formula1)
If IsNumeric(Temp) Then
If CDbl(Rng.Value)

Please can anybody help?......and thanks in advance for looking.

I'm using the code below on a number input macro to check to see if th
number entered is the same as the validation code in hidden sheet1!A1

It works perfectly in Excel 2000 but a user reports that in Excel XP h
gets the message: "Compile error in module 2". When I looked at it i
appears that the problem is at the point of 'MyNum' in the 6th row.

I'm lost as to why it works in the earlier version but not in XP. Ca
anyone please suggest an answer?

Thanks!
-------------------------------------------------------------

Sub InputNumber()
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
myNum = InputBox("Please enter your 8 digit validation code. If yo
don't know it please contact XXXX", "Box Title")
If myNum = "" Then
Sheets("Sheet1").Visible = False
Sheets("Setup Sheet").Select
Application.ScreenUpdating = True
Exit Sub
End If
Range("A1").Value = myNum
Sheets("Sheet1").Visible = xlSheetVeryHidden

Sheets("Setup Sheet").Select
Application.ScreenUpdating = True
End Sub
------------------------------------------------------------

--
Message posted from http://www.ExcelForum.com

I have a textbox (activex) on a worksheet (Not a userform) and i want to copy the text to other cells.
The following code works just fine in excel 2010 but not in other excel programs like 2007.

De code is:

	VB:
	
 scopeopnemen2() 
    ActiveSheet.TextBox1.Activate 
    SendKeys "^A^C", True ' send knoppen combinatie en CTRL A vanuit VBA
    Range("t4").Select 
    ActiveSheet.Paste 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works in excel 2010 but not in others?
Please help.

Can anyone tell me why the following line of code works fine in Excel 2000 but not in '97?

Worksheets("resumen energías").Range("N3:BM4").ClearContents

Thanks

Hi everyone. I have an interesting problem.
I created a spreadsheet with groupings on certain rows.
I now want to delete the rows that belong to the grouping through code. To
identify the rows I want to delete I put a dummy comment on one of the cells
in the row and then run the following code to remove the rows:

For each wks in Application.ActiveWorkbook.Worksheets
wks.Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeComments).EntireRow.Delete
Next

Now it works for some sheets but not others.
I get the error message:
"Cannot use that command on overlapping selections"

Strangely, it works if I perform the steps through the XL interface.
(Edit+Go To+Special...+Comments and then choose Edit+Delete+Entire Row)

Any ideas would be very appreciated.

Thank you.

Dear Excel Gurus,

I have two lists in an excel file. I need to compare these two lists and pick the items which are present in the "List1 but not in "List2".

I attached a sample file with "List1" and "List2" and necessary "OutPut". Sample.xlsx Vba or formulas both are ok. Though I would prefer VBA.

Thanks in advance,

Regard,
Ravi.

Hi, I am new member of the forum and have been using excel macros here and there to come up with useful tools. Presently I am working upon a macro which would
1) Generate a template having 41 columns for end user to enter data
2) Generate a text file from the template in #1.

Template is working fine in 2003 but not in 2007 /2010. (Additionally, I have already worked upon the validation list and it's working in both versions). In 2007 / 2010 after column 31, Date fields are not getting column title, not giving the column width and also I have added the date format to yyyy/mm/dd (which is working fine in column 6, 18,23,25,27 and 29) is not working in and after 31.

I am completely clueless and have wasted couple of days on it. Please see if anyone can help here.
http://dl.dropbox.com/u/89672605/Tes.../TestMacro.xls

PS:
Please ignore the compile error on hidden modules.
Password: atlhawks

Validation Code which is working fine in first six columns.

	VB:
	
 _ 
Target.Column = 18 Or _ 
Target.Column = 23 Or _ 
Target.Column = 25 Or _ 
Target.Column = 27 Or _ 
Target.Column = 29 Or _ 
Target.Column = 33 Or _ 
Target.Column = 35 Or _ 
Target.Column = 37 Or _ 
Target.Column = 39 Or _ 
Target.Column = 41 Or _ 
Target.Column = 43 Then 
     
     '
    Target.Value = Format(ValidDate(Target), "yyyy/mm/dd") 
    If Target.Value = "1899/12/29" Or Target.Value = "1899/12/30" Then 
        Target.Value = Empty 
        MsgBox "Invalid date entry. Enter a date in yyyy/mm/dd format." 
         
        Target.Select 
    End If 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Formatting code which is not working after column 31 but worked earlier.


	VB:
	
.Range("AE" & CStr(wsVariables.headerRow)).Value = "Medical Option Eligibility Effective Date*" 
With Columns("AE:AE") 
    .ColumnWidth = 40 
    .HorizontalAlignment = xlCenter 
    .WrapText = True 
    .NumberFormat = "@" 
    With .Validation 
        .Delete 
        .Add Type:=0 ' skip
        .IgnoreBlank = True 
        .InputTitle = "Eligibility Effective Date" 
        .InputMessage = "Enter a date in yyyy/mm/dd format." 
        .ShowInput = True 
        .ShowError = False 
    End With 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I wrote two examples, a sub and a function.

Sub demo()
Dim table As Range
Set table = Sheets(1).Range("a1").CurrentRegion
NumberOfRows = table.Rows.Count
End Sub

Public Function fDemo(vnos As String) As Integer
Dim table As Range
Set table = Sheets(1).Range("a1").CurrentRegion
NumberOfRows = table.Rows.Count
fDemo = NumberOfRows
End Function

The same code works in sub but not in function. Anybody knows why?

When I’m using advanced filtering the criteria will not work with a formula.

This will not work in the cell
=”=1”

However, if I format the cell as text and enter =1 it works.

Any ideas?

Thanks

David

Cells(j, 1) = DateValue(Cells(j, 1)) + TimeValue(Cells(j, 1))

this is the line, and the error message is "type mismatch" ... sam
code works in excel 2002, but not in 2003.

The date is in format: 01-Dec-02 10:18:59 AM

please help.... it must be a classic problem..

--
Message posted from http://www.ExcelForum.com

I'm trying to create charts in Excel from a bunch of non-contiguous cells
using VBA.

The cells are Sheet1!$C1:C2,Sheet1!$C4:C5,Sheet1!$C6:C7,Sheet1!$ C9:C10, etc.

I first tried to set Series.Formula with a string that I generated from the
sheet & cell names, but I had enough cells that I hit that 250 character
limit in the Series.Formula arguments.

I tried a number of other tactics, including:
Trying SeriesCollection.Extend (didn't work, since I'm trying to put
data from >1 sheet into the chart)
Trying to just set Series.Values & Series.XValues separately
Applying a name to the renge I wanted to plot, & feeding that into .
Formula, then .Values
(I can't remember what else)

After all this, I found out something absolutely maddening

So maddening indeed that I may be without teeth & hair by the end of the day.

I can:
1 select the series manually with the mouse, and select the "Source
Data" context menu
2 type "=Sheet!NamedRange" into the Values field & hit OK
3 see that the plot has the right data
4 manually select the series again & copy the text of the series
fomula from the formula bar
5 paste said text into VBA code that sets Series.Formula
6 FLIP MY FREAKIN LID BECAUSE THE CODE DOESN'T WORK!!!!

Richo.Breathe.Value = xlSlowly
Richo.Wait(10)

Anyway, does anyone know why this doesn't work? Am I doing something wrong,
or do multi-area ranges somehow work in Excel, but not in VBA?

Obviously I can always make a new sheet specifically for the chart data, but
that somehow seems inelegant...

Any help / kind words are appreciated,

Richo

I've attached my full code, basically, i've got a workbook with a "*" in the
A column if i need the line copied, then there are dates in another column
and the row has to be moved to the tab for the month the date falls in. i
can copy the whole row and paste it not problem, but i would like to copy
everying except the "*" in the A column. i've got code that works
seperately, but it won't work in the sub...can someone please give me some
idea as to what the hell i'm doing wrong???
-------------------------
Private Sub CommandButton1_Click()
Dim monthnum As Integer, counter As Integer, anniversary As Date, subrow
As Variant, sheetname As String
subrow = Array(6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6)
For counter = 1 To 999
If Worksheets("2005").Cells(counter, 1).Value = "*" Then
If Worksheets("2005").Cells(counter, 4).Value = "" Then
anniversary = Worksheets("2005").Cells(counter, 3).Value
Else
anniversary = Worksheets("2005").Cells(counter, 4).Value
End If
monthnum = Month(anniversary)
Select Case monthnum
Case 1
sheetname = "Jan"
subrow(1) = subrow(1) + 1
Case 2
sheetname = "Feb"
subrow(2) = subrow(2) + 1
Case 3
sheetname = "Mar"
subrow(3) = subrow(3) + 1
Case 4
sheetname = "Apr"
subrow(4) = subrow(4) + 1
Case 5
sheetname = "May"
subrow(5) = subrow(5) + 1
Case 6
sheetname = "Jun"
subrow(6) = subrow(6) + 1
Case 7
sheetname = "Jul"
subrow(7) = subrow(7) + 1
Case 8
sheetname = "Aug"
subrow(8) = subrow(8) + 1
Case 9
sheetname = "Sep"
subrow(9) = subrow(9) + 1
Case 10
sheetname = "Oct"
subrow(10) = subrow(10) + 1
Case 11
sheetname = "Nov"
subrow(11) = subrow(11) + 1
Case 12
sheetname = "Dec"
subrow(12) = subrow(12) + 1
End Select

'I'VE TRIED 3 DIFFERENT VERSIONS OF HOW TO DO THIS, NEITHER
WORKS, DON'T KNOW WHY THOUGH...

'#1
Sheets("2005").Select
Range("B7:AA7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("JAN").Select
Range("A7").Select
ActiveSheet.Paste

'#2 THIS IS THE SAME AS #3, EXCEPT WITH NO VARIABLES
'Sheets("2005").Select
'Range(Cells(7, 2), Cells(7, 27)).Select
'Selection.Copy
'Sheets(sheetname).Select
'Worksheets(sheetname).Range(Cells(7, 2), Cells(7, 27)).Select
'ActiveSheet.Paste

'#3 - THIS IS THE SAME AS #2, BUT WITH VARIABLES
'Worksheets("2005").Range(Cells(counter, 2), Cells(counter,
27)).Copy
'Worksheets(sheetname).Select
'Worksheets(sheetname).Range(Cells(columnto, 1),
Cells(columto, 26)).Select
'Worksheets(sheetname).Paste

'THIS ONE WORKS
Worksheets("2005").Rows(counter).Copy
Worksheets(sheetname).Select
Worksheets(sheetname).Rows(subrow(monthnum)).Select
Worksheets(sheetname).Paste
ElseIf Worksheets("2005").Cells(counter, 2).Value = "Grand Total" Then
Exit For
End If
Next counter
End Sub

I'm trying to create charts in Excel from a bunch of non-contiguous cells
using VBA.

The cells are Sheet1!$C1:C2,Sheet1!$C4:C5,Sheet1!$C6:C7,Sheet1!$C9:C10, etc.

I first tried to set Series.Formula with a string that I generated from the
sheet & cell names, but I had enough cells that I hit that 250 character
limit in the Series.Formula arguments.

I tried a number of other tactics, including:
Trying SeriesCollection.Extend (didn't work, since I'm trying to put
data from >1 sheet into the chart)
Trying to just set Series.Values & Series.XValues separately
Applying a name to the renge I wanted to plot, & feeding that into .
Formula, then .Values
(I can't remember what else)

After all this, I found out something absolutely maddening

So maddening indeed that I may be without teeth & hair by the end of the day.

I can:
1 select the series manually with the mouse, and select the "Source
Data" context menu
2 type "=Sheet!NamedRange" into the Values field & hit OK
3 see that the plot has the right data
4 manually select the series again & copy the text of the series
fomula from the formula bar
5 paste said text into VBA code that sets Series.Formula
6 FLIP MY FREAKIN LID BECAUSE THE CODE DOESN'T WORK!!!!

Richo.Breathe.Value = xlSlowly
Richo.Wait(10)

Anyway, does anyone know why this doesn't work? Am I doing something wrong,
or do multi-area ranges somehow work in Excel, but not in VBA?

Obviously I can always make a new sheet specifically for the chart data, but
that somehow seems inelegant...

Any help / kind words are appreciated,

Richo

Hi all,

I am using VBA to expand a named range to include an extra column.

I have set up a test scenario in which you enter some of the numbers from 1 to 10 into cells A1, A2, A3 etc and name that range TestName. The following code then correctly expands the range and adds the next number in the series, for all values up to 10:

Code:
Sub Test()
' if the value is not in the range, add a new column, add the value and update the named range
Dim Rng As Range
Dim i As Integer, j As Integer

    For i = 1 To 10
        Set Rng = [TempName].Find(What:=i, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns)
        If Not Rng Is Nothing Then
            j = Rng.Column
        Else
            j = [TempName].Range("A1").Column + [TempName].Columns.Count
            Columns(j).Insert shift:=xlRight
            Names("TempName").RefersToLocal = "=Sheet1!" & [TempName].Resize(1, [TempName].Columns.Count + 1).Address
            Cells(1, j) = i
        End If
    Next i
End Sub
You can see that it is working by stepping through one iteration at a time, or by highlighting a cell off to the right of the range - the highlighted cell will move as the new columns are added.

I have only one problem. The above will not work if I omit "Sheet1" in the RefersToLocal section towards the end.

For many reasons, in the spreadsheet I'm actually applying this to I need to have the name defined across multiple spreadsheets, which you can do by writing the name exactly as you normally would, but without the Sheet1 bit at the front.

Why can I define a name in Excel without a sheet name included, but not in VBA??

Regards,

Alex Gould.

Excel Macro issues in Office 2007 Professional but not in Ultimate????

My customer is upgrading from Office 2003 to Office 2007 Profffesional. They have an very old Excel Spreadsheet (.Xls) and a sepaerate macro workbook (.xlm) and a Add-In (.xla) "solution" that runs fine in Office 2003 but will not run under office 2007 Professional. Before you jump to "security" and the "trust stuff" read on.
The .xla (add-in) has a macro for adding menus to the Excel toolbar, and macro for the menu to launch the .XLS and .XLM together . The .XLS file refers to the macros in the XLM file. I was new to all of this so I spent a few hours in Office 2003 learning how all this macro stuff worked. Then I returned to the the customers Office 2007 Excel Pro PC and experienced all the "macro failures". Yes, all the "trust" settings are correct on the customers PC.
I decided to install Excel 2007 on my dev PC. I opened our MSDN DVD collection and only had an Office 2007 Ultimate DVD (not professional). So I loaded that up, copied the 3 files to my PC, set the "trust" settings on my PC, and hey, everything works just fine! So I uninstalled Ultimate version, downloaded and instaled the Pro version and SP2 and the macro probelm has returned.
What gives? This old maco solution runs OK on Office 95, Office 2003 and Office 2007 Utlimate but NOT on Office 2007 Pro?????
HELP!

The following code runs in 2000 but not in 2003, and seems to be having trouble with the .value(ps1,ps2) part.

What is it that I am overlooking or do not know?

Sub door1(ByVal r As String)
Dim nRow As Integer, nCol As Integer, ps1 As Integer, ps2 As Integer
nRow = Range(r$).Rows.Count
nCol = Range(r$).Columns.Count
Dim DoorOrderData()
Dim rng As Range
Set rng = Range(r$)
ReDim DoorOrderData(rng.Rows.Count, rng.Columns.Count)
With rng
For ps1 = 1 To nRow
For ps2 = 1 To nCol
DoorOrderData(ps1, ps2) = (.Value(ps1, ps2))
Next
Next
End With
Call combineLikeCabinets(r$, DoorOrderData, rng.Rows.Count, rng.Columns.Count)
End Sub

Any help would be deeply appreciated.
Thanks
G

To anyone who can help me: Occasionally I receive an Excel attachment with an
e-mail were some of the text in some cells appear in the formula bar but not
in the cell itself. Would greatly appreciate assitance . . .

Hi,

I am trying to migrate a model that I built in Excel 2002 to Excel 2007. The model has an advanced filter in it to get unique values. However in 2002 the filter worked correctly but in 2007 it produces nothing. The code excerpt is below :-

Worksheets("Transaction Data").Range("PCT_List").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Transaction Data").Range("criteria"), _
CopyToRange:=Worksheets("Transaction Data").Range("Unique_PCT_List"), Unique:=True

Any ideas ? Thanks

Kaps

Hello,

I need to copy a subsection of data to another sheet with advanced filter using VBA. And then overwrite with a new subsection everytime the macro is run.

I have data with a date field and I want to only pick-out rows within a given date range specified as >=04/10/11


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