Create an input box to search for a value
Solved
caribou38
Posted messages
31
Status
Membre
-
caribou38 Posted messages 31 Status Membre -
caribou38 Posted messages 31 Status Membre -
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
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 réponses
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)
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)
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
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
You do not specify the coordinates but I have deduced..
You say
--
Experience teaches more surely than advice. (André Gide)
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)
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+
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+
What’s your 'good', a sheet? a UserForm?
Show the macro you started for that.
--
Experience teaches more surely than advice. (André Gide)
Show the macro you started for that.
--
Experience teaches more surely than advice. (André Gide)
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 +
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 +
For your first error, you need to add an On error,
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.
Otherwise, you explain the second one better.
--
Experience teaches more surely than advice. (André Gide)
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)
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.
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.
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)
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)
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
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
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)
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)
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
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
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.
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.
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)
If it's not quite right, just let me know...
Your binder
--
Experience teaches more surely than advice. (André Gide)
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"
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"
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)
So look a bit... Your turn to play.
--
Experience teaches more surely than advice. (André Gide)
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)
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)
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
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
We'll have to find a solution..
try this..
'
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)
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)
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)
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)
I reread the error message you gave... it seems there’s an error with the objects.
Replace the line copy with
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)
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)
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)
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)
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
I'm giving you back the spreadsheet; I left the error so you can see it.
https://www.cjoint.com/?idvunZFA47
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)
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)
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)
https://www.cjoint.com/?iehNn0c54X
See you!
--
Experience teaches more surely than advice. (André Gide)
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
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
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
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
No, take back this workbook then, and add below...
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)
'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)
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
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
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)
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)
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.
See you later
--
Experience teaches more surely than advice. (André Gide)
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)
Hello,
Since I don't have your sheets, I couldn't really test
See you later
--
Experience teaches more surely than advice. (André Gide)
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)
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
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
I am working on an Excel file and I need to create macros.