The problem seems to relate to the way Windows handles non-native resolutions on monitors and can be avoided in several ways The problem can be a complete nightmare when it happens, but it only happens intermittently. We have been testing recently an excel worksheet used by a few dozen people and have developed a good … Read more
Try this code to pick a file from a SharePoint site: Dim SummaryWB As Workbook Dim vrtSelectedItem As Variant With Application.FileDialog(msoFileDialogOpen) .InitialFileName = “https://sharepoint.com/team/folder” & “\” .AllowMultiSelect = False .Show For Each vrtSelectedItem In .SelectedItems Set SummaryWB = Workbooks.Open(vrtSelectedItem) Next End With If SummaryWB Is Nothing then Exit Sub If I remember correctly, the Microsoft … Read more
There are mulitple results and methods when searching for the LastRow (in Column B). When using Cells(.Rows.Count, “B”).End(xlUp).Row you will get the last row with data in Column B (it ignores rows with spaces, and goes all the way down). When using: With wbshtSelect.Range(“B10”).CurrentRegion LR_wbSelectNew = .Rows(.Rows.Count).Row End With You are searching for the last … Read more
This error occurs often when any argument passed to the worksheet function is not of the correct type or simply doesn’t make sense. For example, I’ve had this problem when calling WorksheetFunction.Asin with an argument bigger than 1. In your case, I’d guess currentCell.Value is a non-numeric value or one not according to your region … Read more
From your comment above you wanted to try this approach If you enter =abb() into any cell Then cell A1 of that sheet wil be set to 12333 This is the line to update to pick the cell to update, and to place a value in it Range(“A1”).Value = 122333 From I don’t want my … Read more
What do you consider “special” characters, just simple punctuation? You should be able to use the Replace function: Replace(“p.k”,”.”,” “). Sub Test() Dim myString as String Dim newString as String myString = “p.k” newString = replace(myString, “.”, ” “) MsgBox newString End Sub If you have several characters, you can do this in a custom … Read more
Can’t be achieved with Conditional Formatting for all colours. Assuming: Row1 contains Data Labels, data set does not have gaps, the HEX colour is for the fill not the font, you have parsed the HEX colour values (numbers, not formulae) into Columns C:E (R,G,B) and that you do not require to do this often, then … Read more
You can add the image into a comment. Right-click cell > Insert Comment > right-click on shaded (grey area) on outside of comment box > Format Comment > Colors and Lines > Fill > Color > Fill Effects > Picture > (Browse to picture) > Click OK Image will appear on hover over. Microsoft Office … Read more
You need to tell the run to wait until the process is finished. Something like: const DontWaitUntilFinished = false, ShowWindow = 1, DontShowWindow = 0, WaitUntilFinished = true set oShell = WScript.CreateObject(“WScript.Shell”) command = “cmd /c C:\windows\system32\wscript.exe <path>\myScript.vbs ” & args oShell.Run command, DontShowWindow, WaitUntilFinished In the script itself, start Excel like so. While debugging … Read more
Instead of formulas, Why not use a PivotTable to crunch the numbers? You potentially face a longer one-time hit to load the data into the PivotCache, but after that, you should find a PivotTable recalculates much faster in response to filter changes than these computationally expensive formulas. Is there any reason you’re not using one? … Read more