How to Programming with Excel

excel,excel-2010,conditional-formatting

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

Conditional Formatting for every row

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 Search VBA macro

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

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

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

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

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

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

excel,vba,excel-vba,ms-project

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

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

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

excel,excel-formula,countif

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

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

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

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

Excel build bar graphs with oddly formatted data

excel,visual-studio-2010,excel-dna

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

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

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

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

VBA - Unable to pass value from Private to Public Sub

r,excel,statistics,dataset,google-adwords

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

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

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

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

Excel VBA Loop Delete row does not start with something

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

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

adding variables into another variable vba

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

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

excel,vba,excel-vba,excel-2010

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

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

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

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.

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

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

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

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 reshape data frame in R or Excel?

r,excel

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

Count amount of differences and return column name with count

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

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

Match Function in Specific Column Excel VBA

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

Ordering values from random list in Excel

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

Finding position of a particular string containing cell in Excel

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

How to write formula for cell in Excel?

string,excel,if-statement,comparison

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

Comparing cell contents against string in Excel

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

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.

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

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.

Retrieve Number from a website into Excel

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

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

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!

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

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

Copying sheet to last row of a sheet from another workbook

excel,excel-formula,excel-2013

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

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

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

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

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

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

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

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

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

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

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

Excel: match two columns with two other columns

excel,powerpoint,spreadsheet

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

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

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

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

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

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

excel search engine using vba and filters?

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

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

excel,vba,excel-vba,excel-2007

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

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

Changing the active cell

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

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

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

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

VBA - do not grab header in range

vb.net,excel,visual-studio-2010

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

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

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

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

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

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

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

display inside a column the number from a string

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

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

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

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

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

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

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

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

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

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

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

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

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

Referencing a new inserted column Excel VBA

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

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

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

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

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

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

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

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 to link table row content to source in Power View

excel,powerpivot,powerview,powerbi,powerquery

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

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

VBA - .find printing wrong value

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 2013 Add a Connector Between Arbitrary Points on Two Different Groups