Help In Macro for If condition Met Criteria

[manilara]

http://www.excelforum.com/excel-programming-vba-macros/896733-need-help-from-experts-or-any-one.html


I want macro code with met criteria conditions

One Master sheet pull data from multiple workbook named sheet in targeted folder,but not range of cell ,multiple dymensions cell values

Like

[pre]

Sub temp()
Application.DisplayAlerts = True

Dim wb As Workbook
Dim sht As Worksheet
Dim r As Integer

Set sht = ActiveSheet 'sheet for results
r = 2 '1st row
Mydir = ActiveWorkbook.Path
myfile = Dir(Mydir & "" & "*.xls")
Do While Len(myfile) > 0
Set wb = Workbooks.Open(Mydir & "" & myfile)
fnd = False
For Each ws In wb.Sheets
If ws.Name = "New Proposed Checklist" Then fnd = True: Exit For
Next

If fnd Then
With wb.Sheets("New Proposed Checklist")
sht.Cells(r, 2) = wb.Name
sht.Cells(r, 3) = .Range("d3")
sht.Cells(r, 4) = .Range("d4")
sht.Cells(r, 5) = .Range("d5")
sht.Cells(r, 6) = .Range("d6")
sht.Cells(r, 7) = .Range("h3")
sht.Cells(r, 8) = .Range("h4")
sht.Cells(r, 9) = .Range("h5")
sht.Cells(r, 10) = .Range("h6")
sht.Cells(r, 11) = .Range("k3")
sht.Cells(r, 12) = .Range("k4")
sht.Cells(r, 13) = .Range("k5")
sht.Cells(r, 14) = .Range("k6")
sht.Cells(r, 15) = .Range("I5")
[b]Here I want add code with criteria condition Like : If in every workbook

in J1:J65000 any cell of column met "Yes" towards (I2:I65000 columns met criteria "A" or "B", or "C", or "D"),

I want count value A,B,C,D in master cell 16,17,18,19 , Other than Yes Stop Code.[/b]

sht.Cells(r, 16) = .Range("H1")
sht.Cells(r, 16) = .Range("H2").Application.WorksheetFunction.CountIf(Range("h:h"), "A")
sht.Cells(r, 17) = .Range("H1")
sht.Cells(r, 17) = .Range("H2").Application.WorksheetFunction.CountIf(Range("h:h"), "B")
sht.Cells(r, 18) = .Range("H1")
sht.Cells(r, 18) = .Range("H2").Application.WorksheetFunction.CountIf(Range("h:h"), "C")
sht.Cells(r, 19) = .Range("H1")
sht.Cells(r, 19) = .Range("H2").Application.WorksheetFunction.CountIf(Range("h:h"), "D")
End With
Else
MsgBox "Sheet Contains No Data " & wb.Name
End If
Application.DisplayAlerts = False
wb.Save
Application.DisplayAlerts = True
wb.Close Saved = True
Application.AskToUpdateLinks = False
ActiveWorkbook.Save
myfile = Dir
r = r + 1
Loop
MsgBox "File Fetching Completed"
End Sub

[/pre]

 

[NARAYANK991]

Hi manilara ,


Please note that today being Sunday , there is bound to be a lull in activity ; you may or may not get a reply today.


I am not able to understand your criterion ; can you explain further by an example ? What does the following mean ?


If in every workbook in J1:J65000 any cell of column met "Yes" towards

(I2:I65000 columns met criteria "A" or "B", or "C", or "D"),

I want count value A,B,C,D in master cell 16,17,18,19 , Other than Yes Stop Code


If any cell in the range J1:J65000 is "Yes" , and if any cell in the range I2 ( or is it I1) : I65000 contains "A" , "B" , "C" , "D" , I want to count the values "A" , "B" , "C" , "D" , in the master cell 16 , 17 , 18 , 19 ; if any cell in the range J1:J65000 contains anything other than "Yes" , skip the code.


I am still not able to understand this ; can you please explain ?


Narayan

 

[manilara]

Now I Make it clear Here

Ji:J65000 Columns May Contains Value "Yes" Next column K May Contains If A B C D Value towards Yes if Yes Met to A,B,C,D Result should not count value of A,B,C,D