Fetch all rows that meet criteria and display in new worksheet Watch

Nathant93
Badges: 0
Rep:
?
#1
Report Thread starter 4 years ago
#1
I am trying to automate excel so that it allows me to show all records from another tab where a certain condition is met.

I have a worksheet called 'CAT' which has all information so is therefore my source table. On another worksheet called 'Summary' I have a dropdown cell which contains dates in cell G3 e.g. 2011, 2012, 2013 etc. when I choose a year I would like for all information from the 'CAT' worksheet that matches the date to be displayed in the 'Summary' worksheet starting in Cell D12, if that makes sense... I'll attach some screen shots so it makes it a bit easier to understand.

So far I have tried some VBA, and put into Cell D12 - MyFunction(G3)

Function MyFunction(parVal As String) As String
'Clear what is there now
ActiveSheet.Range("D12", "I3000").ClearContents

'Add new rows
varRange = Sheets("CAT").UsedRange
varCount = 2
For varRow = 1 To varRange.Rows.Count
If varRange(varRow, 1) = parVal Then
varRange(varRow, 1).EntireRow.Copy
varCount = varCount + 1
ActiveSheet.Cells(varCount, 1).EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next varRow
MyFunction = "Found " & (varCount - 2) & " rows"
End Function
Attached files
0
reply
davros
  • Study Helper
Badges: 16
Rep:
?
#2
Report 4 years ago
#2
(Original post by Nathant93)
I am trying to automate excel so that it allows me to show all records from another tab where a certain condition is met.

I have a worksheet called 'CAT' which has all information so is therefore my source table. On another worksheet called 'Summary' I have a dropdown cell which contains dates in cell G3 e.g. 2011, 2012, 2013 etc. when I choose a year I would like for all information from the 'CAT' worksheet that matches the date to be displayed in the 'Summary' worksheet starting in Cell D12, if that makes sense... I'll attach some screen shots so it makes it a bit easier to understand.

So far I have tried some VBA, and put into Cell D12 - MyFunction(G3)

Function MyFunction(parVal As String) As String
'Clear what is there now
ActiveSheet.Range("D12", "I3000").ClearContents

'Add new rows
varRange = Sheets("CAT").UsedRange
varCount = 2
For varRow = 1 To varRange.Rows.Count
If varRange(varRow, 1) = parVal Then
varRange(varRow, 1).EntireRow.Copy
varCount = varCount + 1
ActiveSheet.Cells(varCount, 1).EntireRow.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next varRow
MyFunction = "Found " & (varCount - 2) & " rows"
End Function
You haven't actually said what your problem is!

Does your code work, or are you running into some problems?
0
reply
Nathant93
Badges: 0
Rep:
?
#3
Report Thread starter 4 years ago
#3
(Original post by davros)
You haven't actually said what your problem is!

Does your code work, or are you running into some problems?
It doesn't work at the moment, hence why it's on here Basically I have created the function but it doesn't do anything... On the 'Summary' worksheet I want to show all data from the 'CAT' worksheet when a single year is selected in the dropdown list in cell G3, so for example if i choose 2011 all the data from the CAT worksheet with the date of 2011 then the entire row is displayed in the 'Summary' sheet starting off from cell D12 onwards.
0
reply
davros
  • Study Helper
Badges: 16
Rep:
?
#4
Report 4 years ago
#4
(Original post by Nathant93)
It doesn't work at the moment, hence why it's on here Basically I have created the function but it doesn't do anything... On the 'Summary' worksheet I want to show all data from the 'CAT' worksheet when a single year is selected in the dropdown list in cell G3, so for example if i choose 2011 all the data from the CAT worksheet with the date of 2011 then the entire row is displayed in the 'Summary' sheet starting off from cell D12 onwards.
OK, the thing that jumped out at me immediately when I scanned your code is that you have a function changing (or attempting to change) a worksheet!

I'm not sure if it's technically illegal for a function to try to change cells on a sheet but it's certainly considered bad programming practice. Try changing your Function to a Sub (and your End Function to an End Sub) and also where you assign a value to the function at the end either use a MsgBox temporarily to report the number of found rows, or write the number of found rows to a specific cell.

Run the macro from the Developer ribbon to start with - if it works then you'll need something like a command button on the worksheet that you can click to invoke the macro when it's finalized.

See if this works - I'm pretty sure your problem is because worksheet changes are disallowed inside functions; the purpose of a function is to do calculations and return a result.
0
reply
Nathant93
Badges: 0
Rep:
?
#5
Report Thread starter 4 years ago
#5
(Original post by davros)
OK, the thing that jumped out at me immediately when I scanned your code is that you have a function changing (or attempting to change) a worksheet!

I'm not sure if it's technically illegal for a function to try to change cells on a sheet but it's certainly considered bad programming practice. Try changing your Function to a Sub (and your End Function to an End Sub) and also where you assign a value to the function at the end either use a MsgBox temporarily to report the number of found rows, or write the number of found rows to a specific cell.

Run the macro from the Developer ribbon to start with - if it works then you'll need something like a command button on the worksheet that you can click to invoke the macro when it's finalized.

See if this works - I'm pretty sure your problem is because worksheet changes are disallowed inside functions; the purpose of a function is to do calculations and return a result.
I've given up using that code as it wasn't working so I created some more instead and it seems to work sort of but not quite, basically at the moment I am working on one sheet and trying to get it to work on that and then I'll try and get it to work over two sheets (if that makes sense), however it doesn't paste into the cell that I want it to start in and I don't know why, so was wondering if you could help with that?

Here is my updated code....

Sub FindDate()
Dim Year As String
Dim finalrow As Long
Dim i As Integer

'Clears the contents of Cells from N11 to Z321 on Sheet named CAT
Sheets("CAT").Range("N11:Z321"). ClearContents

'Gets the value of the year from Cell L4 on sheet named CAT
Year = Sheets("CAT").Range("L4").Value


finalrow = Sheets("CAT").UsedRange.SpecialC ells(xlCellTypeLastCell).Row
MsgBox (finalrow)
For i = 11 To finalrow
'MsgBox (i)
If Cells(i, 3) = Year Then
Range(Cells(i, 1), Cells(i, 10)).Copy

'Pastes Data into sheet CAT, starting at Cell X55
Sheets("CAT").Range("X55").End(x lUp).Offset(1, 0).PasteSpecial
End If
Next i

End Sub
0
reply
davros
  • Study Helper
Badges: 16
Rep:
?
#6
Report 4 years ago
#6
(Original post by Nathant93)
I've given up using that code as it wasn't working so I created some more instead and it seems to work sort of but not quite, basically at the moment I am working on one sheet and trying to get it to work on that and then I'll try and get it to work over two sheets (if that makes sense), however it doesn't paste into the cell that I want it to start in and I don't know why, so was wondering if you could help with that?

Here is my updated code....

Sub FindDate()
Dim Year As String
Dim finalrow As Long
Dim i As Integer

'Clears the contents of Cells from N11 to Z321 on Sheet named CAT
Sheets("CAT").Range("N11:Z321"). ClearContents

'Gets the value of the year from Cell L4 on sheet named CAT
Year = Sheets("CAT").Range("L4").Value


finalrow = Sheets("CAT").UsedRange.SpecialC ells(xlCellTypeLastCell).Row
MsgBox (finalrow)
For i = 11 To finalrow
'MsgBox (i)
If Cells(i, 3) = Year Then
Range(Cells(i, 1), Cells(i, 10)).Copy

'Pastes Data into sheet CAT, starting at Cell X55
Sheets("CAT").Range("X55").End(x lUp).Offset(1, 0).PasteSpecial
End If
Next i

End Sub
Personally when I'm trying to do something like this I maintain a "target row" counter to keep track of the current row where I'm pasting (which won't be the same as the source row since not all rows get copied).

So I'd write something like
Dim targetRow as long

targetRow = 55

' When there's a match, paste the result and increment targetRow:
blah.cells(targetRow, 24).PasteSpecial ' 24 = column X
targetRow = targetRow + 1

Is anything being copied at all or does it go into the wrong place?
0
reply
Nathant93
Badges: 0
Rep:
?
#7
Report Thread starter 4 years ago
#7
(Original post by davros)
Personally when I'm trying to do something like this I maintain a "target row" counter to keep track of the current row where I'm pasting (which won't be the same as the source row since not all rows get copied).

So I'd write something like
Dim targetRow as long

targetRow = 55

' When there's a match, paste the result and increment targetRow:
blah.cells(targetRow, 24).PasteSpecial ' 24 = column X
targetRow = targetRow + 1

Is anything being copied at all or does it go into the wrong place?
The correct things are being copied but they're just being pasted into the wrong starting cell.. It must have something to do with the line:
Sheets("CAT").Range("N55").End(x lUp).Offset(1, 0).PasteSpecial

Cause it starts to paste them into cell N3? which I really don't understand
0
reply
Nathant93
Badges: 0
Rep:
?
#8
Report Thread starter 4 years ago
#8
Don't worry I have sorted it out...
0
reply
X

Quick Reply

Attached files
Write a reply...
Reply
new posts
Latest
My Feed

See more of what you like on
The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

Personalise

University open days

  • University of Birmingham
    Postgraduate Open Day Postgraduate
    Wed, 20 Mar '19
  • King's College London
    Postgraduate Taught Courses - Arts & Sciences - Strand Campus Postgraduate
    Wed, 20 Mar '19
  • University of East Anglia
    All Departments Open 13:00-17:00. Find out more about our diverse range of subject areas and career progression in the Arts & Humanities, Social Sciences, Medicine & Health Sciences, and the Sciences. Postgraduate
    Wed, 20 Mar '19

Where do you need more help?

Which Uni should I go to? (34)
14.11%
How successful will I become if I take my planned subjects? (21)
8.71%
How happy will I be if I take this career? (50)
20.75%
How do I achieve my dream Uni placement? (34)
14.11%
What should I study to achieve my dream career? (30)
12.45%
How can I be the best version of myself? (72)
29.88%

Watched Threads

View All