Macro/VBA For gathering info

[Antonow]

Hello World!

Well, it's been a while since i've been messing with excel VBA and Macros and now I need to develop something thus..
I need the help of the best ones there is!

Basically I have A Data worksheet, with several informations.
Like, factory code, dealer code, Number of the product, mainteneance.. stuff like that.
I need to make a new tab with something like, you input the Factory code, or the Products code and hits this search button.

The final product should give you all the info you need in that tab without having to go in the other ones. Like I can't remember how to make a VBA that looks in every line of a certain range and gives me the info I need.
I've tried to do it and it searches only the first line of every worksheet
Plus, i need to implement the possibility to add a new input, select in what worksheet it should go and press save.

I've done something like it but it was really waaaay back there.. can't remember how I've done it

I know it's a long shot, with a lot of try and error.. But hopefully we will be able to do it!

I will post more info about the worksheet here later. end of the month is a mess for everyone heh.

But I would be glad if you guys helped me out with some "generic" code for that.

Thank you all in advance!

 

[NdNoviceHlp]

I'm sure someone will help. Please post where the data is and how U want to display/store the output (ie. sheet name(s), columns and rows). HTH. Dave

 

[ranman256]

how bout a form, the a combo box to pick CodeType: Factory, Product, or Dealer
a text box for the code
a button to 'find'

at button click event:

sub btnFind_click()
on error goto errFind

sheets(cboCodeType).select
range("A1").select

    Columns("A:A").Select
    Selection.Find(What:=txtCode, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

   if activecell.value  = txtCode then
         'collect values in this row
              'x = activecell.value 

         'make new sheet
       sheets.add

         'paste data to new tab
   endif
errFind:
end sub

 

[Antonow]

[TABLE="width: 1556"]
<tbody>[TR]
[TD]Dealer Code[/TD]
[TD]Description[/TD]
[TD]Factory code[/TD]
[TD]Project[/TD]
[TD]Factory[/TD]
[TD]Status[/TD]
[TD]Vulnerability[/TD]
[TD]Family[/TD]
[TD]Liberação Para produção[/TD]
[TD]Critical product[/TD]
[TD]Critical Proccess[/TD]
[/TR]
[TR]
[TD]ZM001077[/TD]
[TD]Thermostat button MOULD ZM001077 ZM001077[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]F1[/TD]
[TD]AC[/TD]
[TD]Não[/TD]
[TD]F100[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

This is an example of what I have in this worksheet called (Moulds list), this is one product out of 237.
Basically what I need is a macro that is able to both get the information of the Mould or factory code typed in this cell, and to update and add another product if needed.
Like, The very first worksheet is "Information" where I have cells to input either Mould number or Factory code, a button, several other cell that will contain the information about this Product or mould.

Like this.

[TABLE="width: 797"]
<tbody>[TR]
[TD="colspan: 2"]Dealer Code[/TD]
[TD="colspan: 2"]ZM001077[/TD]
[TD][/TD]
[TD]Search Button[/TD]
[TD][/TD]
[TD]Reset fields[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Factory code[/TD]
[TD="colspan: 2"]16[/TD]
[TD][/TD]
[TD]Update Button[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Description[/TD]
[TD="colspan: 10"]Thermostat button MOULD ZM001077 ZM001077[/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]Tool Station[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Critical product[/TD]
[TD="align: right"]1[/TD]
[TD="colspan: 2"]Control Level[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Factory[/TD]
[TD="colspan: 2"]F1[/TD]
[TD="colspan: 2"]SO Tool Station[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 2"]Critical Proccess[/TD]
[TD="align: right"]1[/TD]
[TD="colspan: 2"]Maintenance complexity[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD="colspan: 2"]AC[/TD]
[TD="colspan: 2"]Hot chamber code[/TD]
[TD]Hc321[/TD]
[TD="colspan: 2"]Quality History[/TD]
[TD="align: right"]1[/TD]
[TD="colspan: 2"]Preventive level[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Family[/TD]
[TD="colspan: 2"]F100[/TD]
[TD="colspan: 2"]Hot Chamber dealer[/TD]
[TD]HRS[/TD]
[TD="colspan: 2"]Volume[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This information it gets from the moulds list worksheet

 

[NdNoviceHlp]

Again, "Please post where the data is and how U want to display/store the output (ie. sheet name(s), columns and rows). " Dave

 

[Antonow]

Ok! Here it goes.

So, i've made an Input sheet. Where you enter a code in the Cell "C6" and using a VLOOKUP it will gather all the info from that Mould and place it where it has to be.
To make it easier to understand I've separated into information field and update field.
This spreadsheet (Information field), is locked, the only cell you can change is "C6" to enter the code and the Vlookup will get the data. Cells Will be shown like ("C#")

[TABLE="width: 1643"]
<tbody>[TR]
[TD="colspan: 2"]Fact. Code"A6")[/TD]
[TD="colspan: 2"]Enter Code("C6")[/TD]
[TD="colspan: 2"]Description"E6")[/TD]
[TD="colspan: 10"]Water cooler shelter ("G6")[/TD]
[TD="colspan: 2"]Project"Q6")[/TD]
[TD="colspan: 2"]Dinamarca("S6")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Supplier Code("A7")[/TD]
[TD="colspan: 2"]ZM223252("C7")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Factory("A8")[/TD]
[TD="colspan: 2"]0("C8")[/TD]
[TD="colspan: 2"]S.O Tooling("E8")[/TD]
[TD="colspan: 2"]0("G8")[/TD]
[TD="colspan: 2"] Action("i8")[/TD]
[TD="colspan: 2"]0("k8")[/TD]
[TD="colspan: 2"]Production Release("M8")[/TD]
[TD="colspan: 2"]0("O8")[/TD]
[TD="colspan: 2"]Control level("Q8")[/TD]
[TD="colspan: 2"]0("S8")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Status("A9")[/TD]
[TD="colspan: 2"]Active("C9")[/TD]
[TD="colspan: 2"]H.C. Code("E9")[/TD]
[TD="colspan: 2"]0("G9")[/TD]
[TD="colspan: 2"] Cost ("i9")[/TD]
[TD="colspan: 2"]0("k9")[/TD]
[TD="colspan: 2"]Product Criticy("M9")[/TD]
[TD="colspan: 2"]0("O9")[/TD]
[TD="colspan: 2"]Meintenance Complexity("Q9")[/TD]
[TD="colspan: 2"]0("S9")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Vulnerability("A10")[/TD]
[TD="colspan: 2"]No("C10")[/TD]
[TD="colspan: 2"]H.C. Manufacturer("E10")[/TD]
[TD="colspan: 2"]0("G10")[/TD]
[TD="colspan: 2"] Internal/External Service ("i10")[/TD]
[TD="colspan: 2"]0("k10")[/TD]
[TD="colspan: 2"]Proccess Criticy("M10")[/TD]
[TD="colspan: 2"]0("O10")[/TD]
[TD="colspan: 2"]Preventive level("Q10")[/TD]
[TD="colspan: 2"]0("S10")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Family("A11")[/TD]
[TD="colspan: 2"]F600("C11")[/TD]
[TD="colspan: 2"]H.C. Diameter("E11")[/TD]
[TD="colspan: 2"]0("G11")[/TD]
[TD="colspan: 2"]Execution Time (Internal Tooling)("i11")[/TD]
[TD="colspan: 2"]0("k11")[/TD]
[TD="colspan: 2"]Quality History("M11")[/TD]
[TD="colspan: 2"]0("O11")[/TD]
[TD="colspan: 2"]% Recycled drawing("Q11")[/TD]
[TD="colspan: 2"]0("S11")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Tooling("A12")[/TD]
[TD="colspan: 2"]0("C12")[/TD]
[TD="colspan: 2"]Pendency("E12")[/TD]
[TD="colspan: 2"]0("G12")[/TD]
[TD="colspan: 2"]Tooling Release("i12")[/TD]
[TD="colspan: 2"]0("k12")[/TD]
[TD="colspan: 2"]Volume("M12")[/TD]
[TD="colspan: 2"]0("O12")[/TD]
[TD="colspan: 2"]Hidraulic hitch("Q12")[/TD]
[TD="colspan: 2"]0("S12")[/TD]
[/TR]
</tbody>[/TABLE]

And it copies it to the (Update field) cells, shown below in the same sheet (input), so if you need to update, you change it and press the button "Update".
Like this:

[TABLE="width: 1643"]
<tbody>[TR]
[TD="colspan: 2"]Fact. Code"A14")[/TD]
[TD="colspan: 2"]Enter Code("C14")[/TD]
[TD="colspan: 2"]Description"E14")[/TD]
[TD="colspan: 10"]Water cooler shelter ("G14")[/TD]
[TD="colspan: 2"]Project"Q14")[/TD]
[TD="colspan: 2"]Dinamarca("S14")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Supplier Code("A15")[/TD]
[TD="colspan: 2"]ZM223252("C15")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Factory("A16")[/TD]
[TD="colspan: 2"]0("C16")[/TD]
[TD="colspan: 2"]S.O Tooling("E16")[/TD]
[TD="colspan: 2"]0("G16")[/TD]
[TD="colspan: 2"] Action("i16")[/TD]
[TD="colspan: 2"]0("k16")[/TD]
[TD="colspan: 2"]Production Release("M16")[/TD]
[TD="colspan: 2"]0("O16")[/TD]
[TD="colspan: 2"]Control level("Q16")[/TD]
[TD="colspan: 2"]0("S16")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Status("A17")[/TD]
[TD="colspan: 2"]Active("C17")[/TD]
[TD="colspan: 2"]H.C. Code("E17")[/TD]
[TD="colspan: 2"]0("G17")[/TD]
[TD="colspan: 2"] Cost ("i17")[/TD]
[TD="colspan: 2"]0("k17")[/TD]
[TD="colspan: 2"]Product Criticy("M17")[/TD]
[TD="colspan: 2"]0("O17")[/TD]
[TD="colspan: 2"]Meintenance Complexity("Q17")[/TD]
[TD="colspan: 2"]0("S17")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Vulnerability("A18")[/TD]
[TD="colspan: 2"]No("C18")[/TD]
[TD="colspan: 2"]H.C. Manufacturer("E18")[/TD]
[TD="colspan: 2"]0("G18")[/TD]
[TD="colspan: 2"] Internal/External Service ("i18")[/TD]
[TD="colspan: 2"]0("k18")[/TD]
[TD="colspan: 2"]Proccess Criticy("M18")[/TD]
[TD="colspan: 2"]0("O18")[/TD]
[TD="colspan: 2"]Preventive level("Q18")[/TD]
[TD="colspan: 2"]0("S18")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Family("A19")[/TD]
[TD="colspan: 2"]F600("C19")[/TD]
[TD="colspan: 2"]H.C. Diameter("E19")[/TD]
[TD="colspan: 2"]0("G19")[/TD]
[TD="colspan: 2"]Execution Time (Internal Tooling)("i19")[/TD]
[TD="colspan: 2"]0("k19")[/TD]
[TD="colspan: 2"]Quality History("M19")[/TD]
[TD="colspan: 2"]0("O19")[/TD]
[TD="colspan: 2"]% Recycled drawing("Q19")[/TD]
[TD="colspan: 2"]0("S19")[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Tooling("A20")[/TD]
[TD="colspan: 2"]0("C20")[/TD]
[TD="colspan: 2"]Pendency("E20")[/TD]
[TD="colspan: 2"]0("G20")[/TD]
[TD="colspan: 2"]Tooling Release("i20")[/TD]
[TD="colspan: 2"]0("k20")[/TD]
[TD="colspan: 2"]Volume("M20")[/TD]
[TD="colspan: 2"]0("O20")[/TD]
[TD="colspan: 2"]Hidraulic hitch("Q20")[/TD]
[TD="colspan: 2"]0("S20")[/TD]
[/TR]
</tbody>[/TABLE]

When the update button is pressed, it will get the information from C14:C:20, G14, G16:G20, K16:K20, O16:O20, S16:S20(underlined), and what I need the macro to do is to search for the Fact. Code from ("C14" Input) in ("A2:A243" Moulds list) and update the info that has been changed. (By update the info, i mean just to copy all the information that has been placed in the "Update field" mentioned before.)

Sheet Moulds List cells:

[TABLE="width: 1204"]
<tbody>[TR]
[TD]Fact. Code("A1")[/TD]
[TD]Description("B1")[/TD]
[TD]Supplier Code ("C1")[/TD]
[TD]Project("D1")[/TD]
[TD]Factory("E1")[/TD]
[TD]Status("F1")[/TD]
[TD]Vulnerability("G1")[/TD]
[TD]Family("H1")[/TD]
[TD]Tooling("i1")[/TD]
[TD]S.O. Tooling("J1")[/TD]
[TD]H.C. Code("K1")[/TD]
[TD]H.C. Manufacturer("L1")[/TD]
[TD]H.C. Diameter("M1")[/TD]
[TD]Pendency("N1")[/TD]
[TD]Action("O1")[/TD]
[TD] Cost("P1")[/TD]
[TD]Internal/External Service("Q1")[/TD]
[TD]Execution Time (Internal Tooling)("R1")[/TD]
[TD]Tooling Release("S1")[/TD]
[TD]Production Release("T1")[/TD]
[TD]Product Criticy("U1")[/TD]
[TD]Proccess Criticy("V1")[/TD]
[TD]Quality History("W1")[/TD]
[TD]Volume("X1")[/TD]
[TD]Control level("Y1")[/TD]
[TD]Maintenance Complexity("Z1")[/TD]
[TD]Preventive level("AA1")[/TD]
[TD]% Recycled drawing("AB1")[/TD]
[TD]Hidraulic hitch("AC1")[/TD]
[/TR]
</tbody>[/TABLE]


If it's a bit confusing, i'm sorry, English is not my mother language..
Just message me and I will be pleased to try to explain it better

 

[Antonow]

And if possible, for new Moulds (Products), use the update field to make the input of that new Mould in the Moulds list. Thank you all for the help in advance

 

[NdNoviceHlp]

Maybe trial this and see if this is close. Dave

Sub test()
Dim Lastrow As Integer, Cnt As Integer
'data in "A2:A" & lastrow
With Sheets("Moulds")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To Lastrow
If Sheets("Input").Range("C" & 14).Value = Sheets("Moulds").Range("A" & Cnt).Value Then
'move data here
'eg for description
Sheets("Input").Range("E" & 14).Value = Sheets("Moulds").Range("B" & Cnt).Value
'etc.
Exit For
End If
Next Cnt
End Sub

 

[Antonow]

Thanks for the fast reply!

Got a couple things to adjust tho.
The code works, but it's doing the opposite, instead of updating the info in the "Moulds" it is looking for the info available in the "Moulds" and updating it in the "Input".
Here are some images.

https://imgur.com/GQPLSNo
In here I use the Data check field to gather the info from the "Moulds" using VLOOKUP, once i have the info, I hit the button Copy to Update field. Where it copie and paste the info to the field below, in order for you to update what you need.

https://imgur.com/bmqEoFK
In here, I've updated the info needed. Next step should be click in the update button.

https://imgur.com/t9DULjo
Once the update button is pressed, the information that i've just entered is gone. It hasn't copied it to the "Moulds" sheet, instead, it checked the info in there and copied to the update field.

As you can see in the images and brief explanation, I need to use that Update Info Field to input the new information and then when I hit the Update button it updates that in the "Moulds". I've tried to update it in the "Moulds" tab manually and pressing update, it then gathered the info and placed it into "Input" Data check field.

I'm trying to adjust it, but no success so far.
Can you help me out?

 

[Antonow]

I've managed to fix it
The only thing I need now is this: If the "Fact. Code" is not found in "Moulds" Open a popup asking if it's a new code. If you Hit "Yes" it will add it into that tab "Moulds" under the last one available and if you hit "No" it will say something like "Invalid Code" and do nothing.

Is it possible? to do so?

Here's the code i have now:

Sub Update()Dim Lastrow As Integer, Cnt As Integer
'data in "A2:A" & lastrow
With Sheets("Moulds")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 2 To Lastrow
If Sheets("Input").Range("C" & 14).Value = Sheets("Moulds").Range("A" & Cnt).Value Then
'move data here
'Supplier Code
Sheets("Moulds").Range("C" & Cnt).Value = Sheets("Input").Range("C" & 15).Value
'Factory
Sheets("Moulds").Range("E" & Cnt).Value = Sheets("Input").Range("C" & 16).Value
'Status
Sheets("Moulds").Range("F" & Cnt).Value = Sheets("Input").Range("C" & 17).Value
'Vulnerability
Sheets("Moulds").Range("G" & Cnt).Value = Sheets("Input").Range("C" & 18).Value
'Family
Sheets("Moulds").Range("H" & Cnt).Value = Sheets("Input").Range("C" & 19).Value
'Tooling
Sheets("Moulds").Range("I" & Cnt).Value = Sheets("Input").Range("C" & 20).Value
'Description
Sheets("Moulds").Range("B" & Cnt).Value = Sheets("Input").Range("G" & 14).Value
'S.O. Tooling
Sheets("Moulds").Range("J" & Cnt).Value = Sheets("Input").Range("G" & 16).Value
'H.C. Code
Sheets("Moulds").Range("K" & Cnt).Value = Sheets("Input").Range("G" & 17).Value
'H.C. Manufacturer
Sheets("Moulds").Range("L" & Cnt).Value = Sheets("Input").Range("G" & 18).Value
'H.C. Diameter
Sheets("Moulds").Range("M" & Cnt).Value = Sheets("Input").Range("G" & 19).Value
'Pendency
Sheets("Moulds").Range("N" & Cnt).Value = Sheets("Input").Range("G" & 20).Value
'Action
Sheets("Moulds").Range("O" & Cnt).Value = Sheets("Input").Range("K" & 16).Value
'Cost
Sheets("Moulds").Range("P" & Cnt).Value = Sheets("Input").Range("K" & 17).Value
'Internal/External Service
Sheets("Moulds").Range("Q" & Cnt).Value = Sheets("Input").Range("K" & 18).Value
'Execution Time (Internal Tooling)
Sheets("Moulds").Range("R" & Cnt).Value = Sheets("Input").Range("K" & 19).Value
'Tooling release
Sheets("Moulds").Range("S" & Cnt).Value = Sheets("Input").Range("K" & 20).Value
'Production release
Sheets("Moulds").Range("T" & Cnt).Value = Sheets("Input").Range("O" & 16).Value
'Product Criticy
Sheets("Moulds").Range("U" & Cnt).Value = Sheets("Input").Range("O" & 17).Value
'Proccess Criticy
Sheets("Moulds").Range("V" & Cnt).Value = Sheets("Input").Range("O" & 18).Value
'Quality history
Sheets("Moulds").Range("W" & Cnt).Value = Sheets("Input").Range("O" & 19).Value
'Volume
Sheets("Moulds").Range("X" & Cnt).Value = Sheets("Input").Range("O" & 20).Value
'Project
Sheets("Moulds").Range("D" & Cnt).Value = Sheets("Input").Range("S" & 14).Value
'Control Level
Sheets("Moulds").Range("Y" & Cnt).Value = Sheets("Input").Range("S" & 16).Value
'Mainance complexity
Sheets("Moulds").Range("Z" & Cnt).Value = Sheets("Input").Range("S" & 17).Value
'Preventive Level
Sheets("Moulds").Range("AA" & Cnt).Value = Sheets("Input").Range("S" & 18).Value
'%Recycled drawing
Sheets("Moulds").Range("AB" & Cnt).Value = Sheets("Input").Range("S" & 19).Value
'Hidraulich hitch
Sheets("Moulds").Range("AC" & Cnt).Value = Sheets("Input").Range("S" & 20).Value
'etc.
Exit For
End If
Next Cnt
    ActiveSheet.Unprotect "Pass"
Selection.ClearContents
    Range("C14:D14").Select
    Range("C14:D20,G16:H20,K16:L20,O16:P20,S14:T20,G14:P15").Select
    Range("G14").Activate
    Selection.ClearContents
    Range("C14:D14").Select
    ActiveSheet.Protect "Pass", True, True
End Sub

Thank you once again!!