View Full Version : Call data from another sheet based on multi critiria
kero4000
04-19-2018, 04:06 AM
Dear All
kindly find attached workbook have 2 sheets
1st sheet is as database
2nd sheet is for analysis
required :banghead:
call data from database sheet to analysis sheet based on the criteria and condition in the sheet
thanks
Attachment :
22059
kero4000
04-21-2018, 08:25 AM
any help ????
kero4000
04-22-2018, 09:17 AM
any help ?? :dunno
Bob Phillips
04-22-2018, 02:49 PM
Not with as sparse a description as that and no sample workbook.
kero4000
04-23-2018, 07:01 AM
I cant understand you my friend :dunno
kero4000
04-24-2018, 04:11 AM
Any Help Guys ???????? :crying:
Paul_Hossler
04-24-2018, 07:24 AM
Try this
Option Explicit
Sub Calls_Click()
Dim rData As Range, rFilters As Range
Dim wsAnalysis As Worksheet
Dim iDatabase As Long, iAnalysis As Long, i As Long
Application.ScreenUpdating = False
Set rData = Worksheets("Database").Cells(1, 2).CurrentRegion
Set wsAnalysis = Worksheets("Analysis")
Set rFilters = wsAnalysis.Range("B1:D10")
With wsAnalysis
Range(.Rows(12), Rows(.Rows.Count)).ClearContents
End With
iAnalysis = 12
' 1 2 3 4 5 6 7 8 9
'Period Type Date Category Description Amount Marker Tag Ref
For iDatabase = 2 To rData.Rows.Count
'3 Type
'4 Date C-D
'5 Category
'6 Description
'7 Amount
'8 Marker
'9 Tag
'10 Ref
With rData.Rows(iDatabase)
For i = 2 To 10
Select Case i
Case 1, 3, 5, 6, 7, 8, 9, 10
If Len(rFilters.Cells(i, 2).Value) > 0 And .Cells(i - 1).Value <> rFilters.Cells(i, 2).Value Then GoTo GetNext
Case 2, 4
If Len(rFilters.Cells(i, 2).Value) > 0 And .Cells(i - 1).Value < rFilters.Cells(i, 2).Value Then GoTo GetNext
If Len(rFilters.Cells(i, 3).Value) > 0 And .Cells(i - 1).Value > rFilters.Cells(i, 3).Value Then GoTo GetNext
End Select
Next i
.Copy wsAnalysis.Cells(iAnalysis, 2)
iAnalysis = iAnalysis + 1
End With
GetNext:
Next iDatabase
Application.ScreenUpdating = True
End Sub
kero4000
04-25-2018, 01:19 AM
Dear Paul_Hosslerthank you very much for your support iam very thankfulwhats your opinion if i make anew sheet as shart of account , we will make for each category a number then we can filter with 2 criteria Is it right ????Thankskero
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.