Posts by aidos

Quote

Hello,

Jaafar Tribak has created a very elegant solution ... :wink:

see attached ...

Hope this will help

Display More

That solution didnt help because what I want is a dynamic list that for every letter typed it filter the list range to only show the possible matches, but seeing how your file worked it may have show me how I can fix my problem, but i dont know if its possible to do it.

Lets me explain what i understood of the problem

My dynamic list range works by for every letter that is typed in the combobox it changes the value in the linked cell, and using the value of the linked cell it update the dynamic range list to only show the possibles matches.

If I use the arrow keys to move through the combobox list the linked cell get the full value of the option in the dropdown list therefore updating the dynamic range list and since this new value only have one match it exclude all the other options.

To fix this problem I tought in this solution, if the key pressed is not an arrow key then all the changes are sent to the linked cell, but if the arrow key is used it stop sending the values to the linked cell to allow the user to move through the list and when he founds its he press Enter and then the selected value is sent to the linked cell.

Anyone knows if this is possible?

I created a combobox to help the user to type the right name of a item that he's looking for. The combobox will suggest the possible values that the user can select, I did this combobox by using this guide (https://trumpexcel.com/excel-d…-with-search-suggestions/)
The only problem I found is the only way to select the value is by typping every letter or to click using the mouse in the dropdown list, but I want to allow the user to use the arrows keys to move through the list.
By using that combobox if the user use the arrow key it selects the first value in the dropdown list therefore removing all other matches

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Exemple of the problem","data-attachmentid":1210446}[/ATTACH]
What I want is when using the arrow keys it wont select the value but scroll through the list until he founds what he needs.
I looked everywhere and found some people that had the same issues but none of them could help me, and thats why im here trying to found the answer.

Sample of the workbook attached to the topic

I found the problem and solution

The problem was that some values was treated as numbers and others as text to solve it i used this code to convert all values to text.

Sub NumToText()
' Convert the selected Excel numbers value to Excel text by changing format
lrow = Worksheets(1).Range("A" & Worksheets(1).Rows.Count).End(xlUp).Row
For Each cell In Worksheets(1).Range("A1:A" & lrow)
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Dim Temp As Double
Temp = cell.Value
cell.ClearContents
cell.NumberFormat = "@"
cell.Value = CStr(Temp)
End If
Next cell
End Sub

If anyone knows why some values was treated like number and others not, I would be glad to know

I have this sheet with order's number and to make it easier to use. I created a text box that when typed it will filter the number that contains it in the Order Columns.

However some of the numbers when typed dont show. For instance if I type 999 it wont show any results. but if you go to the filter dropdown arrow in the column header and in the search field you type 999 it will show all the results.

Im totally confuse and dont know what is making this happen or how to solve it, if anyone can help me. If there's a solution using another method to filter instead of a textbox I dont mind it

here's the code and the sample sheet

Private Sub TextBox1_Change()
    Dim strFilter As String
    strFilter = "*" & [A1] & "*"
    Debug.Print strFilter
   ActiveSheet.ListObjects("Dados").Range.AutoFilter _
     Field:=1, _
     Criteria1:= _
        Array(strFilter)


End Sub

Ill look it thanks

Ok Here is my problem:

I Have to create a sheet for every month of the year, so I can know how much of each product we have. To do this im thinking in using a pivot table in another workbook to make things more organized. But i have to do it for every month so to make things easier im thinking in using VBA to create it for every Sheet.

I Already have a macro for exctrating the data I need from a mastersheet, the only problem is to create the pivot table for every month

in the pivot table I want to organize it by type of product in the week. To do it I created a column with the week number, and for easer reading i want to show the number of the first and last day of that week.

In case my explanation wasnt clear I have a sample of my sheet here. It have the macro to import the data, in case you think is important, a exemple of the way I want the pivot table.

The pivot table is in the same file for this sample but for my use I would like to be in another Workbook (Error.xlms) if you can help me to make this happen i would be glad

If you have a better idea to mass create the pivot table with the same configuration to all the sheets not using VBA Im dont mind.

I looked in a lot of places of how to create a pivot table but none worked and thats why im here.

I Started this week to use VBA and I have no knowledge on coding so dont judge too bad it

Im here again with another problem and ill probably post again later. Im not putting everthing I want in a single topic because I want to learn how do it instead of getting something done by someone else.

My Problem:

I want to copy just some columns (C, S, U) of my sheet after I filtered it.

One of the problems is that I want to do it for multiple filter criterias so I just cant say, for exemple A1:A500, because I dont know how many entrys one filter has

The second problem is copying the columns and pasting it in another sheet

Here is my code. it will filter by the criterias i want, but i didnt put the loop for all the sheets yet, but its not the problem. I'm stuck at the part that copy the columns

Function IsWorkBookOpen(FileName As String)


Dim FF As Integer, ErrNum As Integer


Select Case ErrNum
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNum
End Select
End Function
Sub extrair()


Dim Font As Workbook
Dim Dest As Workbook
Dim info


info = IsWorkBookOpen("C:\Users\EU\Desktop\Projetos\Arquivos fonte\Mestre de Pedidos.xlsx")


If info = False Then
    Workbooks.Open ("C:\Users\EU\Desktop\Projetos\Arquivos fonte\Mestre de Pedidos.xlsx")
End If


Set Font = Workbooks("Mestre de Pedidos.xlsx")
Set Dest = Workbooks("Dados.xlsm")


mesNome = Left(Dest.Worksheets(1).Name, 3)
Ano = Right(Dest.Worksheets(1).Name, 2)


mes = Month(DateValue("01-" & mesNome & "-1900"))


bInicio = mes & "/01/20" & Ano
bFim = mes & "/31/20" & Ano




With Font.Worksheets(1)


    .AutoFilterMode = False


    With .Range("A1:V1")


        .AutoFilter


        .AutoFilter Field:=10, Criteria1:="Entregue"


        .AutoFilter Field:=19, Criteria1:=">=" & bInicio, Operator:=xlAnd, Criteria2:="<=" & bFim


    End With


End With


End Sub

I did find some answers for this problem in this forum but they were too complex for me to undestand and modify for my use or it used this syntax Range("A1:A600") but when I run it it gives me error 438

---------------------------------------

Update:

I found a way to do it here is it if anyone is interested

lRow = Font.Worksheets(1).Range("C" & Font.Worksheets(1).Rows.Count).End(xlUp).Row
Font.Worksheets(1).Range("C1:C" & lRow).Copy Dest.Worksheets(Z).Range("A1")
Font.Worksheets(1).Range("K1:K" & lRow).Copy Dest.Worksheets(Z).Range("B1")
Font.Worksheets(1).Range("U1:U" & lRow).Copy Dest.Worksheets(Z).Range("C1")
Font.Worksheets(1).Range("S1:S" & lRow).Copy Dest.Worksheets(Z).Range("D1")

it worked thanks

Here is my problem:

I have a mastersheet with thousands of data and multiple Headers, and I only need the number of data that meets my criteria in a specific month. My idea was to filter first by the city, because i dont care about the others, but since its a huge sheet and they have data since 2013, and Im only interested in for exemple October,2017. im trying to filter again using the month I need but i just cant do it.

The Date Column is typed by hand and it is in the format dd/mm/yyyy

Sub extract()


Dim Font As Workbook
Dim Dest As Workbook


Set Font = Workbooks("Data.xlsx")
Set Dest = Workbooks("MySheet.xlsm")


With Font.Worksheets(1)




        With .Range("A1:V1")


            .AutoFilter


            .AutoFilter Field:=10, Criteria1:="City Name"


            .AutoFilter Field:=19, Criteria1:= 02/10/2017          




        End With


End With


End Sub

With this code i can filter by city name, but when i try to do by date it wont work.

If i type the whole date for exemple :

.AutoFilter Field:=19, Criteria1:= 02/10/2017

it shows results, but I dont want it to show only a specific day I want a entire month. So I tried changing the line for this:

.AutoFilter Field:=19, Criteria1:= ??/10/2017 and .AutoFilter Field:=19, Criteria1:= **/10/2017

I also tried changing the date format to mm/dd/yyyy and none of them gave any results, even though they exist

I Looked everywhere and couldnt find a answer to this and thats why im here.

I cant share the data workbook so I tried to be clear as possible but i not sure if it is. Anyway thanks for the help

This filtering processes is only the begging of what im trying to do. If I can pass this step im trying to make so that the Month it will search it will be based by the name of the worksheet. For Exemple if the sheet is named Oct 17 it will filter by the Month October in the year 2017.

This part im not sure if its possible thats why I didnt put in the main question but if you guys have any ideia if its possible ill be thankful if any information is given of how do it

it worked thanks

This is the end result if you only use the macro once. If i use again it will just copy the same parts (2nd attachment)

First, i want to say that i have 0 knowledge on vba or progamming, i just used my common sense and few pieces of other people's codes, this is probably why my code is not working properly

I created a code that searches for a specific information in a workbook and paste it on another workbook, pasting the copied information in the sheet with the same name as the information source.

I Need to update my final sheet (Erros.xmlm) at least once a week, because i need to keep my workbook updated. But if i use the macro again it duplicate all the information that i already had copied.

My question is: Can I create a way so excel knows if it already copied that information and dont do it again.

If it isnt possible can I limit the range that it will paste the information, so instead of pasting new info it will ovewrite the old ones.

Here is my code

Function IsWorkBookOpen(FileName As String)


Dim FF As Integer, ErrNum As Integer


Select Case ErrNum
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNum
End Select
End Function
Sub Importar()


Dim Font As Workbook
Dim Dest As Workbook
Dim pesq As Range
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim ini As Range
Dim info


info = IsWorkBookOpen("C:\Users\EU\Desktop\Projetos\Arquivos fonte\Resumo de Entrega Mensal - comparativo.xlsx")


If info = False Then
Workbooks.Open ("C:\Users\EU\Desktop\Projetos\Arquivos fonte\Resumo de Entrega Mensal - comparativo.xlsx")
End If


Set Font = Workbooks("Resumo de Entrega Mensal - comparativo.xlsx")
Set Dest = Workbooks("erros.xlsm")


For x = 1 To Font.Sheets.Count
For Z = 1 To Dest.Sheets.Count


 If Right(Font.Worksheets(x).Name, 5) = Right(Dest.Worksheets(Z).Name, 5) Then
        Set copySheet = Font.Worksheets(x)
        Set pasteSheet = Dest.Worksheets(Z)
        On Error Resume Next
        Font.Worksheets(x).Activate
        Set pesq = copySheet.Range("A1").Resize(500, 10).Find(What:="Semana", LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
        Set ini = pasteSheet.Range("A1")
            If Not pesq Is Nothing Then
            firstAddress = pesq.Address
                Do
                pesq.Select
                Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(14, 0)).Select
                Selection.Copy
                ini.PasteSpecial
                Application.CutCopyMode = False
                Set pesq = copySheet.Range("A1").Resize(500, 10).FindNext(pesq)
                Set ini = pasteSheet.Range("IV1").End(xlToLeft).Offset(, 2)
                Loop While Not pesq Is Nothing And pesq.Address <> firstAddress
            End If
        End If
Next Z
Next x


Font.Close


End Sub

and some sample of my workbooks if you want to try it