VBA Question - VBA for validating value from multiple cells/column and fetching value another sheet's cell and pasting it another cell of sheet

rsolanki

New Member
Joined
Jul 30, 2024
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello All,

I am new to VBA and i am working on one personal project, I tried googling my query but haven't found any solution.

I have one Table consisting Name of Doctors, Services they provided, Amount that we collected for their service and type of patient. In another sheet we have percentage which doctor should be getting for their service but it is based on few criteria such as "If Dr. ABC perform Service 1 for Patient Type A than he will get 50 and if Dr. ABC perform Service 1 for Patient Type B then he will get 100" or "If Dr. XYZ perform Service 1 for Patient Type A than he will get 60 and if Dr. XYZ perform service 1 for patient type B then he will get 80" Please refer to below sheets to undersand the what exactly i am trying to achieve.

Sheet 1:

Doctor NameName of ServiceService AmountPatient Type
Dr. ABCService 1100Type A
Dr. XYZService 2200Type B
Dr. EFGService 3300Type A
Dr. XYZService 1100Type A
Dr. ABCService 1100Type B

Sheet 2:

Doctor NameServicesPatient TypeDoctor Percentage
Dr. ABCService 1Type A
50​
Dr. ABCService 1Type B
100​
Dr. ABCService 2Type A
60​
Dr. ABCService 2Type B
80​
Dr. ABCService 3Type A
40​
Dr. ABCService 3Type B
70​
Dr. XYZService 1Type A
40​
Dr. XYZService 1Type B
60​
Dr. XYZService 2Type A
70​
Dr. XYZService 2Type B
100​
Dr. XYZService 3Type A
50​
Dr. XYZService 3Type B
80​

Now i am looking for VBA Macro which can search for Specific Doctor Name, Service and Patient Type and Insert Doctor Percent in last column of Sheet1

End Result

Doctor NameName of ServiceService AmountPatient TypeDoctor Percentage
Dr. ABCService 1100Type A
50​
Dr. XYZService 2200Type B
100​
Dr. EFGService 3300Type A
50​
Dr. XYZService 1100Type A
40​
Dr. ABCService 1100Type B
100​


Please note: Above given are example, actually there are too many Columns and Rows but I am trying to learn and implement this Macro in my excel to automate the task.

Thanks in advance

R Solanki
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
The easy way will be by:
Use XLOOKUP inside VBA as you would go in a workbook. To have it speed optimized, I load both input and output data tables into arrays, do all operations on arrays stored in memory, and finally write array back to a workbook.

VBA Code:
Sub FillColEBasedOnSheet2()
Dim params_for_percentage As Variant, percentage As Variant, input_data As Variant
Dim output_data As Variant, treatment_details As Variant, i As Long, lr As Long
With Sheets("Sheet2")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  input_data = .Range("A2:D" & lr).Value
  ReDim params_for_percentage(1 To lr - 1)
  ReDim percentage(1 To lr - 1)
  For i = 1 To lr - 1
    params_for_percentage(i) = input_data(i, 1) & ";" & input_data(i, 2) & ";" & input_data(i, 3)
    percentage(i) = input_data(i, 4)
  Next i
End With
With Sheets("Sheet1")
  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  output_data = .Range("A2:E" & lr).Value 'note that column E is empty at the beginning
  For i = 1 To lr - 1
    output_data(i, 5) = WorksheetFunction.XLookup(output_data(i, 1) & ";" & output_data(i, 2) & ";" & output_data(i, 4), params_for_percentage, percentage, "N/A", 0)
  Next i
  .Range("A2:E" & lr).Value = output_data
End With
End Sub
 
Upvote 0
Just in case - my test file screenshot to make fitting code to your data easier. Note that last row in both sheets is calculated automatically (I assumend that there is no other content in cells below tables in both sheets
 

Attachments

  • Zrzut ekranu 2024-07-30 150919.png
    Zrzut ekranu 2024-07-30 150919.png
    90.3 KB · Views: 7
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,580
Members
452,927
Latest member
whitfieldcraig

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