Here, a solution that uses DROP/REDUCE/VSTACK pattern to generate each row. Check for example @JvdV’s answer from this question: How to split texts from dynamic range? and a similar idea DROP/REDUCE/HSTACK pattern to generate the columns for a given row. In cell E2 put the following formula: =LET(set, A2:B13, IDs, INDEX(set,,1), dates, INDEX(set,,2), HREDUCE, LAMBDA(id, … Read more
From other forums, I have learned that it is due to the MS Update and that a good fix is to simply delete the file MSForms.exd from any Temp subfolder in the user’s profile. For instance: C:\Users\[user.name]\AppData\Local\Temp\Excel8.0\MSForms.exd C:\Users\[user.name]\AppData\Local\Temp\VBE\MSForms.exd C:\Users\[user.name]\AppData\Local\Temp\Word8.0\MSForms.exd Of course the application (Excel, Word…) must be closed in order to delete this file.
To answer your direct question, it is: Range(“A1”).NumberFormat = “@” Or Cells(1,1).NumberFormat = “@” However, I suggest changing the format to what you actually want displayed. This allows you to retain the data type in the cell and easily use cell formulas to manipulate the data.
I was able to determine that: SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)) is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden. ISNUMBER(SEARCH(“Pear”,B2:B18))+0) is used to return an array of which cells contain “Pear”. If “Pear” is found, 1 is returned, else 0. … Read more
Looks like you may be making some common rookie mistakes here (it’s okay we all did it). VBA example with line-by-line explanations TIP: Try not to use “Select” or “Copy”. Why use select when all you have to do is reference the cells themselves? For example, instead of using Sheets(“codes”).Select Range(“A5:A100”).Select Selection.Copy Sheets(“Sheet2”).Select Range(“B28”).Select ActiveSheet.Paste … Read more
I would use a parameterized query (where IDVALUE=?), but that prompts me to input the parameter value From what you mentioned I understand you are using MS Query and you need to follow the following steps Steps to make a parameterized query in Excel use a cell value as parameter First go to the Data … Read more
CallByName function can help you. Let’s say there are some class modules in your project: clsSample0, clsSample1 and clsSample2. Add a new class module named clsSpawner, which lists all target classes as public variables having the same names, and declared with New keyword: Public clsSample0 As New clsSample0 Public clsSample1 As New clsSample1 Public clsSample2 … Read more
You can use Characters cell’s property like : Cells(1,1).Characters(Start:=2, Length:=3).Font.Color = RGB(255, 0, 0) This should be a good start : Sub vignesh() Dim StartChar As Integer, _ LenColor As Integer For i = 1 To 5 With Sheets(“Sheet1”).Cells(i, 1) StartChar = InStr(1, .Value, “|”) If StartChar <> 0 Then LenColor = Len(.Value) – StartChar … Read more
I have also the same issue: have to export data from Excel (Office 2003, VBA6.5) to UTF-8 encoded file. Found the answer from your question ! Below my example where I also strip the BOM using trick #2 from boost’s (thanks!) answer. I didn’t get #1 working and never tried #3. Sub WriteUTF8WithoutBOM() Dim UTFStream … Read more
Unfortunately these Named Constants are part of the Excel Object Library which VBScript has no way of referencing so the best approach is to lookup the named constants in Object Browser inside Excel VBA or online via various references then create your own named constants and use them in your code. In this example you … Read more