You don’t need the CELL() part of your formulas: =INDIRECT(ADDRESS(B1,B2)) or =OFFSET($A$1, B1-1,B2-1) will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.
I updated the dll calls to use LongPtr everywhere instead of Long. You should have not done that. By adding PtrSafe to a function declaration, you promise to the compiler that you have put LongPtr in all places where it needs to be, and nowhere else. LongPtr is a pointer-sized integer. It must be used … Read more
Macros that take arguments are not visible in the macro box because there is no point in having them there. If they need arguments to run, they cannot be run from the macro box because there is no way to supply an argument to the macro in question. Normally, a macro shows up in the … Read more
This works. =MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,”\”,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))+1,LEN(A1)))) The above was my original answer. Neil simplified the expression somewhat and posted this as a comment below: =LEFT(A1,FIND(“?”,SUBSTITUTE(A1,”\”,”?”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))) This takes advantage of the fact that ? is a forbidden character in paths so that “?” can safely be used instead of CHAR(1) as a placemark, thus improving readability a little bit. … Read more
Offset function dynamic range makes it possible. Sample data Steps Define a dynamic named range =OFFSET(Sheet1!$A$2,,,1,COUNTA(Sheet1!$A$2:$Z$2)) and give it a name mobileRange Right Click on Chart Click on Select Data This screen will come Click on Edit under Legend Entries.(mobiles is selected) change the Series value to point to mobileRange named range. Now if data … Read more
How can I hide and show all the standard Excel ribbon tabs using VBA (not XML) The answer is “YOU CAN’T“. AFAIK, you can’t do that using VBA. Unfortunately VBA doesn’t expose the tabs. The only options that you have are as shown in the image below So you can work with the commandbar, commandbarButton, … Read more
I will break this “Tutorial” in 3 steps 1) Writing your Excel Macro 2) Preparing your vbscript file 3) Setting the task in Windows Task Scheduler WRITING THE EXCEL MACRO Open a new File in Excel and in the module, paste this code Option Explicit Const strTo As String = “[email protected]” Const strCC As String … Read more
I did a little research and was able to write this code: strbody = “<BODY style=font-size:11pt;font-family:Calibri>Good Morning;<p>We have completed our main aliasing process for today. All assigned firms are complete. Please feel free to respond with any questions.<p>Thank you.</BODY>” apparently by setting the “font-size=11pt” instead of setting the font size <font size=5>, It allows you … Read more
Try to slightly modify your code: Sub forEachWs() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Call resizingColumns(ws) Next End Sub Sub resizingColumns(ws As Worksheet) With ws .Range(“A:A”).ColumnWidth = 20.14 .Range(“B:B”).ColumnWidth = 9.71 .Range(“C:C”).ColumnWidth = 35.86 .Range(“D:D”).ColumnWidth = 30.57 .Range(“E:E”).ColumnWidth = 23.57 .Range(“F:F”).ColumnWidth = 21.43 .Range(“G:G”).ColumnWidth = 18.43 .Range(“H:H”).ColumnWidth = 23.86 .Range(“i:I”).ColumnWidth = 27.43 … Read more
This is how I clear the data: Sub Macro3() With Sheet1.ListObjects(“Table1”) If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete End If End With End Sub