Query related to VBA with formula

[Excel]

Hello Everyone, 

I want to apply VLOOKUP FUNCTION formula in filtered cells only with the help of VBA code. So, what should i write VBA code to give VLOOKUP FUNCTION and it should be dynamic ??

Please help.

Here is a attached file.

[#2]

@Excel If you know anything about VBA already, then you are going to have to declare 5 array variable(s). For example, when you declare your DIM statement, here are the variables that I would declare:

Dim varDept as String()

Dim varEmpID as String()

Dim varFName as String()

Dim varLName as String()

Dim varPayRate as Currency()

Then, later, you will have to use the ReDim statement to walk through the columns on your Data spreadsheet and then read the values into each of the above declared array variables. You will require a loop variable to walk through each row.

After you have read the data into these array variables and have them stored in memory, you can then choose to output them to your other worksheet.

Thanks

[#3]

Sir, actually i am learning VBA.
Can you please full VBA code ? Please

[#4]

@Excel Start with this Microsoft Support link about arrays: https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-arrays

And then search from here within this site for ReDim Preserve as well.

This will get you started.

Thanks

[#5]

Sir, Can we do more dynamic ?

[#6]

@Excel , absolutely. 

Step 1: Declare and populate the input data (from your sheet) array variables with the ReDim statement in a loop.

Step 2: INSTEAD of the filter you applied in Excel, use an IF statement to select rows where certain criteria you want to filter on is met.

Step 3: INSIDE the IF statement, you will then have output array variables (that you also have to declare in Dim statements) that will capture the unique list of rows and data based on your filter criteria. You will need to use the ReDim Preserve statement to populate these unique, dynamics arrays that you will then output onto your other worksheet.

Chapter 9, page 187 of the Albright VBA for Modelers textbook listed at the bottom of this web page has the VBA structure that you will require to do what you need to do based on the steps I have described above: https://boxplot-outlier-data-analysis-templates.sellfy.store/outlier-data-analysis-resources/.

I believe the link leads to an eBook version.

Thanks

[#7]

Sir, can you please write VBA code ? Please.

[#8]

@Excel 

You should at least make an effort to write the code.

If you only ask others to write all the code for you, you will never learn.

[#9 · Accepted solution]

@Excel did the link to textbook I added to my first response to you help....

[#10]

I have done with the help of Macro recording.
Thank you so much sir.