Filtre value from date and filter advanced...

[sal21]

here is the file: http://www.mytempdir.com/935740
First sorry for my english....

I have this code to insert letter "E" in column N of a sheet GAF only if the
dates in column B of sheet GAF are into range DATAIN/DATAFIN

use for test DATAIN=01/11/2005 DATAFIN =30/11/2005)

Now, i want to use the same code to insert "E" in column N if the range of
dates is naturally into range DATAIN/DATAFIN but if the value into column H
is the same present into column B of sheet CORPORATE. So, insert in column
“N†of sheet GAF the letter “E†in cells N2, N49, N50, N51 ecc…


Dim RIGA As String

Dim NUM_CONTR As Long

'If Me.TextBox1.Value = "" Then
' MsgBox ("IL CAMPO DATA INIZIO NON PUO' ESSERE VUOTO"), vbCritical
' Me.TextBox1.SetFocus
' Exit Sub
'End If


'If Me.TextBox2.Value = "" Then
' MsgBox ("IL CAMPO DATA FINE NON PUO' ESSERE VUOTO"), vbCritical
' Me.TextBox2.SetFocus
' Exit Sub
'End If


'If Me.TextBox1.Value > Me.TextBox2.Value Then
' MsgBox ("RANGE DI DATE ERRATO! DATA FINE MINORE DI DATA INIZIO"), vbCritical
' Me.TextBox1 = ""
' Me.TextBox2 = ""
' Me.TextBox1.SetFocus
' Exit Sub
'End If

DATAIN = Me.TextBox1
DATAFIN = Me.TextBox2


'If DATAIN > DATAFIN Then
' MsgBox ("DATA FINE MINORE DI DATA INIZIO"), vbCritical
' Exit Sub
'End If


RIGA = 2

Sheets("GAF").Select

Set ELENCO = Worksheets("GAF")

ELENCO.Range("N2:N9000").ClearContents


While Not ELENCO.Range("A" + RIGA) = ""

DoEvents

If ELENCO.Range("B" + RIGA) >= DATAIN And ELENCO.Range("B" + RIGA) <=
DATAFIN Then ELENCO.Range("N" + RIGA) = "E"

RIGA = RIGA + 1

NUM_CONTR = ELENCO.Range("T1")

Me.Label4.Caption = NUM_CONTR

Wend

End Sub

 

[Tom Ogilvy]

Sub aBC()
Dim RIGA As String
Dim NUM_CONTR As Long
Dim rng As Range

'If Me.TextBox1.Value = "" Then
' MsgBox ("IL CAMPO DATA INIZIO NON PUO' ESSERE VUOTO"), vbCritical
' Me.TextBox1.SetFocus
' Exit Sub
'End If


'If Me.TextBox2.Value = "" Then
' MsgBox ("IL CAMPO DATA FINE NON PUO' ESSERE VUOTO"), vbCritical
' Me.TextBox2.SetFocus
' Exit Sub
'End If


'If Me.TextBox1.Value > Me.TextBox2.Value Then
' MsgBox ("RANGE DI DATE ERRATO! DATA FINE MINORE DI DATA INIZIO"), vbCritical
' Me.TextBox1 = ""
' Me.TextBox2 = ""
' Me.TextBox1.SetFocus
' Exit Sub
'End If

DATAIN = Cdate(Me.TextBox1)
DATAFIN = Cdate(Me.TextBox2)


'If DATAIN > DATAFIN Then
' MsgBox ("DATA FINE MINORE DI DATA INIZIO"), vbCritical
' Exit Sub
'End If

With Worksheets("Corporate")
Set rng = .Range(.Cells(2, "G"), .Cells(2, "G").End(xlDown))
End With



RIGA = 2

Sheets("GAF").Select

Set ELENCO = Worksheets("GAF")

ELENCO.Range("N2:N9000").ClearContents

' Column A was empty in your sample, so used B

While Not ELENCO.Range("B" & RIGA) = ""

DoEvents

If ELENCO.Range("B" & RIGA) >= DATAIN And _
ELENCO.Range("B" & RIGA) <= DATAFIN Then
Debug.Print RIGA
If Not IsError(Application.Match(CLng(ELENCO.Range("H" & RIGA).Value), _
rng, 0)) Then
ELENCO.Range("N" & RIGA) = "E"
End If
End If
RIGA = RIGA + 1

NUM_CONTR = ELENCO.Range("T1")

Me.Label4.Caption = NUM_CONTR

Wend

End Sub

 

[sal21]

Tom work perfect!!!!!!!!!!!!!!!!!!!!
You have helped me in other case with brilliance code and briliance mind to
undesrtand my terrible englsh... Wizard for code and to understand my
english...

But if i want use the macro if i click the refred butonoption in CORPORATE,
REATIL, LARGEc.., PUBB amm... how to use to set the related sheet, when i
click one of this?

"Tom Ogilvy" ha scritto:

 

[Tom Ogilvy]

Do you mean when you press the button on userform1 to run this macro, you
want it to look at
Checkbox9, checkbox10, checkbox11, and checkbox12 and if one of those is
checked, match the number in H of GAF to column G of the selected sheet? if
so

Private Sub CommandButton1_Click()
Dim RIGA As String
Dim NUM_CONTR As Long
Dim rng As Range
Dim sName As String
Dim cbox As MSForms.CheckBox

'If Me.TextBox1.Value = "" Then
' MsgBox ("IL CAMPO DATA INIZIO NON PUO' ESSERE VUOTO"), vbCritical
' Me.TextBox1.SetFocus
' Exit Sub
'End If


'If Me.TextBox2.Value = "" Then
' MsgBox ("IL CAMPO DATA FINE NON PUO' ESSERE VUOTO"), vbCritical
' Me.TextBox2.SetFocus
' Exit Sub
'End If


'If Me.TextBox1.Value > Me.TextBox2.Value Then
' MsgBox ("RANGE DI DATE ERRATO! DATA FINE MINORE DI DATA INIZIO"), vbCritical
' Me.TextBox1 = ""
' Me.TextBox2 = ""
' Me.TextBox1.SetFocus
' Exit Sub
'End If

DATAIN = DateValue("11/01/2005 ") 'Me.TextBox1
DATAFIN = DateValue("11/30/2005")


'If DATAIN > DATAFIN Then
' MsgBox ("DATA FINE MINORE DI DATA INIZIO"), vbCritical
' Exit Sub
'End If

For i = 9 To 12
Set cbox = Me.CheckBoxes("Checkbox" & i)
If cbox Then
sName = cbox.Caption
Exit For
End If
Next
If sName = "" Then
MsgBox "No sheet selected for validation"
Exit Sub
End If



With Worksheets(sName)
Set rng = .Range(.Cells(2, "G"), .Cells(2, "G").End(xlDown))
End With



RIGA = 2

Sheets("GAF").Select

Set ELENCO = Worksheets("GAF")

ELENCO.Range("N2:N9000").ClearContents


While Not ELENCO.Range("B" & RIGA) = ""

DoEvents

If ELENCO.Range("B" & RIGA) >= DATAIN And _
ELENCO.Range("B" & RIGA) <= DATAFIN Then
Debug.Print RIGA
If Not IsError(Application.Match(CLng(ELENCO.Range("H" & RIGA).Value), _
rng, 0)) Then
ELENCO.Range("N" & RIGA) = "E"
End If
End If
RIGA = RIGA + 1

NUM_CONTR = ELENCO.Range("T1")

'Me.Label4.Caption = NUM_CONTR

Wend

End Sub