Return to Answer

Also, speaking of lastrow - There is a standard way to find lastRow and lastColumn. That post explains why.

Also, speaking of lastrow - There is a standard way to find lastRow and lastColumn. That post explains why.

First things first, variables. Please use them. Right now the whole thing seems overwhelming because it is.

Oh, please forgive me if my tone come across abrasive - I know you're just trying to maintain and learn, which is why I bring this all up! Don't feel bad, I'm self-taught too and if I ran into that thing, I could not have fixed it when I started learning.

First things first, variables. Please use them. Right now the whole thing seems overwhelming because it is.

###Example

You pointed to

Sheets("Main Tab").Range("O2:O" & LastRow).Formula = "=IF(AND(N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), N2<>""""),IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",4,TRUE), ""Missing""),N2)"

as a single formula, right?

Const LOOKUP_MATCH as String = "Missing"
Dim lookupString as string
lookupString = CONCATENATE(E2,C2,D2,CalculateWeek)
dim velocityLookupRange as Range
set velocityLookupRange = Velocity.Range(cells(2,10),cells(lastRow,13))
dim lookupCell as Range
Set lookupCell = Range("N2")
Dim returnColumn as Long
ReturnColumn = 4

The formula would now be

MainTab("O2:O" & LastRow).Formula = "=IF(AND(LOOKUPCELL>IF(VLOOKUP(LookupString),VelocityLookupRange,1,1)=LookupString), VLOOKUP(LookupString),VelocityLookupRange,returnColumn,1), LOOKUP_MATCH), LOOKUPCELL<>""""),IF(VLOOKUP(LookupString),VelocityLookupRange,1,1)=LookupString), VLOOKUP(LookupString),VelocityLookupRange,returnColumn,1), LOOKUP_MATCH),LOOKUPCELL)"

Still overwhelming! Let's get out Notepad++ to figure this thing out:

Sheets("Main Tab").Range("O2:O" & LastRow).Formula =

=IF(
 AND(
 N2>IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",4,TRUE), ""Missing""), 
 N2<>""""),
 THEN 
 IF(VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",1,TRUE)=CONCATENATE(E2,C2,D2,CalculateWeek), VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",4,TRUE), ""Missing"")
 ELSE:
 ,N2)

I think boils down to (in VBA)

IF NOT N2 = vbNullString Then
 if N2 > someResult then
 someResult
 end if
 Else: MISSING
end if

With

dim someResult as String
someResult = VLOOKUP(CONCATENATE(E2,C2,D2,CalculateWeek),Velocity!J2ドル:N$" & VelocityLastRow & ",1,TRUE) = lookupString

I'm still not sure I got that right. How can you debug this? Is the error because it's taking forever to calculate or because the formula string is too many characters? Or is there a missing result?