hmmm, vlookup with an if statement as a worksheet function in VBA....

[ajm]

so, i am trying to replicate a vlookup, that has a lookup value determined by an if statement, in VBA.

the formula seeks to lookup a name associated with a project. the lookup value for each project is the concatenation of the project name and project phase. the project phase appears within a string so is determined by finding either of two words that appear at the start of each phase.

the phase might start with "Description" or it might start with "Resource".

the lookup value formula that works in the sheet is:

Procurement Forward Plan (Live) (A7702697).xlsb
	A	B	C	D
36	LookupValue	Contract Owner	Contract	Description
37	COM-ABC-U-0012Resource - Design Period of Project	NOT YET ALLOCATED	COM-ABC-U-0012	Common Street ABC Upgrade - Technical words here for my industry - Description - Resource - Design Period of Project
Sheet1

Cell Formulas
Range	Formula
A37	A37	=IF(B37<>"NOT YET ALLOCATED",TRIM(C37),TRIM(LEFT(C37,14)&IF(COUNTIF(D37,"*Resource*"),RIGHT(D37,(LEN(D37)-(FIND("Description",D37)+13))),RIGHT(D37,(LEN(D37)-(FIND("Description",D37)-1))))))

in the macro i am writing, i want to use a loop to look for all projects "NOT YET ALLOCATED" and then look up the correct person for that project.

my macro so far:

Sub NYAprojects()

'////REPLACES NOT YET ALLOCATED

    Dim COsht As Worksheet
    Dim COrowLast As Long
    Dim COrng As Range
    Dim COarr As Variant
    Dim n As Long
    
    With Application
    .ScreenUpdating = False ' stop screen flashing as macro runs
    .DisplayAlerts = False ' stop alert messages
    .EnableEvents = False ' disable events running
    End With
   
    Set COsht = Worksheets("Forward Plan")
    With COsht
        COrowLast = .Range("A" & Rows.Count).End(xlUp).Row
        Set COrng = .Range(.Cells(45, "A"), .Cells(rowLast, "A"))
        COarr = COrng.Value2
    End With
   
    For n = 1 To UBound(COarr)
        If (COarr(n, 1)) = "NOT YET ALLOCATED" Then
                COarr(n, 1) = /////VLOOKUP TO GO HERE/////

application.worksheetfunction.vlookup(IF(COUNTIF(d5,"*Resource*"),RIGHT(d5,(LEN(d5)-(FIND("Description",d5)+13))),RIGHT(d5,(LEN(d5)-(FIND("Description",d5)-1))),other sheet range, 2,0))



        End If
    Next i
   
    rng.Value2 = COarr

    With Application
         'turn each back on
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With

End Sub

help or suggestions of other ways to do the same thing would be greatly appreciated.

 

[ajm]

anyone?

i thought maybe i should assign the lookup value to a variable but am getting lost.