PDA

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