Hi Mike! I remember you and Leila once doing one video on inserting an image directly from Power Query. The solution was to use data types from Power Query. This could also be another solution for this problem. 1. Transforming data 2. Creating a data type on the mail column 3. Loading the data to the worksheet 4. Adding new column from data type (that would contain a formula like [@Mail].Mail 5. Inserting =HYPERLINK([@Mail].Mail) 6. Hiding the originally loaded column with plain data. It seems like more work but.... in my case Excel messes the formula inserted by macro by adding the implicit intersection "@" each time. Additionally, the text-formula + macro solution requires us to run the macro each time we update the data. Great video! Thank you, Mike!
Thank you so much for your knowledge, it helped me a lot. If you wish to keep the original column, you can also use the following formula in power query: ="=HYPERLINK([@[your original column name]]; ""Open"")". In alternative to the macro you can also use the tool replace, replacing all "=" for "=".
@Nazho248 I've never come across that problem before. After a bit of research I found a possible solution. Please go into the VBA Editor (ALT+F11) and change the 2 instances of Value to be Value2 and see if that works. If that still doesn't work, try changing the 2 instances of Value2 to Formula2. As you saw on the video it didn't cause an issue for me. I'm wondering if it's the version of Excel you have?
I'm using the desktop app for Excel from Microsoft 365 and I'm having the same issue. Any instance of ActiveCell.Value = ActiveCell.Value or ActiveCell.Value2 = ActiveCell.Value2 or ActiveCell.Formula2 = ActiveCell.Formula2 is throwing an Application-defined or Object-defined error.
