excel-vba,excel-2007,pivot-table , VBA - test if a value is a valid selection for a PivotField

## Question:

Tag: excel-vba,excel-2007,pivot-table

For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C

Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A"

Occasionally, and let's say randomonly for the purposes of this question, A, B or C will not be a valid selection for filterfield. When VBA attempts to change the field, it throws a run-time error. I want to avoid this.

How can I check if my values are valid for filterfield before I run the code above? I would like to avoid using On Error and VBA does not have try/catch functionality..

You can iterate through the PivotItems and check the Name against your test.

Sub CheckIfPivotFieldContainsItem()

Dim pt As PivotTable
Set pt = Sheet1.PivotTables(1)

Dim test_val As Variant
test_val = "59"

Dim pivot_item As PivotItem
For Each pivot_item In pt.PivotFields("C").PivotItems
If pivot_item.Name = test_val Then
Debug.Print "MATCHES"
End If
Next pi

End Sub


Relevant data shows that a match should exist and indeed it returns MATCHES.

# Related:

## Match Function in Specific Column Excel VBA

excel,vba,excel-vba,match,worksheet-function
I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration. Say that the number of the column is given by colnumvar. The...

## Extract All Named Ranges Into A Class

vba,excel-vba
I have a workbook with a very large amount of named ranges (well over 200). I really need a way to work quickly and easily with all of the named ranges so I can then work with / populate them using VBA. My solution up until now has been to...

## Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic [duplicate]

excel,vba,excel-vba
This question already has an answer here: Detect whether Excel workbook is already open (using VBA) [closed] 7 answers I'm having a problem creating a condition. Please see pseudo code below. thanks in advance Check if File A.xls is open If File A.xls is Open Close File A.xls Else...

## VBA how to initialize vCPath

vba,excel-vba,excel-2010
How do I initialize vCPath? VBA Run-Time Error 1004 @Garry's Student says I "must somehow use the info you get from Application.FileDialog(msoFileDialogOpen) to generate the full filespec of the file you wish to open". What's the simplest way to do this? I am a VBA beginner: I have been programming...

## Using a stored integer as a cell reference

excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

## Referencing a new inserted column Excel VBA

excel,vba,excel-vba
I am trying to reference a cell in the below formulaes. 'AUA Summary'!$D$9 . Each time the macro runs a new column D is inserted. The Problem: When the column is inserted my reference moves to ** 'AUA Summary'!$E$9. How do I get to reference 'AUA Summary'!$D$9 even if a...

## Retaining original table formatting after a 'pagebreak'

excel,excel-vba,table,formatting,page-break
So here's the finished product, a statement of accounts with a working statement table, and an ageing analysis: Everything works great. It basically populates itself row by row with data from another table. Here is the sample code: j = 21 'First row on the statement of accounts workbook For...

## adding variables into another variable vba

excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

## Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format
I am in process of creating a macro that will save the current workbook, create a new outlook message and attach the file to the message. My macro does that but I can not format the text in the body of the email to my liking. Dim OutApp As Object...

## Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

## Range.offset propety

excel-vba
I'm trying to understand a code on VBA, to debug the VBA program of someone. So I want to know that is the property and the meaning of Range (Value).offset(x;y). Here is my code: `Sub initialise() Dim i As Double, j As Double For i = 0 To maxnodes -...

## Best way to return data from multiple columns into one row?

excel,vba,excel-vba
I have a sheet with just order numbers and another with order numbers and all of the data associated with those order numbers. I want to match the order numbers and transfer all of the available data into the other sheet. I've been trying to use loops and VLOOKUP but...

## Retrieve Number from a website into Excel

excel,excel-vba
From this website http://bit.ly/1Ib8IhP I am trying to get this number into an Excel cell. Avg. asking price in Bayswater Road: £1,828,502 Is there any way using VBA or another tool? Couldn't make it work with a web query....

## Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

excel,vba,excel-vba,user-defined-functions
I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

## How do I store a SQL statement into a variable

sql,excel,excel-vba
I am currently facing a problem here. I have a column called "DESC1" in a table called "Master". I'm trying to retrieve the value based on something along the lines of this... "Select DESC1 FROM Master WHERE '" & TextBox1.Text & "' " And I'm trying to display on the...

## VBA Debugger shows only 256 elements of a collection

excel-vba,debugging,collections
the title explains my problem. Im working on an excelmakro and my questions is, is there an option that the debugger shows all elements like it does with an array? And if not, is the only workaround the storage of my classobjects in an array after inflating the collection? I...

## Date formatting - making dd/m/yyyy into dd/mm/yyyy

excel-vba,date,format
I got a pretty simple question (but yet I've been stuck at it for some time now). Does anyone know how to make the date value from dd/m/yyyy into dd/mm/yyyy in a variable? dim lastdaylastmonth as date lastdaylastmonth = DateSerial(Year(Date), Month(Date), 0) So this code, as of now, would return...

excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

## Sort multiple columns of Excel in VBA given the top-left and lowest-right cell

excel,vba,excel-vba,sorting
I am trying to sort these three columns (Sort By Col-2) in excel using VBA. Top-left (Row number and Column number e.g. 1,1) and lowest-right cell (Row number and Column number e.g. 9,3) are known. Every cell contains the values of String type. Input: Col-1 Col-2 Col-3 P1 I1 XYZ...

## Count amount of differences and return column name with count

excel,vba,excel-vba
i am finding the differences between 2 worksheets, the code is as follows: For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then mycell.Interior.Color = vbYellow difference = difference + 1 End If If mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then matches = matches + 1 End If When the...

## Right function vba

vba,excel-vba
I want to take everything to the right of the symbol in the string I used Right(enclosedValue, InStr(enclosedValue, "*") - 0) where enclosed value is 29,6 *60, so I need it to return *60, but now it returns ,6 *60 Sorry if this question has been asked already, I have...

## Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

vba,excel-vba,powerpoint-vba
All I want to do is copy a range from Excel and then paste this range into PowerPoint. When my range is manually copied from Excel to the clipboard... If I right click on a blank slide when pasting into PowerPoint, it gives me the option to paste "using destination...

vba,excel-vba
Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i...

## If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

## Errors 91 and 424 when iterating over ranges in Excel VBA

excel,vba,excel-vba,range
I am an absolute VBA beginner. I have been trying to create a function that separates a large range into smaller ranges. However, when I try and iterate over the large range, I get errors 91 and 424 interchangeably. Here is the relevant bit of code: Dim cell As Range...

## VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba
I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

## EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba
I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

## Range, Select, Change contents, Allignment or Offset?

excel,vba,excel-vba
I have a case at the moment where I am moving down the column with the names below and clicking on a macro, that then marks the indicator with a 35, a few columns down to the right. Due to the nature of the page, I am wanting to count...

## Concatenate N number of columns between 2 specific column name in VBA Excel

excel,vba,excel-vba
I am trying to concatenate the selected range between 2 specific columns. My first column name is "Product-name" (First column is fixed) and second specific column is not fixed. It can be 3rd, 4th, 5th or N. The name of that column is "Price". I want to concatenate all columns...

## Excel Search VBA macro

excel,vba,excel-vba
I have been given the task of searching through a large volume of data. The data is presented identically across around 50 worksheets. I need a macro which searches through all these sheets for specific values then copies certain cells to a table created in a new workbook. The macro...

## Changing the active cell

excel,vba,excel-vba,excel-2007
I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C \$##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to...

## VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

## Way to capture double click to open file?

Is there a way to have an Excel macro to check what file I double clicked on to open. When I open that file, the Add-Ins installed load first, then the file I clicked on loads. How can I write code inside one of my Add-Ins to check what the...

## Vba changing directory to save

excel-vba
I'd like to save a file in a directory whose name changes according to the name of the Previous Month. Here is the code: name_month = MonthName(Month(Date) - 1) Set Newbook = Workbooks.Add With Newbook .Title = "TESO1" .SaveAs Filename:="C:\Users\ee31264\Desktop\Mensile Automat\name_month \send\TESO1.xlsx" End With Newbook.Close unfortunately the name_month i wrote...

## Replace reference with its value in Excel VBA workbook

vba,excel-vba
I have an Excel workbook to manage my Delivery Notes, which creates another workbook with actual delivery note and stores it in the folder for me, then places new record in my main workbook so that the Delivery Number for the next delivery note can be increased by 1. My...

## Excel VBA 2013 Print Image

image,excel-vba,printing
I would like to print an image that shows in a frame on a userform. frame1.Picture = LoadPicture(spath & xPicture & ".jpg") spath is the full path to where the picture is stored. This shows the picture just fine. Now I would like to be able to print it. Is...

## How to insert data by using VBA in excel

excel-vba
I have 3 sheets (sheet1,sheet2,sheet3) in sheet1 has all user ID, sheet2 has logon user ID and sheet3 is empty. The point is... I need to put not logon user ID into sheet3 but my code fail. If it's a stupid question that because I'm newbie with VBA Here my...

## Doing a same action to all the subfolders in a folder

excel,excel-vba
Given below code converts all xlsx files inside "C:\Files\Bangalore" to csv files. Sub xlsxTOcsv() Dim sPathInp As String Dim sPathOut As String Dim sFile As String sPathInp = "C:\Files\Bangalore\" sPathOut = "C:\Files\Bangalore" Application.DisplayAlerts = False sFile = Dir(sPathInp & "*.xlsx") Do While Len(sFile) With Workbooks.Open(fileName:=sPathInp & sFile) .SaveAs fileName:=sPathOut &...

## VBA - .find printing wrong value

excel,vba,excel-vba
I have two sections of code that basically do the same thing but with two different columns. The code finds the header "CUTTING TOOL" and "HOLDER" (looping through multiple files) and prints the information from those columns into one worksheet, masterfile. I was using a less efficient method of setting...

## Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups

excel,vba,excel-vba
I'm working in Excel 2013 to (programmatically) add a straight line connector between the lower right hand corner of a rectangle that is part of a grouped shape with the endpoint of a grouped series of line segments. As it stands, I can't even seem to do this manually on...

## Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

## ReplaceLine method in VBE only replacing part of line

excel-vba,vbe
In response to this question I thought it would be fun to write a VBE macro that would automatically replace lines which look like DimAll a, b, c, d As Integer by Dim a As Integer, b As Integer, c As Integer, d As Integer In my first draft I...

## Exit Sub And Call another Sub

vba,excel-vba,call
I have two subs, sub1 and sub2. I want to exit sub1 completely and start sub2 if a condition is satisfied. My attempt is (running from : sub1 . . If x=y Then Exit Sub And Call sub2 End If . . End Sub ...

## VBA for duplicate rows

excel-vba,duplicates
I have a sheet of columns. I want to compare data in multiple columns, and return a flag in another column to indicate rows that are duplicates. I found a little code online which was meant for checking one column of data, and have so far been unsuccessful in being...

## Excel - Open a named userform

excel-vba,userform
Sure this is very basic, I had userforms that I had not named and I called these by using UserForm1.Show. Now I have named these eg. ProgressBar. I call this one by UserForms.Add("ProgressBar").Show vbModeless But i cant figure out how to make this bit work: Private Sub UserForm_Initialize() 'With UserForms("ProgressBar")...

## Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

## How do I get a cell's position within a range?

excel,vba,excel-vba
How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the Row- and Column-properties, but I am unsure of how to do the same within a range. I considered using the position of...

## Excel-VBA: create named Range in row until end of cell content

vba,excel-vba,range
Imagine an Excel sheet with some rows and some content in each row (i.e. different column-length for each row). In Excel-VBA: How can I create a range-X within column-X that goes from row-cell-2 to the end of the content of this X-column ?? i.e. I would like to create a...

## Find column with unique values move to the first column and sort worksheets

excel,vba,excel-vba,sorting
I have 2 worksheets with the same headers in different orders. Headers are I.D, Name, Department, Sales, Start date, End Date and a few others. What I am aiming to do is search through the workbooks in which the headers may be in different orders, find the column which has...