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

zoog25

Active Member
Joined
Nov 21, 2011
Messages
376
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Nov 21, 2011
Messages
376
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

Active Member
Joined
Nov 21, 2011
Messages
376
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.
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Nov 21, 2011
Messages
376
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,054
Messages
5,599,533
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top