天道酬勤,学无止境

excel

Twig Excel Bundle autosize

Question How to set autosize in Twig Excel Bundle ? https://twigexcelbundle.readthedocs.io/en/latest/ Answer1 You can set the default autoSize property of a sheet to true via columnDimension: {% xlssheet 'Worksheet' { columnDimension: { 'default': { autoSize: true } } }%} {# ... #} {% endxlssheet %} To define it for a specific column, use the letter of the desired column instead of default, per example the column D: {% xlssheet 'Worksheet' { columnDimension: { 'D': { autoSize: true } } }%} {# ... #} {% endxlssheet %}

2022-07-11 09:58:49    分类:技术分享    excel   twig   bundle   autosize

Find Function in VBA

Question I started a easy demo by writing Sub search() Range("B1:B7").Find("abc").Activate End Sub I was hoping that excel will auto select a cell contains value "abc" in it, however, I got an error message saying "object variable or with block variable not set". Did I miss anything at all please Appreciate it. Answer1 "https://msdn.microsoft.com/en-us/library/office/ff839746.aspx" Sub tester() With Worksheets(6).Range("B1:B500") Set c = .Find("Smith Jon Junior", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address ' not required c.Activate else msgbox ("Not Found") End If End

2022-07-11 09:56:47    分类:技术分享    vba   excel   find

Excel formula reference cell above last cell in range

Question I'm trying to set up a formula to automatically calculate the % change between the most recently added cell in a range (which includes #N/A values at the bottom of the range) and the cell immediately above it. I've been using this formula to obtain the value of the bottom not #N/A cell: LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19) Which is working fine. My first thought on how to reach the cell above it was to use OFFSET, like so: OFFSET(LOOKUP(2, 1/NOT(ISNA(G8:G19)), G8:G19), -1, 0) but this gives me an error, I think because the lookup function is returning the value in the cell rather

2022-07-11 09:51:56    分类:技术分享    excel   excel-formula   excel-2013

Copy all cells with values from a range of columns into one single column

Question I am a beginner with VBA, and I've been working on my first large-scale project. I have a Spreadsheet that compares multiple sets of data, and spits out values if there are any errors. My values are in range T4:X4 and I would like all of the values to be displayed sequentially in the range Z4:Z, with no blanks. One of the problems is all of the blank cells actually have formulas in them that just evaluate to " ". I don't want to copy those. Here is the code I have written thus far: Sub Generate_ReportVSP1() Dim rSource As Range Dim TargetRange As Range Dim i As Integer Dim j As

2022-07-11 09:41:12    分类:技术分享    excel   vba

Excel Vlookup with Autofill VBA

Question I'm trying to make a macro that seems pretty simple, but it's proven to be harder than I thought. I started making it using the "point and click" method, but it hasn't been as dynamic as I had thought. Basically I'm using vlookup to look up the names in cell A2 from another worksheet, then copy numbers in column D over to the current sheet. When looking at the code, it looks something like this for this part: Range("D2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-3],'Downstairs'!R[-1]C[-3]:R[200]C[14],4,0)" Range("D2").Select Selection.AutoFill Destination:=Range("D2:D" & lastrow

2022-07-11 09:36:00    分类:技术分享    excel   vba   vlookup   autofill

enterprise architech export a Use Case to excel

Question i have a use case Diagram like this:Use Case can i export to a excel the name and descriptions? using a script ? Answer1 Yes, you can. There are two ways. One is a package export. From the browser use Import/Export / CSV... for the package which can export the according package with the use case(s). The second is to adapt the csv export script which can be found in Scripting/EAScriptLib (you need to enable the according MDG).

2022-05-17 04:47:04    分类:技术分享    excel   vbscript   jscript   enterprise-architect

How to use Words selection methods through Excel VBA

Question In Word VBA you are able to set and move the cursor position by using the Selection.MoveLeft, Selection.MovRight etc... However while trying to use this same method in Excel VBA I get an error saying "Object doesnt support this property or method." I have imported the Word Object Library reference. How am I able to move the cursor position on the Word document using VBA on the Excel application. Any help will be greatly appreciated. Code: Set Doc = ActiveDocument.Content With Doc.Find .Execute FindText:="*", ReplaceWith:="NEW*" End With Selection.HomeKey Unit:=wdStory Selection

2022-05-17 03:58:04    分类:技术分享    vba   excel   ms-word

Hide worksheet in Excel Add-In from other VBA Projects

Question I have an Excel Add-In which I am building which stores users settings in a Settings worksheet within the Add-In. I use the Settings sheet essentially as storage for boolean values and potentially, the user's registration details. However, if I open up a new VBA Project and include the Add-In as a reference (In VBA: Tools > References > MY Add-In) then I can access the Settings worksheet and potentially break the password on it and hack the registration details. The Settings sheet is set to be xlVeryHidden however it can be accessed via VBA code. Is there anyway to prevent this in

2022-05-17 02:58:39    分类:技术分享    vba   excel

EXCEL Formula not auto updating! Help!

Question I have an excel formula to pull the name of the file in excel and put it into a field. The problem is, when I open multiple files I've already done, it repulls the file name and updates it to the LAST file opened. So if I opened MFULTON and then JADAM, the file name cell for JADAM would real MFULTON. If I manually double click and press enter, it will auto correct back to JADAM, but my colleagues won't know how to do this. What is happening? Answer1 Uou need to make your formula Volatile Answer2 You can try this. Go to File->options->Formulas-> Workbook Calculation-> Select Automatic

2022-05-17 02:57:16    分类:技术分享    excel

How to reference ActiveX ComboBox in Excel 2016 VBA

Question I've tried various solutions on here and elsewhere such as: dim wb as workbook, ws as worksheet Dim cb As Object Set cb = ws.OLEObjects("ComboBoxViews") or ComboBoxViews or ws.comboboxviews But all return Error 1004, the item with specified name wasn't found. But it DOES exist, checking properties the name is very clearly 'comboboxviews'. Any ideas? EDIT: To make it clear for anyone else seeking help in the future and using Romcel's very helpful code as a base, it seems that in order to add items to an ActiveX ComboBox (which is my final goal), you need to reference it as an object

2022-05-17 02:31:07    分类:技术分享    excel   activexobject   vba