excel Archives - Page 13 of 24

[Solved] Set ‘Fit All Columns on One Page’ via Excel JavaScript API

October 21, 2022 by Kirat

The Office 365 Online Automate Scripts helped get me on the right path. Docs: https://learn.microsoft.com/en-us/javascript/api/excel/excel.pagelayoutzoomoptions?view=excel-js-preview https://learn.microsoft.com/en-us/javascript/api/excel/excel.pagelayout?view=excel-js-preview#excel-excel-pagelayout-zoom-member Code: await Excel.run(async (context) => { var ws = context.workbook.worksheets.getActiveWorksheet(); var PageLayoutZoomOptions_Obj = { ‘horizontalFitToPages’: 1, ‘verticalFitToPages’: 0, } ws.pageLayout.zoom = PageLayoutZoomOptions_Obj await context.sync(); }); Note: I had issues using/including scale so I just left it out. solved Set … Read more

[Solved] Count number of times value appears consecutively in a row – Excel

October 19, 2022 by Kirat

Use: =AND(A1=1,B1=1)+COUNTIFS(A1:W1,0,B1:X1,1,C1:Y1,1) We want to count where the there is a pattern of 0,1,1 hence the equal size but offset ranges. It will count where the pattern matches. We also want to count if A1 and B1 both equal 1 because it should be positive but does not meet the pattern. 5 solved Count number … Read more

[Solved] How to copy specific range cells from multiple sheets to one sheet?

October 17, 2022 by Kirat

I fully agree with guys who told you that none will write the code for you, but you are lucky that I have that already written and there may be other people looking for same info, so here it is. Put the code to module in your master workbook and replace some data as commented: … Read more

[Solved] How to write a macro that finds a cell based on length? [closed]

October 15, 2022 by Kirat

The following might help you. Assuming you have a single contiguous range of cells, some of which contain a 4 character code, you can do Sub test() Dim r As Range Set r = Range(“A1:A6”) For Each c In r.Cells If Len(c) = 4 Then Debug.Print “cell “, c.Address(), ” had four characters: “, c.Value() … Read more

[Solved] Excel cells matching in VBA [closed]

October 15, 2022 by Kirat

You can accomplish this with a VLOOKUP between two books. The guts of the equation are below. You wont actually type in the ‘[book2]SheetName’ portion, this will need to reflect the name of your other workbook and the sheet that you are looking at within that book. A2 = Look up value (in your example … Read more

[Solved] Excel Vba Filter Range By Each Unique Value and Copy Data

October 14, 2022 by Kirat

Create a list of all the vendors in a separate place and remove duplicates. Then use this list as an array to filter for individual vendors. Then you can put the data for each vendor in a separate sheet. Here’s the code for this, assuming the data you provided above is put in the cells … Read more

[Solved] vba excel: How to fill in two cells in a row with text

October 14, 2022 by Kirat

Try this: Inside the excel application go to the developer tab on the ribbon and click record macro. Type the values into the two cells manually. Click stop recording and view the macro it created. This would show you VBA code that will work and you can compare it to what you have in order … Read more

[Solved] How to create a combination generator where order does not matter but being limited from a specific range of Sum? Using Excel VBA macro [closed]

October 14, 2022 by Kirat

How to create a combination generator where order does not matter but being limited from a specific range of Sum? Using Excel VBA macro [closed] solved How to create a combination generator where order does not matter but being limited from a specific range of Sum? Using Excel VBA macro [closed]

[Solved] “Object doesn’t support this option” Error 445 in VB Code

October 14, 2022 by Kirat

To get some help, you need to specify where you get the error. This time you’ve been lucky, I’ve recognized an error I went through some time ago. Cause of the error With Application.FileSearch Error description Run-time error 445: Object doesn’t support this option Cause The method has been removed since > Excel 2003. In … Read more

[Solved] Macro/Function to find a string in a column and find it in a sentence in sheet 2 and return respective value in column 3

October 14, 2022 by Kirat

First we will dim the variable Dim Rws As Long, Rng As Range, a As Range Dim Rws2 As Long, rng2 As Range, c As Range Dim sh As Worksheet, ws As Worksheet We need to set the worksheets before we set the ranges Set sh = Sheets(“Sheet1”) Set ws = Sheets(“Sheet2”) Set the range … Read more

[Solved] Excel count rows where column B and C equals value and value in column A is a duplicate

October 14, 2022 by Kirat

Use SUMPRODUCT =SUMPRODUCT((B1:B8=”Sad”)*(COUNTIF(A1:A8,A1:A8)>1)*(ISNUMBER(SEARCH(“veggies”,C1:C8)))) 11 solved Excel count rows where column B and C equals value and value in column A is a duplicate

[Solved] how to calculate XIRR dynamically in excel and in google sheets

October 13, 2022 by Kirat

Assuming your table is in A1:G8 (with headers in row 1), and that your Fund of choice, e.g. “B”, is in J2, array formula**: =XIRR(INDEX(F:G,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8))))),N(IF(1,{1,2}))),CHOOSE({1,2},INDEX(A:A,N(IF(1,MODE.MULT(IF(B$2:B$8=J2,{1,1}*ROW(B$2:B$8)))))),TODAY())) Copy down to give similar results for Funds in J3, J4, etc. I tend to prefer this to set-ups involving OFFSET; not only is it briefer (and therefore more efficient), … Read more

[Solved] Transpose Column into Row with VBA [closed]

October 13, 2022 by Kirat

This is not so straightforward a problem because of having to consolidate the information by date. You also do not indicate what you want to happen should there be more than one identical code associated with a particular date. I chose to ignore it, and only list the unique codes, but you can modify the … Read more

[Solved] Breaking down sums into frequency (for histogram)

October 13, 2022 by Kirat

This should do the trick. If not, it will at least get you started. Sub Expand_Occurance() Dim ItemCounter As Long, shBottom As Long, NewItemRow As Long, OccuranceCounter As Long Dim sh As Worksheet Set sh = ActiveSheet shBottom = sh.Cells(Rows.Count, 1).End(xlUp).Row ‘get the bottom row of column 1 NewItemRow = shBottom + 1 ‘and the … Read more

[Solved] Vlookup all the values matching the search term instead of just one.

October 12, 2022 by Kirat

With Google Sheets use Query: =QUERY(A:A,”select A where A contains “”” & B3 &””””) Since you have the Excel tag use this formula for excel: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:INDEX(A:A,MATCH(“ZZZ”,A:A)))/(ISNUMBER(SEARCH($B$3,$A$2:INDEX(A:A,MATCH(“ZZZ”,A:A))))),ROW(1:1))),””) Copy/drag it down sufficient for your needs. 5 solved Vlookup all the values matching the search term instead of just one.