Formula to be simplified

[Ashish Batham]

.Formula = "=IF(ISERROR(HLOOKUP(E$5,'Append Pivot'!$H" & startThirdPivot & ":$BP" & (reason_count + startThirdPivot) & ",$A" & row & ",FALSE))=FALSE,IF(ABS(HLOOKUP(E$5,'Append Pivot'!$H" & startThirdPivot & ":$BP" & (reason_count + startThirdPivot) & ",$A" & row & ",FALSE))<2, " & quote & quote & ", HLOOKUP(E$5,'Append Pivot'!$H" & startThirdPivot & ":$BP" & (reason_count + startThirdPivot) & ",$A" & row & ",FALSE))," & quote & quote & ")"

 

[Luke M]

Assumptions based on your other threads:
quote = "
startThirdPivot is a number
row is a number
reason_count is a number

 

[Luke M]

It's pretty reduced already in terms of formula structure , but to make it easier to read the code, you could do:

Dim fString As String
Dim myFormula
fString = "HLOOKUP(E$5,'Append Pivot'!$H" & startThirdPivot & ":$BP" & (reason_count + startThirdPivot) & ",$A" & row & ",)"
myFormula = "=IF(ISERROR(" & fString & "),"""",IF(ABS(" & fString & ")<2,""""," & fString & "))"
.Formula = myFormula

By building the formula as a string first, you can more easily debug and see what the actual string looks like before trying to plug it into the formula. By using an extra variable to hold the HLOOKUP bit, I think it makes the longer formula a little easier to read.