How to Programming with Excel

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

Excel 2013 Add a Connector Between Arbitrary Points on Two Different Groups

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

Referencing a new inserted column Excel VBA

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

Using a cell's number to insert that many rows (with that row's data)

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

Doing a same action to all the subfolders in a folder

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

Lookup and cell referencing

This is how you can achieve it: =LOOKUP(D4,Roster!C$3:C$184,Roster!D$3:D$184) The $ locks the cell reference....

Converting column from military time to standard time

r,excel

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

adding variables into another variable vba

excel,vba,excel-vba,excel-2010

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

Way to capture double click to open file?

excel,vba,excel-vba,add-in

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

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

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

VBA “Compile Error: Statement invalid outside Type Block”

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

Range, Select, Change contents, Allignment or Offset?

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

How do I get a cell's position within a range?

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

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

C# code to add hyper link in excel sheet

c#,excel

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

Retaining original table formatting after a 'pagebreak'

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

Excel - select a cell based on adjacent cell value

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

Excel VBA Loop Delete row does not start with something

vb.net,excel,visual-studio-2010

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

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file

java,excel,apache-poi,writing

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

How to output a single file for each row of an excel file read in?

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

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba,ms-project

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

Conditional Maximum in Excel using Array Formulae - how to ignore Blank Rows

excel,array-formulas

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

Slow VBA macro writing in cells

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

Ordering values from random list in Excel

excel,visual-studio-2010,excel-dna

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

Return #N/A error from ExcelDNA

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

Excel: match two columns with two other columns

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

If statement and addition

excel,excel-2010

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

How to write formula for cell in Excel?

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

Is it possible to output to a csv file with multiple sheets?

java,excel,csv

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.

VBA - do not grab header in range

excel,powerpoint,spreadsheet

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

Excel - Pulling data from one cell within a list

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

Retrieve Number from a website into Excel

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

How to convert excel data to json at frontend side

excel,user-interface

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

Copying sheet to last row of a sheet from another workbook

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

Check if excel file is open, if yes close file,if no convert csv file to excel Visual Basic [duplicate]

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

Converting ADODB Loop into DAO

excel,vba,ms-access,ado,dao

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

parsing seconds from mm.ss.00 string

Please try: =RIGHT(A1,LEN(A1)-FIND(".",A1))+60*LEFT(A1,FIND(".",A1)) This treats the likes of 1.44.72 as a string and manipulates it to strip off the numeric characters up to and including the first full stop (period in USA). The part including and after the plus sign may not be required if the minutes are to be...

Best way to return data from multiple columns into one row?

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

Need to export tables inside a
as excel, keeping filled-in input and option data

javascript,html,excel,export,element

I will give you a hint for a lazy solution. On page load clone that div with cloneNode and keep it as a variable. When you need to do the export just export that reference (not the actual div). On the other hand, if you need to keep some other...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

excel,excel-vba,properties,listbox

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

How do I store a SQL statement into a variable

sql,excel,excel-vba

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

How do I do to count rows in a sheets with filters? With a suppress lines

excel,vba,filter

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

if condition inside Insert query in excel?

mysql,excel

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

Excel VB Listbox losing Value and Text Property

I saw that one of the listboxes was filled with a named range, I just removed the named range and filled the listbox manually, this seems to work fine and didn't give me more problems.

Sort multiple columns of Excel in VBA given the top-left and lowest-right cell

excel,vba,excel-vba,sorting

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

How to create nested tiles in Power View

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

K-Means Clustering a list of US addresses based on drive time

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

Concatenate N number of columns between 2 specific column name in VBA Excel

excel,excel-formula,countif

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

How Do I Transform This CSV / Tabular Data Into A Different Shape?

excel,csv

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

Counting across rows for ###, then countif down columns for ids matching ###

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

Simple Enquiry with Complex Answer - How do I Select RowA6-Row(last non-blank) for a simple formula

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

Errors 91 and 424 when iterating over ranges in Excel VBA

excel,vba,excel-vba,range

I think the error is because, as mentioned in the comments, that your "for each" isn't being used correctly. Try this: Dim cel Set nonZeroes = Range(Cells(1, 1), Cells(10, 1)) ' You need to set the range to search through here. For Each cel In nonZeroes question = isTouching(cel.Value, firstfeat)...

Identifying cell in Openpyxl

python,excel,openpyxl

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.

Conditional Formatting for every row

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

How to apply bold text style for a range of text inside a cell using Apache POI?

java,excel,apache-poi

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

timestamp SQL to Excel

php,mysql,sql,excel

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

Transpose in excel doesn't work, it just copies one cell

excel,transpose

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

Deconstruct Excel Formula To Get Result Without Trying Values

excel,formula

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.

Sumifs and Criteria - how to set “all” criteria?

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

12 Characters Including leading and following zeros

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

Compare two data sets in different sheet and print difference in other sheet using excel VBA?

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

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

How to insert excel formula to cell in Report Builder 3.0

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

Unable to extend VLOOKUP formula

excel,excel-2010

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

Hiding #DIV/0! Errors Using IF and COUNTIF in Excel 2010

excel,vba,if-statement

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

Specifying a range to be applied to all subs in a module?

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

Checking for null value (Empty Cell) in Excel file

c#,excel

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

Excel build bar graphs with oddly formatted data

I would do this in three steps: Fill in the missing values for your first column. This is a bit manual. If the data is huge you consider writing a quick vba subroutine. At any rate, make the data look something like: Highlight this data and turn it into a...

VBA - Unable to pass value from Private to Public Sub

excel,microsoft,powerpivot,powerbi

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

Microsoft Power BI Designer data model to Excel or PowerPivot

From my understanding, Power BI Designer files are not compatible with Excel. Please see the following link:http://blogs.technet.com/b/powerbisupport/archive/2014/12/19/power-bi-designer-preview.aspx We also cannot cross between Excel and the Power BI Designer. For example, if we have a Data Model in Excel, I can't use that and create reports within the Power BI Designer....

Export data to Excel sheets ? Not one sheet?

c#,excel,export,foxpro,sheet

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

How to Export an HTML table as an Excel while maintaining style and applying freeze pane

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

Excel DNA how to get events for each minor change in spreadsheet?

c#,excel,vsto,excel-dna

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

Excel VBA - ShowAllData fail - Need to know if there is a filter

excel,vba,excel-vba,filter

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

display inside a column the number from a string

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

Count amount of differences and return column name with count

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

VBA - printing empty cells

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

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)) = " " ...

Counting values embedded in strings inside a column (Google Spreadsheets)

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

Formatting specific part of text string in VBA

excel,vba,excel-vba,outlook,format

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

Type Mismatch Error in DLOOKUP Function

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

Find column with unique values move to the first column and sort worksheets

excel,vba,excel-vba,sorting

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

Creating dynamic dropdowns in Excel where values may appear in more than one column

excel,excel-formula

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

Excel Search VBA macro

r,excel,statistics,dataset,google-adwords

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

Interface Controls for DoEvent in Excel

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

Disaggregate one row of data to multiple rows

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

Apache POI getRow() returns null and .createRow fails

java,excel,apache-poi

See the documentation for the SXSSFWorkbook constructor that takes the XSSFWorkbook as param. You cannot override or access the initial rows in the template file. You are trying to overwrite an existing row and the API does not support this. Your exception message reflects this. https://poi.apache.org/apidocs/org/apache/poi/xssf/streaming/SXSSFWorkbook.html#SXSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) For your use case,...

Match Function in Specific Column Excel VBA

excel,vba,excel-vba,match,worksheet-function

What you mean to do is better served with Range.Find. Dim rngtrg As Range, rngsrc As Range Dim ws As Worksheet Set ws = ActiveSheet Set rngsrc = ws.Range(ws.Cells(1,colnumvar),ws.Cells(1000,colnumvar)) Set rngtrg = rngsrc.Find(1,...) rowvar = rngtrg.Row ...

Excel VBA (via JavaScript) - Moving a Sheet to a new location

javascript,excel,vba,excel-vba

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

Using a stored integer as a cell reference

excel,excel-vba,reference

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

Dates not recognized as Dates, despite format

excel,datetime

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

Label linked to a text box value

excel,vba,textbox,label

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

Comparing cell contents against string in Excel

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

VBA search each cell iteratively for a string

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

Excel vlookup expansion

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

Finding position of a particular string containing cell in Excel

excel,excel-formula,excel-2013

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 - How to concatenate 2 values to make a refference

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

If cell value starts with a specific set of numbers, replace data

Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...

VBA - .find printing wrong value