Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Filtre value from date and filter advanced...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Filtre value from date and filter advanced...
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Filtre value from date and filter advanced...
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

--
Regards,
Tom Ogilvy


"sal21" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Filtre value from date and filter advanced...
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:

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

--
Regards,
Tom Ogilvy


"sal21" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Filtre value from date and filter advanced...
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

--
Regards,
Tom Ogilvy


"sal21" wrote:

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:

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

--
Regards,
Tom Ogilvy


"sal21" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced Filter when using Date Range Cells Jim Excel Discussion (Misc queries) 3 January 25th 09 08:53 PM
Advanced filter a dynamic date range oneandoneis2 Excel Worksheet Functions 2 April 6th 06 08:57 AM
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du TC Excel Worksheet Functions 1 May 12th 05 02:06 AM
Advanced Filter using Date represented as text drice Excel Worksheet Functions 1 December 15th 04 04:56 PM
Date ranges and advanced filter rt Excel Programming 3 December 1st 04 09:12 PM


All times are GMT +1. The time now is 11:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement