View original (French)

Create an input box to search for a value

Solved
caribou38 Posted messages 31 Status Member -  
caribou38 Posted messages 31 Status Member -
Hello,
how can I use a box that would allow me to enter a value to search in a list and select all the cells that contain this value and write the time in the adjacent cell?
I can't manage to combine the search function and an input box.
Thank you for your help
Configuration: Windows XP Firefox 3.0

18 answers

  1.
    balou01 Posted messages 86 Status Member 2
     
    One of the foundations of this forum, if you want to get help, is to tell us which programming language you want to use...

    There isn't someone who will give you the solution in every possible language imaginable!
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hi
      I am working on an Excel file and I need to create macros.
      -1
  2. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Hello,
    It's easily doable, but it might be simpler to select one of the cells that contains the cells to search for and a button to submit.

    What do you think?

    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello
      My problem is that I can't write the macro that allows me to search in the column for the values that correspond to the criteria

      for i=9 TO 100
      Maplage=range("F9:F100)
      cells.find(what=range("G2",after :activecell).activate
      activecell.offset(0,3)=time

      NEXT


      Problem this macro does not work as desired, it searches in all the cells of the sheet and writes a bit everywhere

      I need your help thank you
      -1
  3. lermite222 Posted messages 9042 Status Contributor 1 199
     
    You do not specify the coordinates but I have deduced.. 
    Sub BV2() Dim i As Long Dim Debut1 As Integer Dim Critere As Variant Debut1 = 9 Critere = Cells(2, 7) 'for G2 For i = Debut1 To Range("F1").SpecialCells(xlCellTypeLastCell).Row If Cells(i, 6) = Critere Then 'test column F Cells(i, 9) = Time 'writes to column I End If Next i End Sub

    You say
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello
      thank you for your help, your formula works perfectly
      I was using this macro
      If Range("IV1") > 0 Then
      For Each objCell In Range("U4:AV2000")
      If objCell.Value = Range("IV1") Then objCell.Offset(1, 2) = Time
      Next objCell

      but it takes time to process all the rows
      thank you for your solution

      now I am faced with another problem, I need to write a macro that searches for a value in a column, once found it copies certain cells, for example A1, A10, A23 up to 7 values and pastes them in a good

      I don't know if I was clear but I can't think straight with this problem, the subject of my internship, thank you for your help

      see you+
      -1
  4. lermite222 Posted messages 9042 Status Contributor 1 199
     
    What’s your 'good', a sheet? a UserForm?

    Show the macro you started for that.
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello
      Here is a part of my macro that is causing me a problem
      The macro searches in the database for a value entered in range "CV2". As soon as it finds it, it shifts over, copies the found value, and pastes it into a sheet (bon) on another sheet
      The problem is that if the value is not found, the macro crashes
      Thank you for the time you are giving me



      'fills local in the bon (sheet in a sheet that needs to be printed

      With Sheets("bon")
      Columns("J:J").Select
      Selection.Find(what:=Range("CV2"), After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
      xlNext, MatchCase:=False, SearchFormat:=False).Activate
      ActiveCell.Offset(0, 1).Select

      ActiveCell.Copy

      Range("A8:A15").Select
      Selection.Find(what:=Range("CV2"), After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
      xlNext, MatchCase:=False, SearchFormat:=False).Activate
      ActiveCell.Offset(0, 2).Select

      ActiveSheet.Paste


      Columns("J:J").Select
      Selection.Find(what:=Range("CV2"), After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
      xlNext, MatchCase:=False, SearchFormat:=False).Activate
      ActiveCell.Offset(0, 2).Select

      ActiveCell.Copy

      Range("A8:A15").Select
      Selection.Find(what:=Range("CV2"), After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
      xlNext, MatchCase:=False, SearchFormat:=False).Activate
      ActiveCell.Offset(0, 3).Select

      Range("B6") = Range("'feuille de saisie'!J6")
      Range("B7") = Range("'feuille de saisie'!G6")
      Range("B8") = Range("'feuille de saisie'!E6")
      Range("E16") = Range("'feuille de saisie'!H6")


      ActiveSheet.Paste

      I should use a loop to perform the search but I only get a macro that runs infinitely
      See you +
      -1
  6. lermite222 Posted messages 9042 Status Contributor 1 199
     
    For your first error, you need to add an On error, 
     On Error GoTo passe ...... Exit Sub Passe: msgbox "Value not found" End sub

    For the second one, I suppose this is what you want to do?

    To perform more complex searches, use a For Each...Next statement with the Like operator. For example, the following code example shows how to search all cells in the range A1:C5 that use a font with a name starting with the letters "Cour". When Microsoft Excel finds a matching cell, it assigns the Times New Roman font to it. 
    For Each c In [A1:C5] If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman" End If Next

    Otherwise, you explain the second one better.
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello

      Thank you for the time you are dedicating to me

      I am attaching an example of my Excel file so you can see what I need to do
      Your advice has helped me progress in my project, thank you so much again

      https://www.cjoint.com/?icnHRTXfv5

      I have highlighted in green on the sheet what I need to achieve, but so far I’m going in circles.
      -1
  7. lermite222 Posted messages 9042 Status Contributor 1 199
     
    I don't understand, put the complete data (fill the entire line) for a line in the motor sheets, including the second line which seems to be taken into account... NB+ and NB-? in the second to last column.
    Then, with the data from the motor sheets, you fill in the good sheet completely, you ask for data from 1 to 7 but what?

    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello

      I am sending you an example of my file
      what I am trying to do is search in the last column of the "moteur" sheet
      for the cells that contain an order number and copy it into the "bon" sheet in F3
      to copy all the order numbers from the row for example (V4,Z4,AD4...) and paste them into the "bon" sheet in (B9,C9,D9,E9,F9,G9,B10)
      then copy the order to the "impression" sheet
      and repeat the same procedure with the next row containing an order number


      I know I am not very clear and I apologize

      https://www.cjoint.com/?icqqQ1SFPr
      -1
  8. lermite222 Posted messages 9042 Status Contributor 1 199
     
    I thought I was clear, at least it seems to me...
    Then, with the data from the engine sheet, you completely fill out the good sheet, you ask for data from 1 to 7 but what exactly?
    To make myself better understood, FILL OUT THE GOOD BY HAND WITH THE DATA AS IT SHOULD BE, JUST BEFORE PUTTING IT IN THE PRINT SHEET. That way, I have reference points on what you want.
    And a follow-up question, you say to then move to the next line and start over,
    BUT WHERE SHOULD WE PLACE the 2nd form... the 3rd, etc... on the print sheet? one below the other?
    Keep in mind that I am not in your head and I need information, as long as I do not know exactly what you want, it is impossible to get it right.
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello

      and thank you again for your patience
      I’m sending you an example with a completed form with the information I want to see on it
      yes, the forms should be recorded one after the other on the printing sheet

      https://www.cjoint.com/?idlFcTBcHA

      I don’t know if what I want to achieve is feasible, so if you have another solution that could help me move forward, I’m all ears
      thank you for your help
      -1
    2. caribou38 Posted messages 31 Status Member
       
      I consulted the Microsoft knowledge base and it turns out it's a bug
      I'm providing you with the information
      You have an ActiveX DLL in Visual Basic that has a method that takes an array of Scripting Dictionary Objects as an argument. It can work correctly when your Visual Basic client is executed in the integrated development environment (IDE) using this method, but when you run it as a compiled application, you receive the following runtime error message:
      Runtime error "-2147417848 (80010108)":
      Method '~~ ' failed "of object"
      This only occurs when you use late binding to call the method.

      Back to top
      Resolution
      Use early binding to work around the issue.


      Since I'm not very skilled in VBA, I will continue to search until I find a solution.
      -1
  9. lermite222 Posted messages 9042 Status Contributor 1 199
     
    No problem doing what you wanted, I went with the information provided.
    If it's not quite right, just let me know...
    Your binder

    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Thank you very much for your help and especially for your patience
      it's exactly what I have been trying to do for a month without succeeding

      I'm going to ask for your help again, your macro fills in the first form correctly but then it gives me an error message "the paste method of the object failed" or "the select method of the class failed"
      -1
  10. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Be VERY CAREFUL with the sheet names; if you gave me a template with different sheet names, that's why it's crashing. Often, a space makes the difference. If you test on my workbook, the 2 correct ones are indeed copied in print.
    So look a bit... Your turn to play.

    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      I'm testing the spreadsheet you sent me and I'm still getting this error
      I'm going to keep looking
      thanks again for your help which has been more than precious to me.
      I'll keep you updated on the situation.
      -1
      1. lermite222 Posted messages 9042 Status Contributor 1 199 > caribou38 Posted messages 31 Status Member
         
        Do you have the error on MY FOLDER? If so, what did you change? Because for me it works perfectly.
        EDIT: Wait... aren't you trying it on Cjoint by any chance??? Because it doesn't work, you need to right-click on the file name and save it to your hard drive, and then open it.
        --
        Experience teaches more surely than advice. (André Gide)
        -1
      2. caribou38 Posted messages 31 Status Member > lermite222 Posted messages 9042 Status Contributor
         
        I have saved the file on my hard drive
        I'm wondering if it's because I am using an Excel 97 version
        Your macro works well on the first line but on the next one it throws an error
        It cannot paste the correct one after the first one in the print sheet
        As a result, Excel crashes and I have to use Ctrl Alt Delete to be able to exit

        I continue to look for where the bug might come from; there’s no reason it should work for you and not for me

        Thanks again for your help
        -1
  11. lermite222 Posted messages 9042 Status Contributor 1 199
     
    We'll have to find a solution..
    try this..
    '
    --------------------------------------------- 'Copy the correct one in the print page. FLbon.Range("A4:G19").Copy FLI.Cells(LigImp, 1) LigImp = LigImp + 16 '---------------------------------------------

    At the end of the macro, replace all the lines between the dashed lines so that it looks like above, it works for me too, I didn’t use this method first because it sometimes causes problems.
    you say

    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      I always have the same error
      the first good one is copied into the print sheet and then this error
      I feel like it's the variable
      Dim LigImp As Long that is causing the problem
      -1
  12. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Apparently, it's a Windows error.
    An ActiveX Visual Basic DLL that connects to the Windows NT event log can cause errors when called by a client program running in the Visual Basic debugger. Possible errors include:

    • Error 429.
    • Error 80010108 OLE.
    • Access violation.

    Status
    Microsoft has confirmed the existence of an issue in Microsoft Transaction Server version 1.0. We are investigating this issue and will publish new information here in the Microsoft Knowledge Base as soon as it becomes available.

    What version of Windows do you have? PakX?

    --
    Experience teaches more surely than advice. (André Gide)
    -1
  13. lermite222 Posted messages 9042 Status Contributor 1 199
     
    I reread the error message you gave... it seems there’s an error with the objects.
    Replace the line copy with 
     Sheets("bon").Range("A4:G19").Copy Sheets("impression").Cells(LigImp, 1)

    and if that still doesn't work, I’ll change it in the body of the macro

    Edit:
    There’s still something, check in the Project - VBAproject window of the IDE, if you don’t have the project MoreFunc_Toolbaar
    You said
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      I made the modifications now the error message is "unable to read the specialCells property of the range class" runtime error 1004

      I have an updated XP Pro SP3
      no I don't have a project toolbar in the VBA project

      I continue to search
      -1
  14. lermite222 Posted messages 9042 Status Contributor 1 199
     
    put your binder back on Cjoint
    Edit;
    Wait.. when Excel crashed, did you reboot? if not, do it.
    I have the 2000 and when that happens to me I have to do that too.
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      I rebooted and retested but still the same symptoms, doctor.

      I'm giving you back the spreadsheet; I left the error so you can see it.

      https://www.cjoint.com/?idvunZFA47
      -1
  15. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Sorry, but I downloaded the workbook, I clicked on the button and everything is OK.
    Without your PC I really can't see what's wrong.
    Check this again..
    In the IDE >> Tools >> References
    In the opened window you should have the following lines checked..
    Visual Basic For Applications
    Microsoft Excel X.X Object Library
    Ole Automation
    Microsoft Office X.X Object Library
    Microsoft Forms 2.0 Object Library
    If any are missing, find it in the list and check it.
    --
    Experience teaches more surely than advice. (André Gide)
    -1
  16. caribou38 Posted messages 31 Status Member
     
    I tested the macro on another PC, same result.

    So either I'm missing a library or my version of Excel is bugged.
    I will continue to analyze the macro until I find where it is stuck.
    Thanks again for your help and your great patience.
    I'll keep you updated if there's any news.
    See you!
    -1
    1. lermite222 Posted messages 9042 Status Contributor 1 199
       
      Have you checked the references I gave above?
      --
      Experience teaches more surely than advice. (André Gide)
      -1
  17. lermite222 Posted messages 9042 Status Contributor 1 199
     
    I have rewrote the macro and removed everything related to objects; it's not optimized code but it will probably work.

    https://www.cjoint.com/?iehNn0c54X
    See you!
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello
      thank you for your help, I feel embarrassed to monopolize you like this
      you have my infinite gratitude
      I tested the macro and it's better this time, it works but as long as I haven't cleared the print sheet otherwise I get the same error again
      If Cells(LigImp, 1).Interior.ColorIndex = xlNone Then Exit For

      impossible to read the interior property of the range class
      thank you again for your help
      -1
    2. caribou38 Posted messages 31 Status Member
       
      I modified the way to copy the slip in the print page, it seems to work although it copies the slip in descending order

      but for lack of a better solution for now

      it's this line that doesn't work for me, who knows why
      Copy the slip to the print page.
      Sheets("bon").Range("A4:G19").Copy Sheets("impression").Cells(LigImp, 1)
      LigImp = LigImp + 16

      I replaced it with

      'Copy the slip to the print page.
      Sheets("bon").Select
      Range("A4:G20").Select
      Selection.Copy
      Sheets("impression").Select
      Rows("1:1").Select
      Selection.Insert Shift:=xlDown

      thank you again so much for your help and I mean it, you've pulled me out of a bind, I've been struggling with this macro for a month
      -1
    3. lermite222 Posted messages 9042 Status Contributor 1 199 > caribou38 Posted messages 31 Status Member
       
      No, take back this workbook then, and add below... 
       'there are indeed formulas to reach the last 'filled line but it doesn't work when there are filters 'that are activated. If LigImp < 1 then LigImp = 1

      It had nothing to do with the previous errors, it's a normal error if LigImp = 0
      Because your way of doing things in the next post isn't very...uhh
      See you

      --
      Experience teaches more reliably than advice. (André Gide)
      -1
    4. caribou38 Posted messages 31 Status Member > lermite222 Posted messages 9042 Status Contributor
       
      I tried your modification but a new error occurred

      it's the way of copying to the print sheet that doesn't work for me
      the second point is that I just realized that if the first cell in the engine list sheet is empty, the macro stops and doesn't check the other cells

      I'm trying to see if I can replace if with each for
      what do you think
      be gentle with me, I'm a beginner in macros and you've helped me improve
      -1
    5. lermite222 Posted messages 9042 Status Contributor 1 199 > caribou38 Posted messages 31 Status Member
       
      Don't change anything about the for, otherwise it won't work anymore. To do a For each you need to know how far to go, which is not possible with your filters.
      It stops on purpose, you can't have an empty row every 2 rows, it's because of the autofilters you've applied, it's hard to know how far to go, but if you give me a workbook that's a bit more complete and realistic, I could review that.
      And explain what this formula is for, that's why I only took every two rows..
      =COUNTA(X4;AB4;AF4;AJ4;AN4;AR4;AV4)-COUNTA(X5;AB5;AF5;AJ5;AN5;AR5;AV5)
      I've already asked you this question but you didn't answer.
      Is the engine sheet finished or can it vary over time?
      --
      Experience teaches more surely than advice. (André Gide)
      -1
  18. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Haaaach.. Now I understand better, you changed the formula =COUNTA(X5;AB5;AF5;AJ5;AN5;AR5;AV5) which no longer references the next line and your 2nd line is initialized. The proof that it's necessary to provide as much information as possible and not erroneous. 
    Sub SplitData() Dim i As Long, e As Integer Dim TB Dim Ls TB = Array(16, 17, 18, 50, 22, 26, 30, 38, 42, 46) Ls = Array("C9", "D9", "A9", "F4") '--------------------------------------------- 'Column width For i = 1 To 7 Sheets("impression").Columns(i).ColumnWidth = Sheets("bon").Columns(i).ColumnWidth Next i '--------------------------------------------- For i = 4 To Range("AX1").SpecialCells(xlCellTypeLastCell).Row If Sheets("Liste moteur").Cells(i, 50) <> "" Then ' VideBon For e = 0 To 3 Sheets("bon").Range(Ls(e)) = Sheets("Liste moteur").Cells(i, TB(e)) Next e For e = 0 To 5 Sheets("bon").Range("B10").Offset(0, e) = Sheets("Liste moteur").Cells(i, TB(e + 4)) Next e '--------------------------------------------- 'Copy the good into the print page. Sheets("bon").Select Range("A4:G20").Select Selection.Copy Sheets("impression").Select Range("A65536").End(xlUp).Offset(1, 0).Select Selection.Insert Shift:=xlDown '--------------------------------------------- End If Next i Application.CutCopyMode = False Sheets("Liste moteur").Select Range("A1").Select End Sub


    See you later
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Thank you so much for your help, this time the macro works perfectly
      again a thousand apologies for my explanations which I admit were not always clear
      I am indebted to you and I owe part of the success of my internship to you

      thanks again and see you later
      -1
    2. caribou38 Posted messages 31 Status Member
       
      I'm sorry, but I can't assist with that.
      -1
  19. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Hello,
    Since I don't have your sheets, I couldn't really test 
    Sub validate() Dim RC As String Application.ScreenUpdating = False Sheets("BDD").Select On Error Resume Next 'if error on the Find Sheets("BDD").Select Cells.Find(What:=Sheets("input sheet").Range("J6").Value, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate On Error GoTo 0 'cancel error detection RC = ActiveCell.Offset(0, 1).Address & ":" & ActiveCell.Offset(0, 7).Address Range(RC).Copy Sheets("input sheet").Select Range("K17").Select ActiveSheet.Paste Application.ScreenUpdating = False End Sub

    See you later
    --
    Experience teaches more surely than advice. (André Gide)
    -1
    1. caribou38 Posted messages 31 Status Member
       
      Hello

      I replaced your macro with the one I had and it works perfectly, thank you

      I'm attaching my workbook if you have time to take a look
      some functions might glitch because I haven't included everything, it's 4MB

      I tried to place the buttons from the sheets into a toolbar but without success
      I only managed to crash everything

      Second point, the find functions crash if no result is found

      Thanks again for all the help you've provided

      https://www.cjoint.com/?igo3TGgddD
      -1