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

Free Microsoft Excel 2013 Quick Reference

Counting filtered results Results

When using auto filters. Sorting by (1) Date to Date then by (2) Product ID, I show visible rows that I can total the columns (Total Weight) that excludes non visible lines using =SUBTOTAL(9,Q10:Q1000). That Works.

But additionally I need to count ONLY the number of items in the visible column that contain the text of various Truck Companies to get a count of the number of trucks shipped. If I try to count the non blank cells of the visible cells it counts all the hidden cells too.

How do I count just the number of visible cells containing text in a column when in auto filter? OR count someother column containing numbers or text for the purpose of getting a count of the visible lines that would get the same result (number of shipments)?

Hello,

I've got a table with the following columns:
FirJrPer: which shows what company, year and month the data is about (text).
Car: the licenceplate of the specific car
Departement: text
Nrcars: indicates how much that car was used by that departement (0-1).
Totalcost: total cost of the cars for that specific period and company.

My problem: I want a calculated column, which gives me, for each row (=car), the sum of Nrcars for the rows in the table where FirJrPer is the same.
I've tried it with:
=sumx(filter('Cars',Cars[FirJrper]=Cars[FirJrper]),Cars[Nrcars]),
hoping that the filter-expression would give the selection I want, but it gives me the whole table. If I replace it with a specific example, e.g. =sumx(filter('Cars',Cars[FirJrper]="LNU20111",Cars[Nrcars]), then I get the correct result. So, I want the same thing, but with "LNU20111" being what is in the FirJrper-field of the particular row.

Hope someone can help.

Steven

Hi..

Problem:
column A has dates in m/d/yyyy format. iam applying auto filter on A
and counting each of the filtered rows.

I wanted the sum of column B(contains nos) filtered rows.. in to some other column.

suppose my filterd result contains 7 values. i want the sum of all 7 values of colum B.
next time filtered result may contain 15 values. i want the sum of all 15 values of colum B.
iam using the SUBTOTAL macro code to get the result. but failing...

i need macro code.

ActiveSheet.Range("$k$1:$k$" & iRow).AutoFilter Field:=1, Criteria1:= _ (((( Auto filter here)))
"<=" & Cell_a_value

RangeToCount = "T51:T999" & iRow
On Error Resume Next
VisibleRows = Range(RangeToCount).SpecialCells(xlCellVisible).count (((( Counting the filtered rows))))

i need code for sum of the filtered rows.

help..

This was posted by someone earlier but I could not see the reply to this problem which is similar to mine:

I have an athletic spreadsheet displaying the name and result of a particular match. I want to be able to Auto Filter the names to easily read the results. That's easy... The Auto Filter just simply "hides" the others names when I choose a particular athlete so when I do a COUNTIF, it counts every name rather than the filtered name. Is there a secret COUNTIF IF statement that I can embed?

Please help.
Julia

Hi Guys,

I have a database that has a filter applied, and the filters are used. How do i count only the number of rows that are displayed; (that meet the filter criteria); in VBA

Sheet where filter is applied : "Database"
1st Row is a header row
Used Range Col A:P

For example I apply the filter to only display rows that have the value "Jordan" in column J.
Every Tenth Row in the filtered range contains "Jordan" so the following rows are shown
Rows: 10, 20, 30, 40, 50, etc etc.
therefore i want the macro to return the number of rows that are filtered. If 100 rows are present, then 10 "Jordan"s are found and the result should be 10
Speculative example only

Regards

Aargh! This one has kept me up up all night!

Basically, I have a folder with around 400 csv files ranging from 100k to 300meg. I want to loop through all of these files, select the data in columns E and F, filter column E for unique values and then extract E&F to a workbook at the last empty row.

I've amended some of the mighty Ron de Bruin's code to accomplish this. Unfortunately, the macro falls over itself after processing the first 20 or so sheets.

It throws the good old "The information cannot be pasted because the copy area and the paste area are not the same shape and size" at this line

It's probably something really simple, but I can't work out why it's doing this. All the csv files have the same headers and
are the same number of columns. Yet some csvs throw the error, others don't.

Full code is below. I will attempt backflips of gratitude for anyone who can help me!

Sub MergeWithFilter()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, FNum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, DestRange As Range
    Dim rnum As Long, CalcMode As Long
    Dim rng As Range
    Dim FilterField As Integer, RangeAddress As String
    Dim ShName As Variant, RwCount As Long

    '**************************************************************
    '***Change these five lines of code before you run the macro***
    '**************************************************************

    ' Change this to the pathfolder location of the files.
   MyPath = "C:UsersSteeeeeveDocumentsPimmDataOBISunzipOBIS"
   'MyPath = "C:UsersSteeeeeveDocumentsPimmDataOBISunzipTest"
    ' Fill in the name of the sheet containing the data.
    ' Use ShName = "Sheet Name" to use a sheet name instead if its
    ' index. This example uses the index of the first sheet in
    ' every workbook.
    ShName = 1

    ' Fill in the filter range: A1 is the header of the first
    ' column and G is the last column in the range and will
    ' filter on all rows on the sheet.
    ' You can also use a fixed range such as A1:G2500.
    RangeAddress = Range("E1:F" & Rows.Count).Address

    ' Set the field that you want to filter in the range
    ' "1 = column A" in this example because the filter range
    ' starts in column A.
    FilterField = 1

    ' Fill in the filter value. Use the "<>" if you want to
    ' filter on the absence of a term. Or use wildcards such
    ' as "ron*" for cells that start with ron, or use
    ' "*ron*" if you look for cells where ron is a part of the
    ' cell value.
    'SearchValue = "ron"

    '**********************************************************
    '**********************************************************


    ' Add a slash after MyPath if needed.
    If Right(MyPath, 1) <> "" Then
        MyPath = MyPath & ""
    End If

    ' If there are no Excel files in the folder, exit.
    FilesInPath = Dir(MyPath & "*.csv")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill the myFiles array with the list of Excel files in the
    ' folder.
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    ' Change application properties.
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Add a new workbook with one sheet.
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    ' Loop through all files in the myFiles array.
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next
                ' Set the filter range.
                With mybook.Worksheets(ShName)
                    Set sourceRange = .Range(RangeAddress)
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then
                    ' Find the last row in target worksheet.
                    rnum = RDB_Last(1, BaseWks.Cells) + 1

                    With sourceRange.Parent
                        Set rng = Nothing
                        .AutoFilterMode = False

                        ' Filter the range on the
                        ' value in filter column.
                        sourceRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
                                sourceRange.Copy BaseWks.Cells(rnum, "A")
                         



                    End With
                End If

                ' Close the workbook without saving.
                mybook.Close savechanges:=False
            End If

            ' Open the next workbook.
        Next FNum

        ' Set the column width in the new workbook.
        BaseWks.Columns.AutoFit
        MsgBox "Look at the merge results in the new workbook " & _
           "after you click on OK."
    End If

    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

Function RDB_Last(choice As Integer, rng As Range)
   Dim lrw As Long
   Dim lcol As Integer
      On Error Resume Next
      RDB_Last = rng.Find(What:="*", _
                          After:=rng.Cells(1), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
      On Error GoTo 0
End Function


Hi Guys,
I am trying to write a macro to filter the list on set of Criteria and then count rows. then it should put the criteria in one column and no. of row in another of a new sheet. I want to loop through the criteria.

For example

I have 10000 rows in list. I have 5 filter criteria in column with header "Fab", four filter criteria in column with header FA, four Filter criteria in column with header "Package/Tool name".

Now, i want to filter on "1A" in column under "Fab" ,on "aa" in column under "FA" and then on "ABC111" in column F, now count the rows and put data in sheet with name "Results of macro" as shown in attachment

Then keeping filter "1A" on column under " Fab" and "aa" on column under "FA" and loop through all the criteria on column under "Package/ Tool name", record the results on sheet "Results of macro" as shown.

Next still keep filter on "1A" on column under " Fab" and loop through criteria on column under " FA" and record results.

Utimately loop through criteria on column under the header "Fab" while recording results.

See the attachment for better understanding
Any help will be much appreciated.

Roop

Hi all,

I have found a few threads which show how to cut/copy & paste with set conditions or filters - this i can manage.
I am having a real hair puller of a prob with (See attached example attached)
Sheet 'ALLDATA' I want to copy last 200 rows of data meeting criteria;
- Col DIA = 12
- Col EG = EG2
Then paste into sheet 'EG2'.

The prob is only selecting the last 200 rows (or up to 200 max in some cases there is less) results.

Thanks in advance for any help

I'm trying to copy and paste the results, including header and data, from an autofilter routine. If there are no results, i don't want to copy anything, I instead want to insert a message where i would normally paste the results. My macro is below, and I can do everything except the copy/paste. I can't get the either/or part to work right. I can either paste the autofilter range, or I can paste the message, but I can't get the message to paste when the autofilter range is empty. My code is below, and it's the "See whether there's data" section that's tripping me up. I'd appreciate any help you can provide.

Sub Step_1()

  Dim DstWkb As Workbook
  Dim rng As Range
  Dim RngEnd As Range
  Dim Cell As Range
  Dim rng2 As Range
  Set rng = Selection
  
  
  Set DstWkb = Workbooks("Active Position Checklist.xls")
   With Application
        .ScreenUpdating = False
        .EnableEvents = False
 
    
    'Copy and paste the Procedure
        DstWkb.Worksheets("Sheet1").Range("A250").EntireRow.copy
Destination:=Sheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
                
    'Restrain the filter to cells from A1 to the last entry in column X
        With DstWkb.Worksheets("APRData")
            Set rng = .Range("A1:X1")
            Set RngEnd = .Cells(Rows.Count, rng.Column).End(xlUp)
            Set rng = IIf(RngEnd.Row < rng.Row, rng, .Range(rng, RngEnd))
        End With
     Stop
        'Filter the data using column M
          rng.EntireRow.Autofilter Field:=13, Criteria1:=("0.00")
                       
          'Trap the error if there were no matches
          On Error Resume Next
          
          'See whether there's data or not
        With DstWkb.Worksheets("APRData").Autofilter.Range
        On Error Resume Next
        Set rng2 = Range("A2:X20000").SpecialCells(xlCellTypeVisible)
        'Set rng2 = Autofilter.Range.Offset(1, 0).Resize(Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
         End With
          If rng2 Is Nothing Then
            DstWkb.Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0) = "Congratulations! 
You have no Zero FTEs!"
        Else             
           'Copy and paste only the filtered data
           'Set rng = IIf(RngEnd.Row < rng.Row, rng, .Range(rng, RngEnd))
            rng.SpecialCells(xlCellTypeVisible).copy _
                Destination:=DstWkb.Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
        End If
         'Clear the error if there was one
          Err.Clear
       
         'Return error control back to the system
          On Error GoTo 0
            
         'Copy and paste the Solution
            Sheets("Sheet1").Range("A251").EntireRow.copy
Destination:=Sheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
                                              
        'Turn off the autofilter
          Worksheets("APRData").AutoFilterMode = False
    End With
End sub


Hello,

I've got a very frustrating problem with a macro that I'm using to autofilter one sheet and copy the data found to another sheet. The autofilter is set to use a custom filter (taking the filter criteria as a string from yet another worksheet), but there are some custom filters that return no results (this is intentional, as they will return results in the future). When no results are found, Excel returns the run-time error '1004' of 'No Cells Were Found'. I've got an error handler in place, but for some reason it doesn't seem to be helping - I've searched all over the place, but none of the solution methods I've found seem to include the error taking place as part of a loop that will need resuming after the error is passed (since Resume Next breaks things further down the track). By the look of things, the Error goto command works once, then doesn't work again.

The original code was sent my way by a forum user called JBeaucaire - I've just removed the notes they included to try and keep the length of the code section down to a minimum.

The code is as follows (I've tried to include the relevant sections that aren't included in the section of code that's causing the problem):

Dim LR As Long, LRq As Long, FR As Long
Dim BR As Long, i As Long, Val As String
Dim wsPQ As Worksheet, wsNumNam As Worksheet
Application.ScreenUpdating = False

Set wsPQ = Sheets("Positions&Quals")
Set wsNumNam = Sheets("Pos Numbers and Names")

    LR = wsNumNam.Range("A" & Rows.Count).End(xlUp).Row
    i = 1

    Sheets.Add.Name = "Upload"
    Sheets("Upload").Activate
    Range("A2:D" & Rows.Count).ClearContents
    FR = 2
    
    wsPQ.Range("A1:C1").AutoFilter
    LRq = wsPQ.Range("A" & Rows.Count).End(xlUp).Row
    
  On Error GoTo ErrorSkip:
    Do
ErrorReturn:
        Val = wsNumNam.Range("B" & i).Text
        wsPQ.Range("A1:C1").AutoFilter field:=1, Criteria1:=Val
                                            
      wsPQ.Range("A2:C" & LRq).SpecialCells(xlCellTypeVisible).Copy Range("B" & FR)

        BR = Range("B" & Rows.Count).End(xlUp).Row
        Range("A" & FR, "A" & BR) = wsNumNam.Range("A" & i)
        FR = BR + 1
        i = i + 1
    Loop Until wsNumNam.Range("A" & i) = ""

Exit Sub

ErrorSkip:
BR = Range("B" & Rows.Count).End(xlUp).Row
FR = BR + 1
i = i + 1
GoTo ErrorReturn:

Exit Sub

End Sub
This line is the one causing the problems:
I can see why - it's trying to copy a range that contains no visible cells. If anyone could suggest exactly what it is I need
to do in order to 'skip' this error without just using 'Resume Next' (which just causes more problems for me further down the
line!), I'd hugely appreciate it. Is there a reason my ErrorSkip: section doesn't help? I'm still learning VBA, so maybe
there's just a term I haven't come across yet. I've tried to make the ErrorSkip: section simply set the Autofilter up to
progress to the next 'Val' criteria and repeat the process, as if the issue with non-visible cells simply hadn't happened,
but it doesn't seem to like it! I'm sure there's another way of doing this - maybe just getting the macro to select the area
to be copied and, using an IF statement, telling it not to copy if there are no visible cells? I just don't know how to put
that statement into the above code! 

Like I said, when I watch the code function in Break Mode, the On Error Goto works fine the first time, but is totally ignored the second time. I think this is the crux of the issue.

For reference, wsPQ and wsNumNam are the two source worksheets (and 'Upload' is the worksheet where the collated information is placed). The autofilter criteria of 'Val' goes down the A column of wsNumNam one cell at a time, using the contents as the autofilter string (B & i etc.). The error occurs when the autofilter string provides no results.

Edit: I've attached an example of the problem, containing a few records in each worksheet. In this instance, the 'no cells found' error is occuring on the last entry in the custom autofilter list - however, in the original spreadsheet, which contains hundreds of records on both forms, the issue occurs much sooner (as it comes across an autofilter string with no results within the first 100 records or so).

Thanks a bunch for your time!

I just had a rush job to do up an Excel sheet while my supervisor dictated
the data to me as she interpreted the emails/faxes with various responses.
One column in particular had YES and NO as the possible data type. In the
end, I was to total up the number of YES answers and put the total in one
cell; and then to do the same for the NO answers. These YES's and NO's are
all in one column, unfortunately, but the sum of each we put into 2 cells.
Is there a formula that I could have used for each just substituting a value
that would work with the YES answers and another that would return the total
of the NO answers?

I had a filter and because this was a rush just filtered for each and
counted them up and inserted figure manually, but it would be good to
automate this as they'll need to be inputting more data as responses come in
over the next 5 days. Hoping there's a way to deal with the two types of
data.

Thank you!! :oD

I'm trying to count the instances of specific numbers in a column, but many are repeated. So I put a filter on the column so I could see each instance once. I want to be able to count how many instances there are and present these numbers separately. The problem is that there are too many instances...around 500+. So I can't count and re-write manually.

Ex. Column A has 5 numbers. 1.0, 1.0, 2.1, 2.1, 3.0. What I want to be able to do is say, there are actually only 3 different codes (1.0, 2.1, 3.0). Even though they happen multiple times, resulting in 5 numbers, there are only 3 different codes.

Any ideas? I'm not so good with vba, so doing this without it would be awesome.

Thanks in advance!

Hello -

I'm new and have read the FAQs, but if any of my post is inappropriate, please let me know and I'll happily edit.

I'm trying to create a simple bill of materials. In one column (B2:B51) I have a list of electronic part values. Some occur more than once. My aim is to have excel look at this list, and output another list of unique values, along with a count of how many times each unique value occurs in my original list.

Through searching, the best solution I've been able to find has been to use the advanced filter feature to create a list of unique values (I tell the list to start at D2, and it extends through D7), and then use the COUNTIF function to tell me how many times each unique value appears in the original list (this seems to work, and can be seen in E2:E7). The problem I'm having is that when I run the advanced filter, the list of unique values that is output actually contains a duplicate (in my sample sheet, the value "10n").

I've attached my file in case it's easier to see than to read a noob's explanation.

I'm hoping to make this a multi-tabbed sheet, so I have two questions.

1. What am I doing wrong with the advanced filter feature?
2. Is there a way that I can have a "standing" formula or other solution - so that as I enter new part values in column B, the unique results and counts found in columns D and E update automatically in real time?

Thanks kindly for any help you can provide.
Joe

I have two Excel spreadsheets. In one the results of applying a filter are
displayed in the status bar - eg. '24 of 144 records found'. In the other I
get just 'Filter mode'.
I've examined the option settings - they appear identical. There are no
macros attached to the sheets either. The '24 of 144 records found' type of
message is very useful. How do I make all my spreadsheets produce it on
filtering?

Hi,

I'm using AVERAGEIFS to count specific data from a sheet using 3 criterea, however I also need to extract the actual data into another calculation, so instead of returning the average I want it to return the list of corisponding data against 3 selection criterea.

ie: I have a list of members who take part in diferent activities which get a score. I want to be able to lookup the member name with a specific activity and then extract the top 10 scores from their total list of scores.

So A1=surname;B1=forname;C1=activity type;D1=score

Everytime a member attends they get an entry for that day, the list has multiple member name entries.

I can obviously achieve the result by filtering then applying a calculation to the filtered result but I need it to be automated because there are a hell of alot of members.

Any suggestion would be welcomed.

Thanks Shaun

If I have a table of data including three columns of similar data, if there a way for a Pivot Table to count an entry across the multiple columns?

For instance, if I had a list of items that were each assembled from one, two, or three parts (any given part could show up in column 1, 2, or 3) and wanted to use a Pivot Table to count how many items of a particular class included part #415 regardless of which column it was in ... or the average price of items that include part #248, etc.

At present, I've using one Pivot Table and toggling between part1, part2, and part3 entries and adding the results together or setting a filter for each of the three, but there has to be a more elegant solution.

Hi

In a spreadsheet we have a list of caes which are pending. Some rows may relate to the same individual as others. In one column there is a 9-character entry which identifies each individual. I want to be able to put a formula in place which counts the number of different entries, so that if the same entry crops up more than once, that is excluded. I know I could perform an Advanced Filter searching for Unique records only, but is there a formula I could use which would achieve the same end result?

How can I use vlookup against a worksheet that has multiple matching values in the table array? I’d like to match the value in column A, pull the data from Column B, but only if Column C has a certain value in it. From the data below, I’d like to create the results shown below in Sheet2. This would be to capture the count in Column B only if Column C is Yes. I can take care of error checking for missing values later.

Sheet1
	A	B	C
1	Item	Count	Stock
2	aa	123	yes
3	aa	456	no
4	aa	789	maybe
5	bb	123	no
6	bb	456	maybe
7	cc	123	no
8	cc	456	maybe
9	cc	789	yes

Sheet2
	A	B
1	Item	Stock
2	aa	123
3	bb	n/a	
4	cc	789


I think I might have gotten a little bit over my head with a project for work. I’m working on a Userform that pulls from an excel sheet with 35 columns. some of the rows in the column have very similar information, with the exception of 5-7 different cells.
The first thing I was trying to do is be able to filter out the rows that are similar using a listbox and a combobox. In the Combobox, you would input the number in the cell that you are looking for. The userform would then autofilter the results into the listbox.
The next thing I wanted to do is, once you click on one of the results in the listbox, it then shows you all of the data from that specific row.
The first attempt filtered the rows into the listbox, but when I clicked on one of the results, it would always show me Row 1, even if the information I wanted was row 23. The code is listed below.
Private Sub UserForm_initialize()
Dim wks As Worksheet
Dim vaItems As Variant
    Dim I As Long, j As Long
    Dim vTemp As Variant
    
Set wks = Sheets("Legal tracking")
Me.UserFilter.List = wks.Range("B2", wks.Range("B65536").End(xlUp)).Value

comboset.List = Array("Open", "Close")
comboda.List = Array("Leavy", "McConnaughhay", "Rissman")
comboresult.List = Array("n/a", "Yes", "No")
combosettle.List = Array("n/a", "Yes", "No")
combostatus.List = Array("Unresolved", "O/C Dismissed", "We agreed")
combodepo.List = Array("", "Yes", "No")
combodrdep.List = Array("", "Yes", "No")
DCSCR.List = Array("n/a", "Yes", "No")
combotime.List = Array("n/a", "Yes", "No")
CESCR.List = Array("", "Yes", "No")
End Sub

Private Sub UserFilter_Change()
Dim MyList() As Variant
Dim X As Long
Dim Y As Long
Dim FoundSomething As Boolean

FoundSomething = False
Y = 0
For X = 2 To Sheets("legal tracking").Range("B" & Rows.Count).End(xlUp).Row
    If InStr(1, UCase(Sheets("legal tracking").Range("B" & X).Value), UCase(UserFilter)) > 0 Then
        FoundSomething = True
        ReDim Preserve MyList(Y)
        MyList(Y) = Sheets("legal tracking").Range("H" & X).Text
        Y = Y + 1
    End If
Next
If FoundSomething Then
    frmlegal.filteredlist.List = MyList
Else
    frmlegal.filteredlist.Clear
End If
End Sub
Private Sub filteredlist_Click()
 Dim n As Long
    n = filteredlist.ListIndex
    If n > -1 Then
        With Sheets("legal tracking")
            n = n + 2
            txtadj.Value = .Cells(n, 1).Value
            txtclaim.Value = .Cells(n, 2).Value
            txtlname.Value = .Cells(n, 3).Value
            txtfname.Value = .Cells(n, 4).Value
            txtdoi.Value = .Cells(n, 5).Value
            txtda.Value = .Cells(n, 6).Value
            comboda.Value = .Cells(n, 7).Value
            txtpfbrcvd.Value = .Cells(n, 8).Value
            txtpfbresp.Value = .Cells(n, 9).Value
            txtissues.Value = .Cells(n, 10).Value 
            txtresponse.Value = .Cells(n, 12).Value
            DCSCR.Value = .Cells(n, 13).Value
            combotime.Value = .Cells(n, 14).Value
            CESCR.Value = .Cells(n, 15).Value
            txtmeddate.Value = .Cells(n, 16).Value
            combostatus.Value = .Cells(n, 17).Value
            txtpreconf.Value = .Cells(n, 18).Value
            comboresult.Value = .Cells(n, 19).Value
            combosettle.Value = .Cells(n, 20).Value
            txtpostmed.Value = .Cells(n, 21).Value
            combodepo.Value = .Cells(n, 22).Value
            txtdepo.Value = .Cells(n, 23).Value
            txtpredep.Value = .Cells(n, 24).Value
            combodrdep.Value = .Cells(n, 25).Value
            txtdrdepo.Value = .Cells(n, 26).Value
            txtpredr.Value = .Cells(n, 27).Value
            txtpretrial.Value = .Cells(n, 28).Value
            txtfinal.Value = .Cells(n, 29).Value
            txtprefinal.Value = .Cells(n, 30).Value
            txtconfops.Value = .Cells(n, 31).Value
            txtfhresults.Value = .Cells(n, 32).Value
            comboset.Value = .Cells(n, 34).Value
            txtnotes.Value = .Cells(n, 35).Value
End With
End If
End Sub
The second attempt, I used an offset code to have the data pull from the lisbox instead of the spreadsheet, but found that I could only have 9 columns in the listbox, which will not populate the 34 fields that I need to populate. I will post up the second part of code seperately as all of it's too long to fit into one post

I really need help with this as I’m completely stuck. Is this something that just cannot be done, or am I just missing something REALLY important???

I created a dummy work to learn about DCOUNT function (file is attached). There are two sheets which

Sheet1 consists of required information and criteria.

Sheet2 consists of DCOUNT formula to count number of cells that contain numbers in columns of records that match conditions I specified on Sheet1 G1:N8.

My question, is that possible to filter number (cell B3 on Sheet2) by category "Smartphone" from (Sheet1 B2:B9) without using Advanced Filter? so expected result counting number on cell B3 would be 2.

Thank you for any help


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