Search data and calculate with VBA UserForm

JustoneE

New Member
Joined
Dec 7, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everyone, its my first time using VBA and any sort of coding in general so I have been cracking my head for weeks trying to do this UserForm . I'm wondering if there is anyone here that could help me with it? Any help is appreciated, Thank you in advance!!

I have 2 main issues that I've been trying to figure out,

1: I've created a UserForm to key in data into the excel spreadsheet. There is Name, Product, Date and Quantity. I would like the last column to be Time Taken ( which is Quantity*standard time ) The Data for the standard time is in another sheet ( Sheet2 ) and to get the standard time, I would have to identify first from which group does the product lies, as the standard time is tied to the group. I am able to do it with excel formula ( IFS ) quite easily but I would like to do it with VBA so whoever uses it wouldn't accidentally delete or change the formula. Is there a way to do so? I'll attach the screenshot of the excel formula below.

The Excel formula that I would like to change to VBA and the reference sheet (Sheet2) that states the group and standard time
IFS function.png
reference sheet.png

Not sure if this is needed but, here's the UserForm I use to key in data to the excel sheet
Dataentry UserForm.png


2: After calculating the time taken above, I would like to calculate the total time taken by Name and Date. I'm creating a separate UserForm to search for specific name and date and calculate a certain figure. Once the search button (cmdSearch) is clicked, the textbox for total review DHR (txtDHR) will immediately show the number. At the same time, if it is possible I would like the spreadsheet to automatically update itself at the total review DHR. The date stated will be everyday of the month, for example 2021-10-01, 2021-10-02 and etc , and I would need to do it for every name listed. I'm not very good at explaining so I'm uploading the code below and the screenshot up here. I found the code online and tried to change here and there to fit my spreadsheet but I still couldn't get it.

Total DHR userform.png

The whole excel sheet
Full Data Sheet.png


VBA Code:
Private Sub cmdSearch_Click()
Dim arr, Name As Name, Dat As Date, Dat$, rng As Range, Name As Range, Name$, Quantity As Range
arr = Array("Peter", "Armin", "Vanessa", "Savannah") & Sheets("Sheet1").Cells(r, 8)
Name = Sheets("Sheet1").Cells(r, 2)
Dat = Sheets("Sheet1").Cells(r, 3)
Quantity = Sheets("Sheet1").Cells(r, 5)

With Sheets(Dat)
  Set rng = .Rows(8).Find(Dat, lookat:=xlWhole)
  If Not rng Is Nothing Then
    Set rng = .Column(9).Find(Peter, lookat:=xlWhole)
    If Not rng Is Nothing Then
        Set Quantity = .Rows(9).Find(Peter, lookat:=xlWhole)
        If Not Name Is Nothing Then
     
      .Cells(Name.Row, rng.Column + 1) = cboName
       txtDate.Value = Format(txtDate.Value, "yyyy-mm-dd")
      .Cells(mch.Row, rng.Column + 2) = TextBox2
      .Cells(mch.Row, rng.Column + 3) = TextBox3

        Else
            MsgBox "can not find product"
    End If
    Else
        MsgBox "can not find date"
  End If
End With
Me.Hide
End Sub


Thank you so much for reading and helping me. I appreciate every help I can find here!!
 

Attachments

  • Full datasheet.png
    Full datasheet.png
    43.8 KB · Views: 40

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top