First, your formula =sheet2!NOT(ISBLANK($G2)) + IF($F5>2, 0, 1) is not using proper Excel syntax. The sheet reference goes with the cell reference, not outside the general formula. Also, your formula checks if F5 is greater than 2, not less than 3. There are 2 conditions : 1st to check the...

Using formula in column B: =MID(A1,LEN("http://www.testing.eu/test") + 1, LEN(A1) - LEN(".html") - LEN("http://www.testing.eu/test")) Using VBA and Regex: Sub GetNumberFromURL() Dim regEx As Object, matches As Object, cl As Range Set regEx = CreateObject("vbscript.regexp") regEx.Pattern = "([0-9]+)" For Each cl In Range("A1:A10") //Update to suit your needs Set matches = regEx.Execute(cl)...

This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...

I hope this can help more. This code may not work 100% but it should be good enough to guide you. Let me know if you have questions. Dim WS As Worksheet Dim Results(7, 1000000) As String ''Didn't know what is a good data type or how many possible results...

r,excel,statistics,dataset,google-adwords

Try library(data.table) setDT(df1)[, list(Clicked=rep(c(1,0), c(Clicks, Impressions-Clicks)), Converted=rep(c(1,0), c(Conversions, Impressions-Conversions))) , Keyword] # Keyword Clicked Converted # 1: SampleName 1 1 # 2: SampleName 1 1 # 3: SampleName 1 0 # 4: SampleName 1 0 # 5: SampleName 1 0 # 6: SampleName 0 0 # 7: SampleName 0 0...

excel,vba,excel-vba,outlook,format

HTML tags do work. I don't know why you say they don't. sBody = "All,<br /><br />Please Approve attached request for " & rType & ".<br /><br /><strong>Customer:</strong> " & customer & "<br />" then instead of the .Body property, use .HTMLBody .HTMLBody = sBody ...

Here is one of the possible solutions: Option Explicit Sub RetrieveAvgPrice() Dim sUrl, sContent, sPrice sUrl = "http://www.zoopla.co.uk/home-values/london/bayswater-road/" With CreateObject("MSXML2.XMLHttp") .Open "GET", sUrl, False .Send "" sContent = .ResponseText End With With CreateObject("VBScript.RegExp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = "Avg\. asking price[\s\S]*?class=""price big"">([\s\S]*?)<" sPrice =...

You are correct about potentially using InStr(). Here is a tiny example of searching a column: Sub SeekingHappiness() Dim A As Range, r As Range, s As String s = "happiness" Set A = Range("A1:A10") For Each r In A If InStr(1, r.Value, s) > 0 Then MsgBox r.Address &...

This will do what you want, it polls through from the bottom up, if it encounters a number in C and it is > 1 then it will insert the number of rows equal to column C number - 1 then copy the data from the host row. This will...

You need to select a 2 col by 3 rows range before entering the formula, e.g. The range can be larger - then all result cells not included in the (untransposed) source range are filled witn #N/A; if the result range is too small (like in your example) you get...

I think You'r missing some classes "UnsupportedFileFormatException" try to change the poi versions to the same and dont use the 3.11-beta2 You can use both in version 3.12 http://mvnrepository.com/artifact/org.apache.poi...

You explained everything very well, and the images you uploaded helped What your code is doing seems to be correct, but the error is complaining about one of the parameters, and it could be the 2nd one: .BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1 ConnectionSite: "A connection site on the shape specified by ConnectedShape....

If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...

excel,visual-studio-2010,excel-dna

Ok - it looks like you have to stop the ExcelDNA.Integration reference from copying locally to get this. Original thread I found here: https://groups.google.com/forum/#!topic/exceldna/MeYq0-LiGLM...

The logic here is: (1) Find the date for each subject that is the principal date, and return it for each row; and (2) subtract this date from the current date in col B. (2) is easy, but (1) requires a way to match the value in B on both...

This appears to be a good candidate for an INDEX-MATCH formula, with a multi-condition MATCH() formula to account for both ID criteria. The formula below places the raw data are in A1:C4 and the reformatted data in E1:H2, so you will need to change the references: In cell F2, I've...

Please consider the following formula to resolve your issue: =IFERROR((COUNTIF('Sheet 3'!B4:F4,"N"))/(COUNTIF('Sheet 3'!B4:F4,"Y")+(COUNTIF('Sheet3'!B4:F4,"N"))),0) Regards,...

Starting with: Running this small macro: Sub TwoDee() Dim s1 As Worksheet, s2 As Worksheet Dim N As Long, i As Long, v1 As String, v2 As String, v3 As Long Dim iRow As Long, iCol As Long Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") s2.Cells.Clear N = s1.Cells(Rows.Count,...

The COM object model that Excel provides, together with the set of events exposed by the Application, Worksheet and other objects, is the richest set of information available from Excel. VSTO, and any other type of add-in (including those built with Excel-DNA), are restricted by the same COM object model...

=month(A1*1) works because, as you guessed, the *1 turns the text into a number. I think the problem may be rooted in the format of the file you are importing. There may be non-printing characters in it? Or maybe it's an issue caused by US / European dates (this is...

If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...

excel,google-spreadsheet,excel-formula,formula,countif

You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...

javascript,excel,vba,excel-vba

This should do it: newSheet.Move(null, iWorkbook.Sheets("Payable")) ...

First, create your Font with bold styling, using the Workbook object. Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); Next, grab the RichTextString from the Cell and call the applyFont overload that takes a range of indexes and the Font to apply. RichTextString rts = cell.getRichStringCellValue(); rts.applyFont(0, 8, font); You should reuse the...

You should check the value of A1 first with an IF statement and apply the appropriate formula. Something like: =IF(A1="All",SUMIFS(Amount,Type,"<>""",Amount,">0"),SUMIFS(Amount,Type,$A$1,Amount,">0")) or more better (courtesy of @pnuts): =IF(A1="All",SUM(Amount),SUMIFS(Amount,Type,$A$1,Amount,">0")) ...

put your code under the textbox_change event Following works fine Private Sub TextBox1_Change() If Me.TextBox1.Value < 0 Then Me.Label1.Visible = True Else Me.Label1.Visible = False End If End Sub ...

vb.net,excel,visual-studio-2010

Use StringBuilder.Clear before each row. For rr As Integer = 10 To rowct str.Clear() For cc As Integer = 1 To colct I would also suggest calling My.Computer.FileSystem.WriteAllText once per row since you are writing to the file on each columns....

We could use the devel version of data.table i.e. v1.9.5 which can take take multiple value.var columns and reshape the 'long' form to 'wide'. Instructions to install are here. library(data.table)#v1.9.5+ names(data)[2] <- 'objective' dcast(setDT(data), student~objective, value.var=c('attempt', 'score')) # student attempt_objective1 attempt_objective2 score_objective1 #1: student1 90 20 6 #2: student2 27...

You can't do that with the EXPORT or COPY TO commands. To put data into multiple sheets in Excel, you need to use Automation. The fastest approach is probably to use EXPORT or COPY TO to create multiple workbooks, and then use Automation to consolidate the data into a single...

sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2

Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...

I'll give two answers. The first requires the matches to be in the same rows for column C and D. So if A2 matches C3,C4,C5 then B2 will need to match D3,D4 or D5 =IF(SUMPRODUCT(--($C$1:$C$5=A1),--($D$1:$D$5=B1))>0,1,0) From the inside out --($C$1:$C$5=A1) compares A1 to all the values in C1 to C5...

excel,vba,excel-vba,ms-project

Would it be possible, to write in a whole row, instead of the single cells? Would that be faster? Yes and yes. This is exactly where you can improve performance. Reading/writing to cells is notoriously slow. It matters very little how many cells you are reading/writing, but rather how...

In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...

Try this code, you need to concatenate the String values. They need to be enclosed within single quotes. I have also removed .Value as this becomes redundant. One final suggestion is that you add an Else part to the AfterUpdate event. When the value is Complete you need something, but...

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....

cell.row and cell.column should give you the addresses you need. Here is what I got from the documentation: https://openpyxl.readthedocs.org/en/latest/api/openpyxl.cell.html?highlight=.row#module-openpyxl.cell.cell It gives you the list of attributes of a cell object.

You can use Indirect() For example 'AUA Summary'!$D$9 can be written as INDIRECT("'AUA Summary'!$D$9") This way even when the columns move, it will refer to the same cell. The other way is to use Index For example D9 in Excel 2007+ can be written as INDEX(1:1048576,9,4) or INDEX(INDIRECT("1:" & ROWS(A:A)),9,4)...

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...

To find and move the "ID" column like 1th column Sub movecolumn() Dim sht As Worksheet Dim keySrc As String Dim lastcol As Long, cutCol As Long Dim arrcol As Variant Set sht = ThisWorkbook.Worksheets("Sheet1") keySrc = "ID" lastcol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column 'find the last Headers columns arrcol = Range(Cells(1,...

I got the solution after going through many tutorials and hence posting here for reference of any one who needs help. Sub testSort() Dim CurrentSheet As Worksheet Set CurrentSheet = ActiveSheet lastRows = CurrentSheet.Cells(Rows.Count, 1).End(xlUp).Row lastCols = CurrentSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set sortA = CurrentSheet.Range(Cells(2, 1), Cells(lastRows, lastCols)) CurrentSheet.Sort.SortFields.Clear CurrentSheet.Sort.SortFields.Add Key:=Range(Cells(2, 2),...

One example of a working function would be: =CONCATENATE("INSERT INTO TABLE MyTable (Col1, Col2) VALUES ('", A2, "', ", IF(ISBLANK(B2), "NULL", CONCATENATE("'", B2, "'")), ")") This assumes that you are inserting two values from the same Excel row, adds quotes around values to allow you to insert text strings, and...

I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...

This can be simplified pretty easily to affect the cells from 2 to the current row, six columns to the right- Sub tester2() Dim col As Integer col = ActiveCell.Column + 6 Dim row As Integer row = ActiveCell.row Dim rng As Range Set rng = Range(Cells(2, col), Cells(row, col))...

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...

excel,vba,excel-vba,loops,doevents

How about changing your 'do until' loop to a 'for next' loop? Something like?... Sub rowinput() Dim lngInputStartRow As Long Dim lngInputEndRow As Long Dim row_number As Long lngInputStartRow = Range("A1").Value 'specify your input cell here lngInputEndRow = Range("A2").Value For row_number = lngInputStartRow To lngInputEndRow DoEvents Next row_number 'Then a...

excel,excel-vba,cell,calculated-columns,calculated-field

You need to determine the row of last non-blank cell in the column. The method for this would depend on whether there are blank cells in the middle, for instance. Two alternatives are (taken from here*): =SUMPRODUCT(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94)))) =INDEX(MAX(($AK6:$AK94<>"")*(ROW(AK6:AK94))),0) Then you can use this value with OFFSET to get a reference...

java,html,excel,apache-poi,jsoup

I ended up achieving this by adding to my original class that created the xls file using jsoup and apache POI. I was able to retrieve the contents of the 'style' tags for all html elements as Strings using jsoup and then parse those Strings looking for font-family, font-size, etc,...

The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...

Assuming your events are in different columns, something like this would work - Sub test() Dim wsOrig As Worksheet Set wsOrig = ActiveSheet Dim wsDest As Worksheet Set wsDest = Sheets("Sheet2") Dim lcol As Integer lcol = Cells(1, Columns.Count).End(xlToLeft).Column Dim lrow As Integer lrow = Cells(Rows.Count, "A").End(xlUp).row Dim x As...

I tackled this by using 2 instances of Workbook_Open inside an excel Addin. When a file is loaded, the addin starts up, and checks to see if there are any active workbooks. If there is none, then we wait a little bit and check again, looped as many times as...

excel,vba,excel-vba,user-defined-functions

You need to use Application.Caller. This will return the value in cell A1 of the sheet the function is entered to: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Cells(1, 1) End Function This will return the name of the calling sheet: Public Function DisplayCaller() As String DisplayCaller = Application.Caller.Parent.Name End...

Have difference as an Array, and increment the component corresponding to the current Column. You can do something similar with matches if you want. Plus, you can abbreviate your two Ifs. Dim difference(1 To 5) As Long Dim matches(1 To 5) As Long For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange Dim col...

Here's a generic solution where you don't need to know the names of the subfolders. This will find all the subfolders and process the spreadsheets in each of them. You need to reference the Windows Script Host Object Model, which you do by clicking the Tools Menu, References..., and then...

updated using variant provided by lori_m But I wonder if there are any native functions ... use this Sub test() Dim r As Range, c As Range With Sheet1 Set r = .[B2:E10] Set c = .[C2] End With If Not Intersect(r, c) Is Nothing Then Debug.Print "Column in sheet:...

Try with this solution which works for current instance of Excel: On Error Resume Next Dim tmpWB As Workbook Set tmpWB = Workbooks("Acc_FR044_SAP.xls") On Error GoTo 0 If tmpWB Is Nothing Then WbOpen = False Else tmpWB .Close SaveChanges:=False End If ...

Given your criteria -- that 322 is represented as 3 and 2045 is 20 -- how about dividing by 100 and then rounding towards 0 with trunc(). time_24hr <- c(1404, 322, 1945, 1005, 945) trunc(time_24hr / 100) ...

Please consider this VBA script to resolve your inquiry: Sub LookupOuput() Dim OrderNumberColumn As Range Set OrderNumberColumn = Worksheets("batches").Range("B2:B1384") Dim LookUpRange As Range Set LookUpRange = Worksheets("OrderLvl").Range("C:DL") Dim cell As Range Dim FindResult As Range For Each cell In OrderNumberColumn If Not cell.Value2 = Empty Then Set FindResult = LookUpRange.Find(what:=cell.Value2)...

string,excel,if-statement,comparison

We need an Array formula. In G2 enter: =NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0))) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. Note: The curly brackets that appear in the Formula Bar should not be typed....

@nbayly is correct, you can't concatenate an entire range like this, and even if you could you are not assigning the result to anything. Here is one way to do it using a different technique. The test data looks like this: Make sure you have either the column or header...

It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...

Try the following which uses the SpecialCells() method to select only cells that are currently visible on screen (i.e. not filtered out). count = Application.WorksheetFunction.CountA(Range("A:A").SpecialCells(xlCellTypeVisible)) ...

Create a special sub to perform the initialization and run it first: Dim DataSheet As Range Sub RunMeFirst() Set DataSheet = ThisWorkbook.Sheets(1).Range("A3:FU5002") End Sub ...

DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...

A boolean would most likely not yield better performance than integers, since the Excel formula engine is dynamically typed. To significantly improve the performance of your spreadsheet, you should probably consider other options. Excel PowerPivot comes to mind, as it can easily handle millions of records with hundreds of calculations,...

In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...

By my reading, it seems as if you may be trying to do both tasks in a single formula. This may be possible with a more complex formula, but a straightforward solution is to simply add a column with activity counts to the raw data, and then count the instances...

excel,excel-formula,excel-2013

Commenting in an answer so as to add a picture. But your second formula also seems to work fine: Note that with a 2 in B3, the formula returns B from Sheet_2!B2...

The problem lies in the GetValue function. When there is no value below the header, the range selection ends up selecting the empty cell plus the heading above it. You have also not properly implemented the If Len(v) = 0 Then from a previous post. You have added it in...

You nearly had it! However, in an array formula, you cannot replicate an "AND" construction so straightforwardly, in essence since the return from the AND function is always a single value, never an array. Hence, your attempt: =MAX(IF(AND(A1:A8="A",B1:B8<>""),B1:B8)) would initially correctly resolve to (using the values you posted): =MAX(IF(AND({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}),B1:B8)) though...

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

Fix: Had it print empty " " down the range equal to whatever is occupied in column 3. Section that fixed the code: (4) ... Else 'if no items are under the HOLDER header StartSht.Range(StartSht.Cells(i, 2), StartSht.Cells(GetLastRowInColumn(StartSht, "C"), 1)) = " " ...

excel,vba,excel-vba,excel-2007

The comments made by @Paradox and @Tim are all valid. To specifically answer your question, you cannot change the ActiveCell from code but instead use the Range or Cells to set a reference to the range: Public Sub PriceSearch(SaSh As Worksheet) Dim StartNumber As Integer Dim EndNumber As Integer Dim...

You'd have to setup a second column for Field2 and for Field3. If you assume your dropdowns are located in cell A10 to C10 and your fields are in column A-C then you could go over to column D and make D1 =if($a$10=a1,b1,"") and D2 would be if(countif(d$1:d1,if($a$10=a2,b2,""))=1,"",if($a$10=a2,b2,"")). You could...

You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA. One option for the formula is to use OFFSET, as in =SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1)) This sums all values from Cell1 to Cell2, in the...

The error message says your hyperlink address is invalid. Try generating it as: string strp="#'" + i + " of " +nfaultCount + "'!" + strRange1; // Sample result: "#'4 of 5'!A3 instead of string strp="#" + i + " of " +nfaultCount + strRange1; // Sample result: #4 of...

You could of course always use Excel VBA, as you are already working with Excel. The following sub exports the used range of the current sheet as valid JSON into a .js-file: Option Explicit Sub jsonex() Dim fname, q$, str$, lineend$, na, va, ur As Range, i%, j%, ncols%, nrows%...

Have you tried Goal Seek, the disadvantage is it will only change 1 cell. Using the Solver will change all three cells, but you then need to set constraints on the cells in column B.

One way: Sub qwerty() lastrow = 10 For x = 2 To lastrow If InStr(Sheets("Sheet1").Cells(x, 3), "TFMODE") > 0 Then MsgBox Sheets("Sheet1").Cells(x, 3).Address(0, 0) End If Next x End Sub ...

excel,excel-vba,table,formatting,page-break

Just change the page margins and the top few rows get excluded, but the manual page break is still required though!

excel,matlab,cluster-analysis,k-means,geo

I think you are looking for "path planning" rather than clustering. The traveling salesman problem comes to mind If you want to use clustering to find the individual regions you should find the coordinates for each location with respect to some global frame. One example would be using Latitude and...

If you use Worksheet.AutoFilter.ShowAllData instead of Worksheet.ShowAllData it will not throw the error when nothing is filtered. This assumes that Worksheet.AutoFilterMode = True because otherwise you will get an error about AutoFilter not being an object. Public Sub UnFilter_DB() Dim ActiveS As String, CurrScreenUpdate As Boolean CurrScreenUpdate = Application.ScreenUpdating Application.ScreenUpdating...

excel,powerpivot,powerview,powerbi,powerquery

This is not something that's supported in Power View. HTH, -Lukasz

Here is the code that will compare sheet1 and sheet2(corresponding cells ) and according wite the correct value or Mismatch based upon the result into sheet3. Sheet1 and sheet2 wil have same number of rows and columns and the headers be same so you can keep them as it is...

User the formula =LEFT(TEXT(A1,"+0.000000000;-0.000000000"),12) where A1 contains your number. This will be a text cell type but will format the cell exactly how you want it....

Dim sqlConnection1 As New SqlConnection("Your Connection String") Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "Select DESC1 FROM Master WHERE '" & TextBox1.Text & "' " cmd.CommandType = CommandType.Text cmd.Connection = sqlConnection1 sqlConnection1.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. If reader.HasRows...

I use Double in reading Datatable. The double is nullable. The answer for this question under the link below: double and null in c#...

I would generally not try and amalgamate so much together in a standard cell in this way - it tends to cause confusion (as you have found) With that in mind, the below appears to do what you want: ="INSERT INTO table_1 VALUES("&A2&",'"&B2&"','"&C2&"','"&D2&"',"&E2&","&F2&", "&IF(ISBLANK(G2),"Null","'"&G2&"'")&",'"&H2&"','"&I2&"');" You had some extra brackets and...

SOLUTION: needed to change the function for finding the values under the headers '(8) 'Get the Values from columns with specified headers Function GetValues(ch As Range, Optional vSplit As Variant) As Scripting.Dictionary Dim dict As Scripting.Dictionary Dim dataRange As Range Dim cell As Range Dim theValue As String Dim splitValues...

excel,powerpivot,powerview,powerbi,powerquery

If you use a slicer within your tile it will almost do what you want. However, you wouldn't get the nice icons for warning & error to be the source of selection. You could make it more visual by separately having a table that includes the images so that when...

CSV file is interpreted a sequence of characters which comply to some standardization, therefor it cannot contains more than one sheet. You can output your data in a Excel file that contains more than one sheet using the Apache POI api.