Biogeneric
New Member
- Joined
- Nov 30, 2016
- Messages
- 5
Hi guys,
Got quite a unique issue here, and a solution would mean the world to me as it's for a high-priority project at my place of work.
So I consider myself to be quite proficient at Excel and VBA, having used it on a daily basis for the past few years for countless different tasks, however this issue has me and my colleagues stumped.
Here's the issue...
So I have around 30 columns, each with ~100,000 rows of data (many blank cells though I might add). Several of these columns have formulas added to them, inserted via a VBA module (see detail of these formulas below, consisting of IFs and VLOOKUPs).
Now, loading these formulas, and calculating the sheet, actually runs quite quickly, surprisingly. The issue is, in the interest of spreadsheet usability, I need to convert ALL of these formulas to values. When I try and do this via VBA, it crashes (stops responding for what appears to be forever).
Now, if I manually try and delete the cell contents of each of these columns, one by one, it deletes almost instantly UNTIL I reach the last remaining column with formulas in, and that one crashes (running out of memory?). Even using a small sample of 100 rows takes an absolute age.
What's weird is, it doesn't matter what column, it's always the last one I get to (whether it's column A, B, C etc.) that causes these memory issues. All of the other columns clear almost instantly.
What I have tried...
I've tried the attempted solutions as above, I've tried running in safe mode, I've tried repairing the file in case of some form of corruption, I've tried writing a code that steps through a loop and removes the formulas line by line, I've tried removing ALL formatting from the sheet - none of these things have worked.
I'm at the point now where I don't know what to do.
Snippet from my code that inserts the formulas based on criteria...
I originally tried to achieve my desired outcome with several loops, in replacement of the lookups, rather than formulas, however this ended up taking hours (understandably). Formula based seemed the best solution, which it is, all up until I need to convert the last column to values.
Anyone come across anything like this before? Is there even a solution to this?
Any help would be greatly appreciated.
Thank you in advance,
Jack
Got quite a unique issue here, and a solution would mean the world to me as it's for a high-priority project at my place of work.
So I consider myself to be quite proficient at Excel and VBA, having used it on a daily basis for the past few years for countless different tasks, however this issue has me and my colleagues stumped.
Here's the issue...
So I have around 30 columns, each with ~100,000 rows of data (many blank cells though I might add). Several of these columns have formulas added to them, inserted via a VBA module (see detail of these formulas below, consisting of IFs and VLOOKUPs).
Now, loading these formulas, and calculating the sheet, actually runs quite quickly, surprisingly. The issue is, in the interest of spreadsheet usability, I need to convert ALL of these formulas to values. When I try and do this via VBA, it crashes (stops responding for what appears to be forever).
Now, if I manually try and delete the cell contents of each of these columns, one by one, it deletes almost instantly UNTIL I reach the last remaining column with formulas in, and that one crashes (running out of memory?). Even using a small sample of 100 rows takes an absolute age.
What's weird is, it doesn't matter what column, it's always the last one I get to (whether it's column A, B, C etc.) that causes these memory issues. All of the other columns clear almost instantly.
What I have tried...
I've tried the attempted solutions as above, I've tried running in safe mode, I've tried repairing the file in case of some form of corruption, I've tried writing a code that steps through a loop and removes the formulas line by line, I've tried removing ALL formatting from the sheet - none of these things have worked.
I'm at the point now where I don't know what to do.
Snippet from my code that inserts the formulas based on criteria...
Code:
For j = 1 To endColTrk
If shtTrk.Cells(i, j).Value <> shtTrk.Range("G" & i).Value Then
If Len(shtTrk.Cells(i, j).Value) > 2 Then
formLen = (Len(shtTrk.Cells(i, j).Value) + 1) / 3
Select Case formLen
Case 2
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE),""""))"
Case 3
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE),"""")))"
Case 4
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE),""""))))"
Case 5
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE),"""")))))"
Case 6
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE),""""))))))"
Case 7
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE),"""")))))))"
Case 8
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE),""""))))))))"
Case 9
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE),"""")))))))))"
Case 10
shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
"=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE)" & _
",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 9) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 9) * 3) - 2, 2) & ",FALSE),""""))))))))))"
End Select
End If
End If
Next
I originally tried to achieve my desired outcome with several loops, in replacement of the lookups, rather than formulas, however this ended up taking hours (understandably). Formula based seemed the best solution, which it is, all up until I need to convert the last column to values.
Anyone come across anything like this before? Is there even a solution to this?
Any help would be greatly appreciated.
Thank you in advance,
Jack