Bdtjtk

I have the VBA code below. I don't know how it works, but I want to convert it to Google Sheets.

I'm hoping someone can either:

• Explain to me what the VBA is doing so that I can, perhaps, reason it out enough to work on programming it as Google Apps script,

or

• Show me how the same VBA function would be achieved through Google Sheets.

Function getData(targetName As String, targetSheet As String, targetDate)

Application.Volatile True

Dim res As Double

Dim col As Integer

Dim cell As Range

Dim names As Range



Dim lastrow As Long



With ThisWorkbook.Worksheets(targetSheet)

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    col = Application.Match(targetDate, .Range("4:4"), 0)

    If col = 0 Then

        getData = CVErr(xlErrNA)

        Exit Function

    End If

    Set names = .Range(.Cells(4, "A"), .Cells(lastrow, "A"))



    For Each cell In names

        If cell = targetName Then

            res = res + cell.Offset(, col - 1)

        End If

    Next cell

End With



getData = res

End Function

Here's a link to an example excel file where the function is being used:
https://www.dropbox.com/s/h5vcjv9tlh1vvg7/Resources%20and%20Projects%20Full%20Example.xlsm

Though I am not familiar with Google Apps scripting, I can help you with the first part.

The point of the function appears to be adding up all values where the name found in column A matches targetName passed in as a parameter and the date found in row 4 matches targetDate, which is also a parameter. (Row is determined by name and column is determined by date.) The total value is then returned as a double.

Here's line by line comments.

Function getData(targetName As String, targetSheet As String, targetDate)

Application.Volatile True 'I don't see a reason for this line

Dim res As Double

Dim col As Integer

Dim cell As Range

Dim names As Range



Dim lastrow As Long



With ThisWorkbook.Worksheets(targetSheet) 'All ranges start with ThisWorkbook.'targetSheet'

    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Get the last row with data in column A to 'lastrow'

    col = Application.Match(targetDate, .Range("4:4"), 0) 'Find 'targetDate' in row 4 and set it to 'col'

    If col = 0 Then 'Couldn't find 'targetDate'

        getData = CVErr(xlErrNA) 'Function returns the appropriate error

        Exit Function 'Exit the function after setting the return value

    End If

    Set names = .Range(.Cells(4, "A"), .Cells(lastrow, "A")) 'Setting the range from A4 to A'lastrow' to 'names'



    For Each cell In names 'Looping through every 'cell' in the 'names' range

        If cell = targetName Then 'If the 'cell' value matches the 'targetName' passed in as a parameter

            res = res + cell.Offset(, col - 1) 'Add the value from the column with the 'targetDate' to 'res'

        End If

    Next cell

End With



getData = res 'Return the total

End Function