[Solved] Convert text date format to date format

Sub datestuff() myDate = “22nd July 2016″ myDateArray = Split(myDate, ” “) myDateArray(0) = Trim(Replace(Replace(Replace(Replace(myDateArray(0), “st”, “”), “nd”, “”), “rd”, “”), “th”, “”)) myDate = DateValue(Join(myDateArray, ” “)) Debug.Print myDate ‘ outputs ’22/07/2016’ End Sub Seems to me the main issue is the day part; handle that and the rest will be simple enough? solved … Read more

[Solved] VB.net get date from string [closed]

@Pᴇʜ Regex pattern shared in the comment works perfectly per your examples. Here is how to use it with an in cell function. Don’t Forget to add the reference to “Microsoft VBScript Regular Expressions 5.5“ Function simpleCellRegex(Myrange As Range) As String Dim regEx As New RegExp Dim strPattern As String Dim strInput As String Dim … Read more

[Solved] Excel Grouping (or using SQL) [closed]

” So created a SQL report to generate data that looks like this:” The reason for your downvotes is likely due to you not posting the SQL code here. Leaves me guessing at your table formats, so for my ease… select * from mytable is what I’ll guess you’ve used. I’m left guessing column names … Read more

[Solved] VBA MsgBox ‘Liability Disclaimer’?

Here is what I usually do. I do not use a Msgbox. The reason is very simple. Sometimes I need to show lot of information in the Disclaimer. However if you still need to use a MsgBox then adapt it from below. Do this Insert a UserForm as shown in the image below. Place a … Read more

[Solved] Whats wrong with this VB code? [closed]

Change the first line to: Function nameTonumber(name As String) As Integer Above End Function add: nameTonumber=number ‘If you are using VB6. return number ‘If you are using VB.NET 0 solved Whats wrong with this VB code? [closed]

[Solved] Import data from an API link that contains JSON to EXCEL [closed]

First of all you need to examine the structure of the JSON response, using any online JSON viewer (e. g. http://jsonviewer.stack.hu/), where you can see that your JSON object contains atletas array, clubes, posicoes, status, time objects, and several properties with scalar values: Going further there are objects within atletas array, each of them contains … Read more

[Solved] Automation email in Excel

You should be able to handle basic VBA usage in order to achieve this. Below is the VBA code that sends an Outlook e-mail message for Office 2000-2016. Source is http://www.rondebruin.nl You may put the code in the SelectionChange event of the requested cell(s) and change the Body, SendTo etc. portions according to your needs. … Read more

[Solved] Measuring query processing time in Microsoft Access

Here’s another alternative (old VB6/VBA – not VB.Net syntax). KEY SUGGESTION: the “_” characters are “continuation lines”. I honestly don’t think you want them in most of the places you’re using them. IMHO… Option Explicit Private Declare Function timeGetTime Lib “winmm.dll” () As Long Private startTime, endTime As Long Private Function elapsedTime(t1, t2 As Long) … Read more

[Solved] Do While Loop for SKU numbers

No need to use the Do Loop. Find the last row and then use a For loop. Is this what you are trying? Sub Sample() Dim ws As Worksheet Dim lRow As Long, i As Long ‘~~> Change this to the relevant sheet Set ws = ThisWorkbook.Sheets(“Sheet2”) With ws ‘~~> Find last row lRow = … Read more

[Solved] FDDividendAmericanEngine Function in QuantLib

It says clearly in the instructions in the link you provided: Within Excel, the function is named – CT.ENG.FDDividendAmericanEngine This means, in Excel, you should not use: FDDividendAmericanEngine …you should instead use: CT.ENG.FDDividendAmericanEngine To find out how to use functions from a third-party add-in, you need to consult the products documentation (as I just did), … Read more