This works, even when there are middle names: =MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,” “,CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))+1,LEN(A2)) If you want everything BUT the last name, check out this answer. If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula. Note that it is only … Read more
You can convert the value to a date using a formula like this, next to the cell: =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Where A1 is the field you need to convert. Alternatively, you could use this code in VBA: Sub ConvertYYYYMMDDToDate() Dim c As Range For Each c In Selection.Cells c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2)) … Read more
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
The easiest way would probably be ADO. Dim cn As Object Dim rs As Object Dim strFile As String Dim strCon As String Dim strSQL As String Dim s As String Dim i As Integer, j As Integer ”This is not the best way to refer to the workbook ”you want, but it is very … Read more
Excel (at least in Office 2007 on XP) can behave differently depending on whether a CSV file is imported by opening it from the File->Open menu or by double-clicking on the file in Explorer. I have a CSV file that is in UTF-8 encoding and contains newlines in some cells. If I open this file … Read more
In vba the function is MOD. e.g 5 MOD 2 Here is a useful link.
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
This can be achieved by using a class module. In the example that follows I will assume that you already have a userform with some textboxes on it. Firstly, create a class module in your VBA project (let call it clsTextBox — be sure to change the ‘Name’ property of the class module!) Private WithEvents … Read more
It’s a known issue. The VBA Round() function uses Banker’s rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here: PRB: Round Function different in VBA 6 and Excel Spreadsheet The workaround proposed by Microsoft is to write a custom function to get the desired results. Banker’s rounding always rounds 0.5 to … Read more
I had this problem, but I fixed it by switching the option of “Platform target“, from x86 to x64 or “Any CPU“. ( right click on the project, then select “Properties”, then the tab “Build”, then on “Platform target” select “x64” ) The problem is that for platform x86 you can use only about 1.8 … Read more