Converting nested LOOKUP formula into VBA code

Question

I have VBA code that copies data from one sheet into another sheet based on select criteria. The target sheet is a list of project names with columns of data that are pasted over.

Right now the code copies ALL data in the source column into the target cell. I would like the code to paste only the data that matches to the project name, and repeat down for each project - similar to a vlookup.

I'm new to VBA and don't know how to include a vlookup into the code. I have an Excel formula that retrieves the distinct values that match to the project name. I have tried to embed the formula into the code but receive a 'type mismatch' error and the formula is highlighted.

Is there a way for the code to loop through each project name and paste their distinct values?

Any help is appreciated!!

Excel formula =IFERROR(LOOKUP(2, 1/((COUNTIF($J$8:J8,'Project plan'!D2:D91)=0)*($I$9='Project plan'!V2:V91)),'Project plan'!D2:D91),"") Source

Current code

Option Explicit

Sub Update_Market_Status()
'
' Update_Market_Status Macro
' Updates Status field in Executive Market Summary Report
'

Dim d As Long
Dim prev_acts As String
Dim next_acts As String
Dim ws As Object
Dim status_report As Object
Dim is_synced As Boolean
Dim sync_value As String
Dim lastRow As Long

Set ws = ThisWorkbook.Sheets("Project plan")
Set status_report = ThisWorkbook.Sheets("Status Report")
lastRow = ThisWorkbook.Sheets("Project plan").Cells(Rows.Count, "D").End(xlUp).Row + 1

On Error Resume Next

sync_value = ws.Range("U2").Value


If VBA.LCase(sync_value) = "y" Then
    is_synced = True
Else
    is_synced = False
End If

If is_synced = True Then

        For d = 2 To lastRow
            If LCase(ws.Range("N" & d).Value) = "y" Then
                If LCase(ws.Range("T" & d).Value) = "c" Then
                    If prev_acts = vbNullString Then
                        prev_acts = "'- " & ws.Range("D" & d).Value
                        Else
                        prev_acts = prev_acts & vbLf & "- " & ws.Range("D" & d).Value
                    End If
                ElseIf LCase(ws.Range("T" & d).Value) = "o" Or LCase(ws.Range("T" & d).Value) = vbNullString Then
                    If next_acts = vbNullString Then
                        next_acts = "'- " & ws.Range("D" & d).Value
                        Else
                        next_acts = next_acts & vbLf & "- " & ws.Range("D" & d).Value
                    End If
                End If

            End If
     Next d

    status_report.Range("E8").Value = prev_acts ' Previous Actions
    status_report.Range("F8").Value = next_acts ' Next Actions

End If

End Sub

mermaldad mermaldad · Accepted Answer · 2019-10-31T18:48:39

One way to approach this would be to have the VBA script insert the lookup formula you gave into the cells of the new sheet, then select and copy those cells and paste them as values only.

A similar approach would be to use the WorksheetFunctions object to call the functions of the formula within VBA, then write the resulting value into your sheet.

Either of these approaches avoids recreating the worksheet functions in VBA code.

Converting nested LOOKUP formula into VBA code

1 Answers