[Solved] Find value from Range

[mfaisal.ce]

Dear,

I have a file with 02 sheets,
1. Daily
2. R302

I need to find data from Sheet 2 based on DATE from Sheet 1 and fill the Column B & C in Sheet 1 if Reactor "A" else in Column E & F in sheet 1.

I am trying to use the following command but unfortunately i am not able to find Two criteria values,

Set Rng_Name = Sheets("R302").Range("B").Find(What:=Int(Date))

above code works fine but i need to combine Date along with Reactor value, so the array will be unique even at same date there is value for reactor A and B like 20 June & 24 June.





Regards,

 

[Md Junaed]

Dear mfaisal.ce,

Thank you for your query. I understand you want to separate data based on the Reactor type. You may use the XLOOKUP function to execute your desired work.

Please use the formula given below to get the perfect results:
Formula for batch values of Reactor A:
=XLOOKUP(A2&"A",'R302'!$B$3:$B$18 & 'R302'!$D$3:$D$18,'R302'!$C$3:$C$18,"")
Copy the formula and paste it into cell B2. Use Fill Handle for the other cells as well. To get the value whether it is in the Reactor A, use the following formula in C2.
=IF(B2="","","A")
We will use the same formula for the Reactor B but we will modify the formula slightly for the result.
=XLOOKUP(A2&"B",'R302'!$B$3:$B$18 & 'R302'!$D$3:$D$18,'R302'!$C$3:$C$18,"")
In the same way write down the formula below to get Reactor type in cell F2 and use Fill Handle.
=IF(E2="","","B")

I am attaching the Excel file for your better understanding.

Best Regards,
Md Junaed

 

[mfaisal.ce]

Dear,
Thanks for the help but i need the formula in vb script not in excel sheet. I know how to use find single

Dear mfaisal.ce,

Thank you for your query. I understand you want to separate data based on the Reactor type. You may use the XLOOKUP function to execute your desired work.

Please use the formula given below to get the perfect results:
Formula for batch values of Reactor A:
=XLOOKUP(A2&"A",'R302'!$B$3:$B$18 & 'R302'!$D$3:$D$18,'R302'!$C$3:$C$18,"")
Copy the formula and paste it into cell B2. Use Fill Handle for the other cells as well. To get the value whether it is in the Reactor A, use the following formula in C2.
=IF(B2="","","A")
We will use the same formula for the Reactor B but we will modify the formula slightly for the result.
=XLOOKUP(A2&"B",'R302'!$B$3:$B$18 & 'R302'!$D$3:$D$18,'R302'!$C$3:$C$18,"")
In the same way write down the formula below to get Reactor type in cell F2 and use Fill Handle.
=IF(E2="","","B")

I am attaching the Excel file for your better understanding.

Best Regards,
Md Junaed

Dear,

Many thanks for the help but i require code in vb script and not by excel sheet. I know how to define range with 01 criteria but I am not able to make multiple criteria; following code is working with only one condition;

Set RngName= Sheets("R302").Range("B").Find(What:=Int(.Sheets("Daily").Range("A2").Value ))

This code works fine but how to add "Reactor Column from R302 Sheet " in the condition to specify the range correctly.

Why to specify the 2nd condition is because of multiple Reactor values at same date.


Hopefully, I highlighted my problem in detail.

Regards.

 

[Md Junaed]

Dear mfaisal.ce,

According to your need , I am giving you a VBA code to get your desired result. Copy and Paste it into your VBA Editor than run it.

Sub separate()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("R302")
Set ws2 = ThisWorkbook.Sheets("Daily")
lastRow = ws1.Cells(ws1.Rows.Count, 4).End(xlUp).Row
lastRow1 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow
If ws1.Cells(i, 4) = "A" Then
Batch = ws1.Cells(i, 3)
Date_val = ws1.Cells(i, 2)
For j = 2 To lastRow1
If ws2.Cells(j, 1) = Date_val Then
ws2.Cells(j, 2) = Batch
ws2.Cells(j, 3) = "A"
Exit For
End If
Next j
ElseIf ws1.Cells(i, 4) = "B" Then
Batch = ws1.Cells(i, 3)
Date_val = ws1.Cells(i, 2)
For j = 2 To lastRow1
If ws2.Cells(j, 1) = Date_val Then
ws2.Cells(j, 5) = Batch
ws2.Cells(j, 6) = "B"
Exit For
End If
Next j
End If
Next i
End Sub


I am attaching the Excel file for your better understanding.

Best Regards,
Md Junaed

 

[mfaisal.ce]

Dear,

Bundle of thanks, it worked. but my question still remains the same, is it not possible to set a range with multiple find criteria.

The code you suggested is perfect, consider the R302 have more than 1000 records then loop will take more time in processing as compared to find record technique. Moreover, it will be a single line code while the code you suggested is bigger one.

Sorry, but if i can know how to use multiple criteria find in range is more preferable for me.

regards,

 

[Md Junaed]

Hello mfaisal.ce,

Hope you are well. I am giving the VBA code in the Excel file according to your need. Hope it will be fine for you.

If you have further queries, let us know.

Regards
MD Junaed