Excel/Pivot Table


I was wondering if you could help me out with this vba code that I tried to use but I am not getting the desired result.  

Data in Pivot Table
Low Cost Houses
 Mr. A
 Mr. D
 Mr. M

Medium Cost Houses
 Mr. E
 Mr. F
 Mr. G
 Mr. K

 Mr. O
 Mr. P

I should be getting this result when running the vba code appearing at the bottom of this message -

Mr. E
Mr. F
Mr. G
Mr. H

but instead is getting

Mr. E
Mr. E
Mr. E
Mr. E

Sub Test()
   Dim cHse As Range
   cCost = "Medium Cost Houses"
   nRow = 5

   Do While Worksheets("Bal").Range("A" & nRow) <> ""
       With ActiveSheet.PivotTables("PivotTable1").RowRange
         Set cHse = .Cells.Find(What:=cCost, After:=.Cells(1), _
         LookAt:=xlWhole, SearchDirection:=xlNext)
         If cHse Is Nothing Then Exit Sub
       End With
       Worksheets("Target").Range("A" & nTRow) = cHse
       nRow = nRow + 1
End Sub

Hi Frank,

I'm not sure I understand your question.
In your source data, you don't have a Mr. H but in expected result, you are showing a Mr. H.
Further the logic on which you are querying the data is not clear.

Can you explain you requirements more clearly so I can help.
If possible, please share a link to your file.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 About.com. All rights reserved.