reissmiltiadous
New Member
- Joined
- Jan 21, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi,
ThisWorkbook.Worksheets("(1)").Range("B6").Formula = "=IFERROR(UNIQUE(FILTER(VSTACK('Prior TB Backend'!A2:A" & RowNumCombinePrior & ",'Opening TB Backend'!A2:A" & RowNumCombineOpening & ",'Closing TB Backend'!A2:A" & RowNumCombineClosing & "),VSTACK('Prior TB Backend'!A2:A" & RowNumCombinePrior & ",'Opening TB Backend'!A2:A" & RowNumCombineOpening & ",'Closing TB Backend'!A2:A" & RowNumCombineClosing & ")<>" & Chr(34) & Chr(34) & "))," & Chr(34) & Chr(34) & ")"
Sorry it's a bit long winded but I'm trying to get the script above to paste in an Excel formula for me but using VBA variables. The whole thing pretty much works but for some reason it just adds a "@" after the "=" and I don't have a clue why. I have attached a screenshot of this so you can see, this means the formula doesn't work. If I just take out the "@" myself in Excel then the whole formula works perfectly however I've tried everything I can think of and nothing seems to get rid of the "@" without breaking the script.
To give context, the formula above is used to pull all of the unique entity names from a list that is generated by my script. RowNumCombinePrior, RowNumCombineOpening, RowNumCombineClosing are just variables to record the number of lines of data in each sheet.
Any help would be greatly appreciated.
ThisWorkbook.Worksheets("(1)").Range("B6").Formula = "=IFERROR(UNIQUE(FILTER(VSTACK('Prior TB Backend'!A2:A" & RowNumCombinePrior & ",'Opening TB Backend'!A2:A" & RowNumCombineOpening & ",'Closing TB Backend'!A2:A" & RowNumCombineClosing & "),VSTACK('Prior TB Backend'!A2:A" & RowNumCombinePrior & ",'Opening TB Backend'!A2:A" & RowNumCombineOpening & ",'Closing TB Backend'!A2:A" & RowNumCombineClosing & ")<>" & Chr(34) & Chr(34) & "))," & Chr(34) & Chr(34) & ")"
Sorry it's a bit long winded but I'm trying to get the script above to paste in an Excel formula for me but using VBA variables. The whole thing pretty much works but for some reason it just adds a "@" after the "=" and I don't have a clue why. I have attached a screenshot of this so you can see, this means the formula doesn't work. If I just take out the "@" myself in Excel then the whole formula works perfectly however I've tried everything I can think of and nothing seems to get rid of the "@" without breaking the script.
To give context, the formula above is used to pull all of the unique entity names from a list that is generated by my script. RowNumCombinePrior, RowNumCombineOpening, RowNumCombineClosing are just variables to record the number of lines of data in each sheet.
Any help would be greatly appreciated.