Help With macro involving find info in a column and either adding information or looking up info on sheet 2

[zoog25]

Hello all,

I'm trying to write a macro that looks at a list of values in column A on sheet 1. The values are jobid numbers (for example 8001). Now if the number appears on the list, the macro should prompt the user to enter information to inputbox. 4 boxes in total. Otherwise the macro should tell the user that the id isn't located on that list and prompt them if they would like the information added from the master list (sheet 2). In prompted yes then the macro should look at sheet 2, find the job number and then copy and paste information into sheet 1 and be prompted after for the 4 inputbox data. I hope this is helpful. I've been trying but getting stuck on writing how to find the information. I keep getting all different sorts of errors if i use the .find.

 

[JLGWhiz]

Hello all,

I'm trying to write a macro that looks at a list of values in column A on sheet 1. The values are jobid numbers (for example 8001). Now if the number appears on the list, the macro should prompt the user to enter information to inputbox. 4 boxes in total. Otherwise the macro should tell the user that the id isn't located on that list and prompt them if they would like the information added from the master list (sheet 2). In prompted yes then the macro should look at sheet 2, find the job number and then copy and paste information into sheet 1 and be prompted after for the 4 inputbox data. I hope this is helpful. I've been trying but getting stuck on writing how to find the information. I keep getting all different sorts of errors if i use the .find.

This code is not intended to solve your entire problem, because there is a lot of information that is not provided in the OP. However, It will demonstrate how to use the Find function. The code assumes that the ID numbers are in column A on both sheets.

Code:
Sub search()
Dim sh As Worksheet, lr As Long, rng As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
fNumb = InputBox("Enter data to find", "SEARCH ITEM")
Set c = rng.Find(fNumb, LookIn:=xlValues)
If Not c Is Nothing then
data1 = InputBox("Enter data")
data2 = InputBox("Enter data")
data3 = InputBox("Enter data")
data4 = InputBox("Enter data")
'Add code to deal with input boxes
Else
Sec altSrch = Sheets(2).Range("A:A").Find(fNumb, LookIn:=xlValues)
If Not altSrch Is Nothing then
altSrch.EntireRow.Copy sh.Range("A" & lr + 1)
lr = lr + 1
End If
End If
'Not sure what you need to do at this point.
End Sub
Code:

 

[zoog25]

Thanks JLGWhiz. From the code you have put, i can see where i personally went wrong put your code should work. I'm going to test it right now.

 

[zoog25]

Hey JLGWhiz, your coding worked perfect. Here is the modification i made to your code. I ended up adding a third sheet because I was asked to by work.

Sub SOP()
 Dim sh As Worksheet, lr As Long, rng As Range, shw As Worksheet
 Dim PM As String, Project As String, Contractor As String
 
 Set sh = Sheets("Sophisticated Paint Jobs")
 
 Set shw = Sheets("Current Work Order List")
 
 lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
 
 Set rng = sh.Range("A7:A" & lr)
 
 Job = InputBox("Please Input JobNumber", "Job Number")
 
 Set c = rng.Find(Job, LookIn:=xlValues)
 
 If Not c Is Nothing Then
    Size = InputBox("What Is The Project Size (tons)?")
    Dates = InputBox("What Are The Dates Work Was Performed?")
    SurfacePrep = InputBox("What Was The Surface Prep?")
    PaintSys = InputBox("What Was The Paint System?")
        sh.Range("D" & c.Row).Value = Size
        sh.Range("E" & c.Row).Value = Dates
        sh.Range("F" & c.Row).Value = SurfacePrep
        sh.Range("G" & c.Row).Value = PaintSys
 Else
    If Len(Job) > 4 Then
        Set altSrch = shw.Range("C:C").Find(Job, LookIn:=xlValues)
        n = 1
    Else
        Set altSrch = Sheets("Condensed Job List").Range("A:A").Find(Job, LookIn:=xlValues)
        n = 0
    End If
    
    If Not altSrch Is Nothing Then
    
        If n = 0 Then
            With Sheets("Condensed Job List")
                PM = .Range("E" & altSrch.Row).Value
                Project = .Range("B" & altSrch.Row).Value
                Contractor = .Range("C" & altSrch.Row).Value
            End With
        Else
            With shw
                PM = .Range("B" & altSrch.Row).Value
                Project = .Range("E" & altSrch.Row).Value
                Contractor = .Range("D" & altSrch.Row).Value
            End With
        End If
        
        sh.Range("A" & lr + 1).Value = Job
        sh.Range("B" & lr + 1).Value = PM
        sh.Range("C" & lr + 1).Value = Contractor & " " & "-" & " " & Project
        ' Remaining spots
            Size = InputBox("What Is The Project Size (tons)?")
            Dates = InputBox("What Are The Dates Work Was Performed?")
            SurfacePrep = InputBox("What Was The Surface Prep?")
            PaintSys = InputBox("What Was The Paint System?")
                sh.Range("D" & lr + 1).Value = Size
                sh.Range("E" & lr + 1).Value = Dates
                sh.Range("F" & lr + 1).Value = SurfacePrep
                sh.Range("G" & lr + 1).Value = PaintSys
    End If
 End If
End Sub

It works, though looks a little messy. Oh well. Thanks for your help.

 

[JLGWhiz]

Its not all that messy. All you needed was a little help over the humps and you turned out a product. Can't beat that.
Regards, JLG

 

[zoog25]

Thanks JLGWhiz, Now I need to add all this coding to a shared workbook and add several other macros to save the company hours of work. Man I love vba. This is now my second successful program for the company.