Converting / reading a cell while filtering ( double .. Ccur ... )

[JonasB]

Hi There :)


Please, not been able to filter or using the right function to read a currency value :doh:

Thank you :)

=======

27884


=======


Option Explicit


Sub data_controle()


Dim LastRow As Long


Dim Valorx As Double


With ActiveSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

' .Range("A8:E" & LastRow).Sort Key1:=.Range("C9"), Order1:=xlAscending, Header:=xlYes


.Range("A8:E" & LastRow).AutoFilter Field:=3, Criteria1:=">=" & CLng(Range("C4").Value), Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C5").Value)
' Linha acima = Data controle - Field 3 ( coluna C )


.Range("A8:E" & LastRow).AutoFilter Field:=2, Criteria1:="*" & Range("B4") & "*"
' Linha acima = Representantes - Field 2 ( coluna B )

Range("A1").Value = Valorx
.Range("A8:E" & LastRow).AutoFilter Field:=4, Criteria1:=Valorx



.Range("A8:E" & LastRow).AutoFilter Field:=5, Criteria1:="*" & Range("E4") & "*"

End With


End Sub

[Paul_Hossler]

1. Deleted your second post which was a quote of the first

2. Add CODE tags to format your macro. Use the [#] icon to insert CODE tags and then paste your macro between

3. Attaching a small sample workbook with data and your macro makes it easier for people to see the issue

[p45cal]

Perhaps
.Range("A8:E" & LastRow).AutoFilter Field:=4, Criteria1:=.Range("D4").Value?
You probably don't need Valorx at all.
Suppling a file would be very helpful.

[JonasB]

Hi there

Please, take a look at the attached file.

Thank you

Kind regards

[p45cal]

Can't gain access to the code - it's password protected.

[JonasB]

Can't gain access to the code - it's password protected.


Ooops, really sorry.

The pass is: 123=

Now everybody knows it :)

[p45cal]

try:
Sub data_controle()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A8:E" & LastRow).AutoFilter Field:=3, Criteria1:=">=" & CLng(Range("C4").Value), Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C5").Value)

If .Range("B4").Value = "" Then
.Range("A8:E" & LastRow).AutoFilter Field:=2
Else
.Range("A8:E" & LastRow).AutoFilter Field:=2, Criteria1:="*" & .Range("B4") & "*"
End If

.Range("A8:E" & LastRow).AutoFilter Field:=4, Criteria1:=.Range("D4").Text

If .Range("E4") = "" Then
.Range("A8:E" & LastRow).AutoFilter Field:=5
Else
.Range("A8:E" & LastRow).AutoFilter Field:=5, Criteria1:="*" & .Range("E4") & "*"
End If

End With
End Sub

[JonasB]

try:
Sub data_controle()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A8:E" & LastRow).AutoFilter Field:=3, Criteria1:=">=" & CLng(Range("C4").Value), Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C5").Value)

If .Range("B4").Value = "" Then
.Range("A8:E" & LastRow).AutoFilter Field:=2
Else
.Range("A8:E" & LastRow).AutoFilter Field:=2, Criteria1:="*" & .Range("B4") & "*"
End If

.Range("A8:E" & LastRow).AutoFilter Field:=4, Criteria1:=.Range("D4").Text

If .Range("E4") = "" Then
.Range("A8:E" & LastRow).AutoFilter Field:=5
Else
.Range("A8:E" & LastRow).AutoFilter Field:=5, Criteria1:="*" & .Range("E4") & "*"
End If

End With
End Sub



Hi there and thank you :) It is not filtering anything :think:.

[p45cal]

Works here.
Tried to link to a video:
https://app.box.com/s/jz5nsbvpkifk9n9i1qlu4jckijyedj5u

[JonasB]

Works here.
Tried to link to a video:
https://app.box.com/s/jz5nsbvpkifk9n9i1qlu4jckijyedj5u



Thanks a lot for the video :bow:

date range + the amount = it's working perfectly

but I also need to be able to search in all the fields ( or )

Example:

Despesas---- Data-------------------------------Valor------Obs

alu----------- 02/05/2021 - 02/05/2021-----100,00---- pag

or

Despesas---- Data-------------------------------Valor------Obs

xxxxxxxxxxx 02/05/2021 - 02/05/2021-----100,00---- xxx

or

Despesas---- Data-------------------------------Valor------Obs

xxxxxxxxxxx 02/05/2021 - 02/05/2021-----100,00---- pag

[Paul_Hossler]

Thanks a lot for the video :bow:

date range + the amount = it's working perfectly

but I also need to be able to search in all the fields ( or )



@JonasB -- suggestion:

You do not need to "Quote" your own entries

Just type in the blank area

Likewise, if you do include a quote, you can edit it since most times, it's not adding anything to quoting a macro, or someone's paragraphs and paragraphs of text

[p45cal]

but I also need to be able to search in all the fields ( or )That's in place now as AND. For OR as well, Autofilter won't do that. You need a re-write; two ways come to mind; (a) use a loop to run down the entire list which if long might take noticeable time or (b) use Advanced Filter in vba. This could be quite slick.
Either way, I'm not inclined to re-write it for you because I don't have the time right now, also because it's way off the scope of your original question which was about getting vba to autofilter the currency values; that bit's been answered.
You're probably better served starting a new thread.

[JonasB]

That's in place now as AND. For OR as well, Autofilter won't do that. You need a re-write; two ways come to mind; (a) use a loop to run down the entire list which if long might take noticeable time or (b) use Advanced Filter in vba. This could be quite slick.
Either way, I'm not inclined to re-write it for you because I don't have the time right now, also because it's way off the scope of your original question which was about getting vba to autofilter the currency values; that bit's been answered.
You're probably better served starting a new thread.


Hey

I thought something with the "ifs " that should be adjusted but since u said that it is necessary the looping... well, it is something beyond my understanding :(

Thank you