excel,excel-formula,excel-2010

The formula itself is ok, but you need to enter it as an array formula (press CTRL+SHIFT+ENTER when you entered it instead of simple ENTER)

excel,vba,excel-vba,excel-2010

strFileName contains the full path of the workbook. So you cannot use it in Workbooks(strFileName) since it only expects the workbook name. This is how you should do it: Dim wbName As String wbName = Split(strFileName, "\")(Ubound(Split(strFileName, "\"))) ' Get the WB Name For Each sheet In Workbooks(wbName).Worksheets ' Other...

If the row of the headers is static and your dataset is small (given you want to send emails, I'll assume it is) you can usethis kind of check method. In my example headers were in row 5 and I used the Like argument. For i = 6 To LastRow...

excel-vba,excel-2010,outlook-vba

Mail_Object.CreateItem(o) Shouldn't that be Mail_Object.CreateItem(0) 0 and not o In the below code, you are not required to set a reference to MS Outlook Object Library. I am using Late Binding with MS Outlook. Try this (Untested) I have commented the code so you shall not have a problem...

excel,excel-vba,excel-formula,excel-2010,excel-2007

The following formula will do it (array formula which has to be entered using Ctrl + Shift + Enter): =INDEX($C$5:$C$9,MATCH(1,($B$5:$B$9=$E5)*($A$5:$A$9=F$4),0)) I assume that: your data of Table 1 is in A5:C10 E5 has the value of "Widget" F4 has the date value This array formula should be entered in F5....

try SELECT table."NUMBER" AS a_number FROM table or create a view referencing the table and rename the column as required Aliases solve a lot of problems with names. I suspect that the issue you were having is not with running the query in Oracle but the result that comes back...

html5,excel,vba,excel-vba,excel-2010

You need to double the quotes where they are within a quoted string in VBA - so: pos1 = InStr(1, LCase(res), "<meta name=""dcterms.description"" content=""") pos2 = InStr(pos1, LCase(res), """/>") ...

excel,excel-formula,excel-2010

I think a VBA solution using Regular Expressions will be easiest in order to be able to return measurements like 1 1/2". To enter this User Defined Function (UDF), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu,...

excel,vba,excel-vba,excel-2010

You have two options. First, you can use a variable to store the range upon which you hope to act. If it's a one time procedure, variables aren't necessary, but if it's a repetitive task, or involves multiple references, use a variable. Option two is to refer directly to the...

excel,vba,excel-vba,excel-2010

You can't run UNION() on ranges from different worksheets. A Range is limited to a set of cells on a single sheet. EDIT#1: I suspect your Debug.Print. Your are printing a pixel-related variable rather than a row count Sub dural() Dim r As Range Set r = Range("A1:C5") MsgBox r.Height...

sorting,excel-2010,pivot-table,excel-2013,powerpivot

If you have more than one field in the row area of the pivot table, you cannot create a sort purely by value. The hierarchy cannot be ignored. That's how a pivot table works. Don't shoot the messenger.

excel,excel-vba,excel-2010,conditional-formatting

Data bars only support one color per set. The idea is that the length of the data bar gives you an indication of high, medium or low. Conditional colors can be achieved with color scales. What you describe sounds like a combination of the two, but that does not exist...

vba,excel-vba,excel-2010,submenu

I will show you a very simple example. Please amend it to suit your needs :) Private Sub Sample() Dim cb As CommandBar Dim cbc As CommandBarControl Dim newitem As CommandBarControl Dim newSubItem As CommandBarControl Set cb = Application.CommandBars(1) '~~> Delete Existing command bar control On Error Resume Next cb.Controls("Menu...

excel,if-statement,excel-formula,excel-2010,pivot-table

Your both IF functions return an empty string "". Using + operator with strings returns #VALUE!. There are different methods to fix it: use 0 instead of "" use SUM instead of + (it ignores strings). And as @John Bustos mentioned in his comment, you can simplify your formula with...

excel,vba,excel-vba,excel-2010

this an example how you can achieve required result Private Sub Worksheet_Change(ByVal Target As Range) Dim x&, y& If Target.Column = 6 Then y = 5: x = Target.Row While Cells(x, y) <> "": x = x - 1: Wend If Cells(Target.Row, 5) <> "" Then Cells(Target.Row, 6) = Cells(x,...

excel,vba,excel-vba,excel-2010

No loop is required: Sub Check_Ref() Dim rng As Range Dim rngError As Range Set rng = Sheets("All Projects").Range("AE7:AK7") Set rngError = rng.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) If Not rngError Is Nothing Then rngError.Clear End If End Sub EDIT#1 Disable error if no errors found on worksheet: Sub Check_Ref() Dim rng As Range...

To me, I think you should change the sheet layout a little, create a User Defined Function (UDF) and alter the formulas in your Sum rows for efficient row/column adding (to make use of Excel's formula fill). The only issue is that you need to save this as a Macro-Enabled...

excel,vba,excel-vba,excel-2010

Yes you can use the LIKE Operator with a wildcard "*". Here is an example. I am assuming that the workbook is open. Sub Sample() Dim wb As Workbook Dim wbName As String '~~> "MyWorkbook2015" wbName = "MyWorkbook" For Each wb In Application.Workbooks If wb.Name Like wbName & "*" Then...

excel,excel-vba,excel-formula,excel-2010,spreadsheet

you need to put this in cell C3 and fill to the right until the last column and then select the first row and copy/fill down until the last row: =C$2+$B3*C$2 the principle is to lock the row for your main values in the second row by adding a $...

vba,excel-vba,excel-formula,excel-2010

Here is a formula based solution. It assumes that you can create a new sheet to reform the data. Based on your description, sounds like this is what you want anyways. I have dummied down the example to have 4 categories and 3 repeats per row. Change the 4 and...

excel,excel-formula,excel-2010

Try =SUMIF(C2:C9,"Credit",B2:B9)-SUMIF(C2:C9,"Debit",B2:B9) The first Sumif sums up all the credits, the second Sumif sums up all the debits and the debit sum is then subtracted from the credit sum....

excel,vba,excel-vba,excel-2010

Here you go, split each of the 4 columns into an array on ";" then nested loop them all: Sub Cartesian() Dim MyStr1 As Variant, MyStr2 As Variant, MyStr3 As Variant, MyStr4 As Variant, Str1 As Variant, Str2 As Variant, Str3 As Variant, Str4 As Variant, X As Long MyStr1...

If you want to save your sanity, you can use Andy Pope's Ribbon Editor which will generate the callbacks automatically (at least the text for them). Then you just copy them into the VBA module. (I am not affiliated with that add-in, I just use it and recommend it highly)...

You don't seem to need to return any value from matching rows, just check for the existence of a four column match. A COUNTIFS function can accomplish this efficiently and without Helper column(s). Any non-zero value can be considered True but if you need to show TRUE on the worksheet,...

This call: FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) will replace the column names you have with the values from the first row. Since the first value under the column "Week" is the filename, then your table will now use that filename as the column name. You can fix this by adding the custom...

excel,vba,excel-vba,excel-2010,excel-2007

Here is a somewhat different approach. I have set up a user defined class called cUUID. The class has the properties of the UUID, Response, ResponseCode and a Collection consisting of the paired Response and ResponseCode. We create a Collection of this class object, where each member of the collection...

excel,vba,excel-vba,excel-2010

With only the starting, concatenated data in the active sheet and ID is in A1, run this macro. Sub split_out() Dim v As Long, vVALs As Variant, vID2s As Variant, vSTRs As Variant Dim rw As Long, lr As Long, mx As Long With ActiveSheet lr = .Cells(Rows.Count, 1).End(xlUp).Row .Cells(1,...

vba,excel-vba,excel-2010,word-vba,word-2010

There are a few options you can try: For Word: If you have Dim wordApp As Word.Application Dim wordDoc As Word.Document 1. wordApp.Visible = False '...copy chart wordApp.Visible = True 2. wordDoc.Windows(1).WindowState = wdWindowStateMinimize '...copy chart wordDoc.Windows(1).WindowState = wdWindowStateMaximize 'or wdWindowStateNormal . For Excel: 1. Application.Visible = False '...copy chart...

excel,vba,excel-vba,excel-2010

You may save a sub to your ThisWorkbook module. It will trigger on any worksheet activation event. The worksheet can be passed on as an argument to the sub called. Private Sub Workbook_SheetActivate(ByVal Sh As Object) Select Case Left(Sh.Name, IIf(InStr(Sh.Name, "-") > 0, InStr(Sh.Name, "-") - 1, Len(Sh.Name))) Case "SOD"...

excel,vba,excel-vba,excel-2010,excel-2007

The error was actually outside of the function, when the function is called. Instead of passing in a collection for DataSet, it passes in nothing.

excel,excel-vba,excel-formula,excel-2010

With some small changes, your UDF gives correct (Same/Change) results: Function ColorComparer(rColor As Range, rRange As Range) As String Dim rCell As Range Dim lCol As Long Dim vResult as String lCol = rColor.Interior.Color If rRange.Interior.Color = lCol Then vResult = "Same" ' vResult.Interior.ColorIndex = RGB(0, 255, 0) Else vResult...

Something like this should work for you: Public Function GetSearchArray(strSearch As String) As String Dim ws As Worksheet Dim rngFound As Range Dim strFirst As String Dim strWSname As String For Each ws In ActiveWorkbook.Sheets Set rngFound = ws.UsedRange.Find(strSearch, ws.UsedRange.Cells(ws.UsedRange.Cells.Count), xlValues, xlPart) If Not rngFound Is Nothing Then strWSname =...

While defining name range you should use the absolute reference for eg. If you want to set the name range to qw then its reference should be like =Sheet1!$A$1:$C$4 which means range get fixed. And if you drag the formula it will refer to same range

excel,excel-formula,excel-2010

Are you sure that you should be using a ; as opposed to a ,? I get an error for that, and it directs me to $A$30 as you stated.

TL;DR, couple options to resolve this, in order of preference: Stop using Select to access cells Qualify your call to Range("E4") when executing code in a Worksheet object by using Application.Range("E4") or Sheets("Draft").Range("E4") or ActiveSheet.Range("E4") Move the code to ThisWorkbook or a code module and call that Sub from the...

excel,vba,excel-vba,excel-2010

You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...

excel,vba,excel-vba,excel-2010

Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name <> "Front" Then OpenDataEntryForm End Sub ...

either A) adjust the formatting to number or B) do not multiply by 24 and simply adjust the formatting to [h]:mm

excel,vba,excel-vba,excel-2010

Give this a go, 1 more time... Sub SaveASDiBox() Dim FlSv As Variant Dim MyFile As String Dim sh As Worksheet Set sh = Sheets("Sheet1") sh.Copy MyFile = "YourFileName.xlsx" FlSv = Application.GetSaveAsFilename(MyFile, fileFilter:="Excel Files (*.xlsx), *.xlsx)", Title:="Enter your file name") If FlSv = False Then Exit Sub MyFile = FlSv...

excel,vba,excel-vba,excel-2010

Let's say your worksheet looks like this This is what I get when I run the code Further to my comment, see this The syntax is col.add "Item","Key". The key has to be unique. If you have duplicate values then then use OERN as I suggested or use a unique...

excel,excel-formula,excel-2010,excel-2013

You could simply turn around your first example and use the correct string: =TEXT(J3, "yyyyMM") Note, I used upper case M. Lower case m means minutes, while upper case M means month. The CONCATENATE is unnecessary. Please also note, that this is prone to localization issues, i.e. it depends on...

excel,vba,excel-vba,excel-2010

try this. note that you will need to have an empty column that will temporarly hold a mark if the value in the row has been found — in my example i used the "I" column, you will need to modify this if it's not empty. Private Sub pasteValues() Dim...

excel,excel-formula,excel-2010

If you wish to change a specific cell across multiple worksheets in the same workbook, you can make a '3D' selection as follows: Select the sheets you wish to update by Shift or Ctrl-clicking to select the tabs you need. You can also right-click on a sheet tab and choose...

excel,excel-2010,ssas,pivot-table

Go to the drop down filter that you have in the pivot table, click on Select Multiple Items and deselect ALL. Then in the search bar (again at the pivot table filter) write *e* and select add current selection to filter. Repeat for *h*. Do not forget to select add...

excel,vba,excel-vba,excel-2010

To know how many entries you have: dim listLength listlength = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row - 1 'I assumed column E, starting at row 2 You need to loop from row 2 to the row 2 + listLength, check the cell in column E, and check if it is equal to your...

Say we have data in column AIn C1 enter the array formula: =IFERROR(SMALL(IF(($A$1:$A$20)=MIN(A$1:A$20),ROW($A$1:$A$20),""),ROW()),"") and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. This will display the row number(s) of the cells containing the minimum value: ...

excel,excel-formula,excel-2010

Number starts at one. Then get the cell's row number and subtract one. Divide that number by 5 and discard the fractional part (or the remainder). So numbers from 0 to 4 (which are rows 1 through 5) all get an increment of 0, 5 to 9 get 1, and...

c#,excel,winforms,excel-2010,vsto

Ok I was able to fix the issue using the following code protected override void WndProc(ref Message m) { if(m.Msg == 528 && !this.Focused) { this.Focus(); } base.WndProc(ref m); } I added this function to my TaskPaneView which is simply a UserControl with that webbrowser child. I don't have a...

excel,vba,excel-vba,outlook,excel-2010

I wrote the solution just below my question.

Use the Columns property Dim rng as Range Set rng = ThisWorkbook.Worksheets(1).Columns(12) ' for column 12 ...

vba,excel-vba,excel-2010,pivot-table

I think you need VBA for this. By running the Macro Recorder while adding a date filter I came up with: Sub GetPivotFilterDates() Dim pvt As Excel.PivotTable Dim pvtField As Excel.PivotField Set pvt = Worksheets(1).PivotTables(1) Set pvtField = pvt.PivotFields("Date Range") With pvtField.PivotFilters(1) If .FilterType = xlDateBetween Then Worksheets(1).Range("A1").Value = "Filter...

excel,excel-2010,boolean-logic,named-ranges

Description of AND function (MS link): Syntax AND(logical1, [logical2], ...) The AND function syntax has the following arguments: logical1 Required. The first condition that you want to test that can evaluate to either TRUE or FALSE. logical2, ... Optional. Additional conditions that you want to test that can evaluate to...

If you want do define a range that is a variable offset from column J then consider: Sub dural() ' ' N will be the number of extra columns ' so if N=0 we want Range("J:J") ' and if N=1 we want Range("J:K") ' etc. ' Dim N As Long...

excel,vba,excel-vba,excel-2010

try this Sub UpdateW2() Dim Dic As Object, key As Variant, oCell As Range, i& Dim w1 As Worksheet, w2 As Worksheet Set Dic = CreateObject("Scripting.Dictionary") Set w1 = Workbooks("workbookA.xlsm").Sheets("Sheet1") Set w2 = Workbooks("workbookB.xlsm").Sheets("Sheet1") i = w1.Cells.SpecialCells(xlCellTypeLastCell).Row For Each oCell In w1.Range("D2:D" & i) If Not Dic.exists(oCell.Value) Then Dic.Add oCell.Value,...

excel-vba,excel-formula,excel-2010,vlookup,worksheet-function

I modified the code as below and it worked. I removed WorkSheetFunction and also handled the result value. Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow On...

excel,excel-formula,excel-2010,array-formulas,consolidation

Maybe try: =COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,TRUE)>0 ...

With a little help from others all i had to do was replace. Cell.EntireRow.Copy ActiveWorkbook.Sheets("MergedData").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) With this: Cell.EntireRow.Copy ActiveWorkbook.Sheets("MergedData").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues ...

excel,excel-formula,excel-2010

You may add one line above/below the header and fill it with just 0 or 1, then horizontally sum each line using an array formula, e.g. A B C D E F 1 Selector 1 1 0 0 2 Data 1 2 3 4 3 Formula in F2: {=SUM(B2:E2*B$1:E$1)} (enter...

excel-vba,excel-2010,vb.net-2010

After long hours of excruciating pain, I've finally did it! Apparently I was missing an "\" Since "sMonth" became dynamic name, which later I wanted to use as path, and save files in that folder. I needed to simply put that "\" after sMonth, to tell it to save inside...

arrays,vba,import,ms-access-2010,excel-2010

Private Sub Command5_Click() Dim fileInfoToBeImported(3, 1) fileInfoToBeImported(0, 0) = "Stock_CC" fileInfoToBeImported(0, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\Stock_getdata.xlsm" fileInfoToBeImported(1, 0) = "Wips_CC" fileInfoToBeImported(1, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\Wips_getdata.xlsm" fileInfoToBeImported(2, 0) = "CCA_cc" fileInfoToBeImported(2, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\SLAcc.xls" fileInfoToBeImported(3, 0) = "Eps_cc" fileInfoToBeImported(3, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\eps.xlsm" Dim...

excel,excel-formula,excel-2010

Try =INDEX($A$1:$A$9,COLUMN(A1))+INDEX($B$1:$B$9,COLUMN(A1)) copy across ...

excel,graph,datatable,excel-2010

Do these look like something you want to get? I used fake data just over 3 years. Each years is represented by a line. Each line spans over 4 quarters. To make this you select: 1) Series are years => 3 years = 3 series, Give each a name (e.g....

excel,vba,excel-vba,excel-2010

Since you are using a macro, you can take direct control of the material being output to the text file: Public Sub printDecimal() Dim st As String Close #1 Open "C:\TestFolder\testfile.txt" For Output As #1 st = Range("A1").Text st = Replace(st, ",", ".") Print #1, st Close #1 End Sub...

excel,excel-2010,conditional-formatting

Here is a tiny macro - you need to copy it to a module in VBA Project. Once there, put a cursor in the macro and press F5 :) 1) it works for rows from 1 to 800 (change it if you need it somewhere else) 2) it works on...

If I understood correctly, I think you will need to use another MATCH function to find the column of the MIN value. Once you know the column and the row, you can use the OFFSET function to retrieve the value of the cell. So below I am assuming you will...

excel,vba,excel-vba,events,excel-2010

Change the worksheet code to this : Simpler alternative (Edit 1) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$25" Then Call GasFlow End If End Sub Better alternative Works even when multiple cells are changed at once, including the "E25": Private Sub Worksheet_Change(ByVal Target As Range) If Not...

This depends on the chart type. A line chart will show the X axis as a category axis, and each value will be a marker on the chart. If the X values are dates, a line chart can accommodate that (i.e. put in spaces for missing days). If the X...

Add the total of all sites as a data series to the line chart and then add a trend line for the total series. Format the total series to show no line and only the trend line will be visible. In the screenshot the dotted line is a polynomial trend...

excel,excel-vba,ms-access,ms-access-2010,excel-2010

My guess is your code triggers a primary key violation when attempting to add a new row with a LocationID value which matches the LocationID value of another row. This is where the new row is added: .AddNew .Fields("LocationID") = Range("B" & r).Value Since you have this earlier in the...

Add a new column (to the left of your data) on both sheets called FULL_NAME, which is =CONCATENATE(FIRST_NAME,LAST_NAME) Then on WS1, MATCHED ID is =VLOOKUP(WS1!FULL_NAME,WS2![RANGE],[ID COLUMN],0)...

Perhaps: Sub range_reporter() Dim r As Range Dim nLastRow As Long ActiveSheet.UsedRange Set r = ActiveSheet.UsedRange nLastRow = r.Rows.Count + r.Row - 1 ActiveSheet.Name = ActiveSheet.Name & "(" & nLastRow - 1 & ")" End Sub However the count will include rows that may be empty within non-empty rows....

excel,vba,excel-vba,excel-formula,excel-2010

If you know the number of the column (in the ColNum variable) that you wish to sum then you can use: ActiveCell.FormulaR1C1 = "=COUNTIFS('Report'!C" & ColNum & ":C" & ColNum & ",""=In Force"")" If the name of the sheet is also in a variable, ReportName, then the formula is: ActiveCell.FormulaR1C1...

excel,vba,excel-vba,excel-2010

Here is a pretty dumb example......a UDF to return the first 7 primes in column form: Public Function Primes() ' ' Array UDF to return the first 7 primes ' Dim rN As Long, ary(1 To 7) As Long Dim tdim As Long, i As Long Dim wf As WorksheetFunction...

excel,vba,excel-vba,excel-2010

It seems you need to pass one more parameter to the function: Option Explicit Dim bWeStartedOutlook As Boolean Function AddToTasks(strDate As String, strText As String, DaysOut As Integer, email as String) As Boolean Dim intDaysBack As Integer Dim dteDate As Date Dim olApp As Object ' Outlook.Application Dim objTask As...

excel,excel-2010,conditional-formatting

Please try selecting from top left to bottom right rather than vice versa. Without the anchor ($) for rows (has to be left off for the one formula rule to apply throughout the range) the row references are relative - but relative to the active cell rather than to the...

excel,excel-2010,powerpivot,powerquery,powerbi

The specific bulk-import requested cannot be performed in the standard Excel 2010 and PowerBI interface at this time. Edit: It's now possible to avoid the necessity of bulk-importing your Power Queries into PowerPivot, by creating the connections directly. See blog at: http://www.excelguru.ca/blog/2015/04/29/load-power-query-directly-to-power-pivot-in-excel-2010/...

excel,vba,excel-vba,excel-2010

Remove all sheet protection Open the VBE (Alt + F11) Go to the immediate window (Ctrl + G) Type ActiveSheet.Protect Password:="myPassword", UserInterfaceOnly:=True and press Enter The active worksheet is now protected with the password "myPassword", however the UserInterfaceOnly argument means that your code is still allowed to run on...

excel,vba,excel-vba,excel-2010

I would recommend that you avoid activating the worksheet in order to use it. Dim header_cell As Range With Sheets("Report") 'this searches just row 1 'Set header_cell = .Rows(1).Find(what:="Status", lookat:=xlWhole, MatchCase:=False, searchformat:=False) 'this searches the whole worksheet Set header_cell = .Cells.Find(what:="Status", lookat:=xlWhole, MatchCase:=False, searchformat:=False) Debug.Print header_cell; "Column: " & header_cell.Column...

excel-vba,if-statement,excel-2010

You're not indexing the collection correctly. Use Worksheets("Side").Range("B1") = "yes" Note the plural....

=IFERROR(MATCH(TRUE,ISNUMBER(SEARCH({"BMW","Mazda","Mercedes"},A2)),0),"") Add more manufacturers as required. Must be entered as an array formula in B2 using Ctrl Shift Enter ...

In your original code you've got this block: ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count Next lngCount For Each strFilename In .SelectedItems MsgBox strFilename Next End With Which already does what you want....

excel,excel-vba,excel-formula,excel-2010

If =OFFSET(A2;0;0;COUNTA(A2:A99999);1) is your full formula you are getting a result with a hard-coded number due to Inferred Position (I hope that's the right term). This means that when you are referencing a range but are not processing the full range the operative cell is the one relative to the...

vb.net,excel,ssis,excel-2010,vb.net-2010

You can use a simple SELECT to verify your file: Dim blnMissingColumns As Boolean = False Dim cmdTest As OleDbCommand = xlConnection.CreateCommand cmdTest.CommandText = "SELECT TOP 1 trayid, trayname, description, quantity FROM [Tray Header$]" Try cmdTest.ExecuteNonQuery() Catch ex As Exception If TypeOf ex Is OleDbException Then If CType(ex, OleDbException).ErrorCode =...

excel,excel-vba,excel-formula,excel-2010,excel-2007

Alright, this is not very elegant, but the first thing that I came up with, that kinda works. use this formula to delete the last word in your bulk of text ("members") Assuming your text is in A1: =LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) This formula gets you the last word of a...

vba,excel-vba,excel-formula,excel-2010,excel-udf

Here is a formula-based alternative that will extract the first 5 digit number found in cell A1. I tend to prefer reasonably simple formula solutions over VBA in most situations as formulas are more portable. This formula is an array formula and thus must be entered with Ctrl+Shift+Enter. The idea...

excel,excel-formula,excel-2010,formula

1st SUMPRODUCT could become =COUNTIFS(Sheet1!$N:$N,$A4,Sheet1!$H:$H,"1A*",Sheet1!$M:$M,"<>service catalog",Sheet1!$J:$J,"incident",Sheet1!$I:$I,"<>self-serve",Sheet1!$AK:$AK,AFM$1,Sheet1!$E:$E,">="&$E$1,Sheet1!$E:$E,"<"&$E$2) The LEFT part can be handled by a wildcard, as shown change the second part along the same lines...

vb.net,excel,excel-formula,interop,excel-2010

The error might be coming from your current data, respectively, the layout of the sheet. I would suggest you to check what is inside the listO.Range(i, j).FormulaR1C1 before you assign the formula. I have had a case where the range has already got wrong data inside, and then strangely, I...

Could you clear/delete the blank range before you paste data in? range(cells(2,1),cells(2,1).end(xldown)).EntireRow.Clear ...

It is not completely clear what result do you expect for different values of CMVAR, but looking at your formula, I suppose you want it to be: You can calculate Result with the following formula: =EOMONTH(CMVAR,-MAX(MOD(MONTH(CMVAR)-4,12),3)) If the picture above does not show your expected output, can you please prepare...

regex,excel,excel-2010,spreadsheet

A simple approach would be to use LEFT with FIND. Something like this =LEFT(A1,FIND("/",A1,9)) ...

excel,excel-vba,excel-formula,excel-2010

This is very easy if we prepare to do it. Say we have data like: The first step is to introduce a "helper" column. In E2 we enter: =IF(SUBTOTAL(3,A2:A2)=0,MIN($E$1:E1)-1,SUBTOTAL(3,$A$2:$A2)) and copy down: Now the cool thing about the "helper" column is that no matter how you filter it, it always...