UPDATE: You can do this now automatically with Excel 2013. I’ve created this as a new answer because my previous answer actually solves a slightly different problem. If you have that version, then select your data to create a pivot table, and when you create your table, make sure the option ‘Add this data to … Read more
Unfortunately, there is no formula way to result in a truly blank cell, “” is the best formulas can offer. I dislike ISBLANK because it will not see cells that only have “” as blanks. Instead I prefer COUNTBLANK, which will count “” as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has … Read more
Some folk dislike this approach because of an “inappropriate” use of error handling, but I think it’s considered acceptable in VBA… An alternative approach is to loop though all the sheets until you find a match. Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean Dim sht As Worksheet If wb Is Nothing Then … 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
I find the easiest way is to double up on the quotes to handle a quote. Worksheets(“Sheet1”).Range(“A1”).Formula = “IF(Sheet1!A1=0,””””,Sheet1!A1)” Some people like to use CHR(34)*: Worksheets(“Sheet1”).Range(“A1”).Formula = “IF(Sheet1!A1=0,” & CHR(34) & CHR(34) & “,Sheet1!A1)” *Note: CHAR() is used as an Excel cell formula, e.g. writing “=CHAR(34)” in a cell, but for VBA code you use … Read more
You need to declare the variables outside the function: Public iRaw As Integer Public iColumn As Integer Function find_results_idle() iRaw = 1 iColumn = 1
Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”) URL = “http://www.somedomain.com” objHTTP.Open “POST”, URL, False objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)” objHTTP.send “” Alternatively, for greater control over the HTTP request you can use WinHttp.WinHttpRequest.5.1 in place of MSXML2.ServerXMLHTTP.
You should have space characters at the start of fields ONLY where the space characters are part of the data. Excel will not strip off leading spaces. You will get unwanted spaces in your headings and data fields. Worse, the ” that should be “protecting” that line-break in the third column will be ignored because … Read more
I have found a 2nd solution. Press “Debug” button in the popup. Press Ctrl+Pause|Break twice. Hit the play button to continue. Save the file after completion.
This function returns the column letter for a given column number. Function Col_Letter(lngCol As Long) As String Dim vArr vArr = Split(Cells(1, lngCol).Address(True, False), “$”) Col_Letter = vArr(0) End Function testing code for column 100 Sub Test() MsgBox Col_Letter(100) End Sub