I have a problem in VBA that I'm not sure how to do as I'm quite new to VBA, would greatly appreciate if anybody could guide me on it. So the problem for some of you python coders out there is simply doing DataFrame.loc[DataFrame['Name']=='John'][-1]. Here is what I mean:

This is what I have in the spreadsheet let's say from cell range (A1:E3)

On a separate sheet, I have 2 buttons and 2 cells

1. Add information to database (built in VBA)
2. Extract latest information to database
3. First cell allows me to enter the name such as 'John'
4. Second cell that allows me to enter the Bio (or equivalently extract the bio to)

How could I build a code in VBA such that let's say if I type John, and I click the button to extract the bio, I will be able to get the latest Bio (the one dated 01/22) for John into another cell?

Thanks

CodePudding user response：

If you got an older version of Excel you may do this with SUMPRODUCT:

=SUMPRODUCT(MAX(--(A2:A5=G5)*C2:C5))

In Excel 365, you can use function MAXIFS:

MAXIFS function

CodePudding user response：

Hi I figured out a solution for anybody else looking for something similar and have closed this query.

if we set up a data array that can be used to store the table in the sheet range. Then we can loop through the data as such:

Dim rowCount
Dim dataArray As Variant
Dim bio

For i = 1 To rowCount
  
    If dataArray(i, 1) = Name Then
        bio = dataArray(i, 2)
    End If
Next
Sheets("Main").Range("A5") = bio

where rowCount should be the number of rows in the table, this can be specified inside a cell on the sheet, and Name be whatever name it is such as 'John'