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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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,microsoft,powerpivot,powerbi

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

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

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

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

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

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

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

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

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

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

excel,excel-vba,properties,listbox

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.

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

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

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

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

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

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

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

javascript,excel,vba,excel-vba

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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!

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

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

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

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

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

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

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.

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

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

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

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

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

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

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.

excel,powerpivot,powerview,powerbi,powerquery

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

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

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

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

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