w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Referring to a range of cells inside a formula in VBA Excel
If it is possible, turn your data into a table. This way, you can refer to dynamic data (changing in size) without running into problems. For instance, to refer to column A, called 'Item' in a table called 'Table1', your reference would be '=Table1[Item]' Regardless of the rows.

Categories : Excel

Excel formula -> how to change SUMPRODUCT formula to skip null cells
In the sumproduct, to exclude empty cells, suppose you're using it on range A1:A100, you could do the following: = Sumproduct((A1:A100),--(A1:A100<>"")) That second criteria will ensure that you're only looking at cells that have a value in them... As an explanation (A1:A100<>"") will return an array of True False, where, if there is a value in the cell, it returns true, otherwise, false. Then, including the -- before it, it converts True/False to 1/0. So, in effect, you're multiplying empty cells by a zero (excluding from the formula) and non-empty cells by 1 (including them in the formula). The --(logical statement for my array) is a very useful trick to use with SumProduct() in MANY different ways!!

Categories : Excel

Count unique values in a column in Excel
try - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1)) where COLUMNRANGE = the range where you have these values. e.g. - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1)) Press Ctrl+Shift+Enter to make the formula an array (won't calculate correctly otherwise)

Categories : Excel

For an Excel Formula's variable taking a Range, input row number by the result of a formula
Try using the INDIRECT function:) say you have three cell that can dynamically calculate the address of your input range. something like what you suggested and it is in the cell A1 ="$BB$2:$BB$" & COUNTA(BB2:BB1000) ---> in cell A1 You can use the Indirect function like this: =SERIES(INDIRECT(A1),[RANGE],[RANGE]) Hope this helps!

Categories : Excel

counting unique entry within a range that meet a specific criteria in another column in excel
Assuming a data setup like this: The formula in cell E2 and copied down is: =SUMPRODUCT(($A$2:$A$15=D2)*($A$2:$A$15<>"")/COUNTIF($B$2:$B$15,$B$2:$B$15&""))

Categories : Excel

Excel Compare two cells in one sheet to two in another, copy a certain range of cells if they are equal
edited July 23 - 10:30EST no VBA or macro needed you can use simple formulas and some copy and paste on sheet2, at the end of the data columns, create a new column (AD?) with this formula =IF(AND(IFERROR(VLOOKUP(A4,[book1]sheet1!$C$4:$C$14,1,FALSE),"")<>"",IFERROR(VLOOKUP(D4,[book1]sheet1!$L$4:$L$14,1,FALSE),"")<>""),[book1]sheet1!T4,X4) and copy it over the next 6 rows (AD to AI) I assumed the first row being 4 and the formula is searching 10 rows in sheet 1, adjust as needed then copy down to last of your data rows if you have a match, the sheet1 data will be displayed otherwise, sheet2 data will be there copy this chunk, go to X4 and use EDIT - PASTE SPECIAL - VALUES if excel 2003 or click the PASTE button on upper left and choose PASTE VALUES if Excel 2007+ I rec

Categories : Excel

Formula in excel not considering dynamically generated values in cells
In case your sheet has some formula for say A3=sum(A1:A2) is calculated and it is working fine with A1=5, A2 =4. Now if change the value of A2 with 2 like sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 and perform write operation, check the value of A3 it is still showing 9. It seems wrong but actully not. The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them. wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); or with wb.setForceFormulaRecalculation(true); and then perform Write operation. This will give you the correct result. For Detail check here

Categories : Excel

Excel formula to the value of a cell based on two cells' values
In case this helps, the following formulae seem to display the values at the corners, assuming Measure is a named range starting in A1 and value1 and value2 are named ranges: =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1))) =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1)+1)) =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)+1),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1))) =INDEX(Measure,IFERROR(MATCH(value1,A:A,0),MATCH(value1,A:A,1)+1),IFERROR(MATCH(value2,1:1,0),MATCH(value2,1:1,1)+1))

Categories : Excel

Excel: get cell collection of cells that contain certain text or formula
Paste this macro in your macro editor in your workbook (ALT+F11) Sub test() Dim w As Worksheet, r As Range For Each w In ThisWorkbook.Worksheets For Each r In w.UsedRange If r.Value = "Certain text" Or r.Formula = "Certain Formula" Then 'Do what you want to do here End If Next Next End Sub Hope this helps

Categories : C#

Comparing two range of cells A and D and copy the duplicate data
if the Problem is only to put the right numbers into H, use this code for that. Put it just be for "end Sub". The code supposes you are never having more than 1000 rows filled in A and D, but you can change that. Range("H1").Select ActiveCell.FormulaR1C1 = _ "=SUMIF(R1C1:R1000C1,RC[-1],R1C2:R1000C2)+SUMIF(R1C4:R1000C4,RC[-1],R1C5:R1000C5)" Selection.Copy Range("H2:H" & WorksheetFunction.CountA(Columns(7))).Select ActiveSheet.Paste Application.CutCopyMode = False Range("H1:H" & WorksheetFunction.CountA(Columns(7))).Select 'the following lines are only necessary if you don't want a formula in H. Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False hope that helps! Max

Categories : Excel

Looping the cells and getting the count of a string value in all the sheets and printing the count sequence in another column in respective sheet
Put the following code and add a reference to the "Microsoft Scripting Runtime" (Tools > Reference) so you can use a dictionary structure Sub CountOccurence() ' Reference: Microsoft Scripting Runtime Application.ScreenUpdating = False Set oDict = New Dictionary Dim Shts() As Variant Dim wS As Worksheet Dim r As Integer, rLast As Integer Dim i As Integer Shts = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") For i = 0 To 3 Step 1 Set wS = Sheets(Shts(i)) rLast = wS.Cells(1, 1).CurrentRegion.Rows.Count For r = 2 To rLast Step 1 If Not (oDict.Exists(wS.Cells(r, 1).Value)) Then oDict.Add wS.Cells(r, 1).Value, 1 Else oDict.Item(wS.Cells(r, 1).Value) = oDict.Item(wS.Cells(r, 1).Value) + 1 End If wS.Cells(r, 2).Value

Categories : Excel

Excel 2010 - VBA/Formula - Unable to find how range name is defined
On the Formulas Tab there is a Name Manager, have a look in there Additionally, you can run this simple Sub to list all the Named Ranges and the ranges they refer too Sub ListNamedRanges() Dim rngName As Name For Each rngName In ActiveWorkbook.Names Debug.Print rngName.Name, rngName.RefersTo Next End Sub Also, Select the drop down and go to Data tab and then Data Validation then

Categories : Excel

Function to sum of a range of cells meeting a condition while skipping hidden rows?
Based on mehow his answer I created this function, which evaluates an condition before calculating the sum. Function SumVisibleWithCondition(MyRange As Range, Condition As String) As Integer Dim sum As Long Dim c As Range For Each c In MyRange If Not c.EntireRow.Hidden Then If Evaluate("" & c.Value & Condition) Then sum = sum + c.Value End If End If Next c SumVisibleWithCondition = sum End Function

Categories : Excel

Get Range of Groups of Cells Excel C++/cli
Worksheet->Cells is supposed to be the entire spreadsheet. You're asking it for a Range^ representing all cells in the entire worksheet, and that's what you get. (Quoting MSDN: "Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).") A Range object simply represents a group of cells; it's not innately tied to sub-tables within a worksheet, like your "A1:C2" and "F1:G2" are. To get the sub-tables, use the CurrentRegion property. I believe the way to do that is to get cell A1 and call CurrentRegion to get A1:C2, and on F1 to get F1:G2. (Obviously, better design would be to have your two sub-tables in their own sheets within the workbook, if possible.)

Categories : Dotnet

Excel: finding range of cells with value
Something like? =SUMIF(A1:A12,"7/17/2013",B1:B12) Of course change the ranges to match your data, the first range is the criteria range, then the date, then the sum range

Categories : Excel

Excel formula to output name in first column based on a value in the 3rd column
Try this: =IF(OR(Sheet1!C2<>"";Sheet1!D2<>"";Sheet1!E2<>"";Sheet1!F2<>"";Sheet1!G2<>"";Sheet1!H2<>"");Sheet1!A2;"") You can use C1 = 1 .... if you prefer. And C1,D1,E1... have to be replaced with your columns. Also Sheet1 have to replaced with your sheet name

Categories : Excel

Split an Excel range into single cells
Not too clear about your case but I have this .. This will give example to extract multi range to single range .. Sub SplitRange() Dim rn As Range Dim c As Range Set rn = Range("A1:A8") For Each c In rn.Cells MsgBox StrRange(c.Row, c.Column) Next End Sub Function StrRange(ByVal nRow As Single, ByVal nCol As Single) As String Dim sC As String Dim nC, nRest, nDivRes As Integer sC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" nC = Len(sC) nRest = nCol Mod nC nDivRes = (nCol - nRest) / nC If nDivRes > 0 Then StrRange = Mid(sC, nDivRes, 1) StrRange = StrRange & Mid(sC, nRest, 1) & Format(nRow) End Function Hope this help and everyone that need something like this ..

Categories : Excel

Excel VBA Range Merge Cells and offset
Woah, I'm really surprised this works at all! Range is a keyword in VBA and Excel, so it is very surprising to me you are able to use that as a variable name without problems. You can troubleshoot problems like this a lot easier by adding a debug statement: 'Add month header Debug.Print "Range Address: " & range.Address & vbTab & "i:" & i Call AddCalendarMonthHeader(MonthName(i), range) Debug.Print "Range updated00: " & range.Address 'Add weekdays header Debug.Print "Range updated0: " & range.Address Set range = range.Offset(1, 0) `<---- this is the line where the Offset loses the entire row Debug.Print "Range updated1: " & range.Address This results in the following: Range Address: $B$2:$H

Categories : Excel

Excel-VBA Before Save Lock a range of cells
Your code will behave differently depending on the active sheet when saving. Additionnaly, I wouldn't recommend to overload the reserved name "Cell" with a local loop variable. This will lead to unexpected behavior. You should remove references to ActiveSheet. If your wish is to protect the entire workbook, I would suggest iteration over the worksheets: Sub ProtectAll() Dim wSheet As Worksheet Dim myCell As Range For Each wSheet In Worksheets wSheet.Unprotect Password:="****" For Each myCell In Range("H5:H24,J5:J24") myCell.Locked = (myCell <> "") Next myCell wSheet.Protect Contents:=True, Password:="****", UserInterfaceOnly:=True Next wSheet End Sub NB: you have to put the code in a code module.

Categories : Vba

Excel - i can only seem to "highlight" non-duplicate/non-unique values
Its a standard option of conditional formatting Duplicate is the default, But the box allows unique too That menu is accessed from home tab > conditional formatting > new rule be sure to set a format for the cell then

Categories : Excel

Count the occurences of each unique string in a range
Try the .find method. Go to your VBA help, look up the range.find method for some more info - it also provides some code that you should be able to modify easily. I'd suggest using a counter for each value that updates each time you have a find. For example: Dim Low_count As Long Low_count = 0 With Worksheets(1).Range("a1:a500") Set c = .Find("Low", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Low_count = Low_count + 1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With

Categories : Excel

Automatically copy content to range of cells in excel using VBA
You could optimize this by using arrays but this is the basic idea: Dim i As Long For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i + 1, 1).Value = "" And Cells(i, 1).Value <> "" And Cells(i + 1, 2).Value <> "" Then Cells(i + 1, 1).Value = Cells(i, 1).Value End If Next

Categories : Excel

Copying a String Array into a Range of cells (in MS Excel using VBA)
Like this Sub StringArrayToRange() Dim strArr(3) As String strArr(0) = "one" strArr(1) = "two" strArr(2) = "three" Range("A1:A" & UBound(strArr) + 1) = WorksheetFunction.Transpose(strArr) End Sub also, this for more examples and tutorial EDIT: this documentation explains why the WorksheetFunction.Transpose was used

Categories : Excel

Creating 'duplicate' cells in other Excel (2010) Sheets including formatting
if you select both sheets (press CTRL while selecting sheet tabs) everything you do on sheet1 is replicated on sheet2 so select both sheets select a cell or range of cells format and enter content as desired untie sheets (press CTRL while selecting sheet tabs) sheet2 is a duplicate of sheet 1 hope that helps

Categories : Excel

What is the excel formula to get an amount from one column if another column indicates a Y?
Are you looking for the IF statement? IF( condition, [value_if_true], [value_if_false] ) Example: =IF(B2="Y",C2,X)

Categories : Excel

Excel 2003/2007 named formula appears to store only single value not range/array
This might work for you. To return a list of unique values given your range x for example: =LOOKUP(SMALL(IF(MATCH(x,x,0)=ROW(INDIRECT("1:"&ROWS(x))),MATCH(x,x,0)), ROW(INDIRECT("1:"&SUM(IF(FREQUENCY(MATCH(x,x,0),MATCH(x,x,0)),1))))), ROW(INDIRECT("1:"&ROWS(x))),x) You could define this as a name called Unique and use it in other formulas eg =COUNTIF(x,Unique) returns an array of frequencies. Addendum Regarding INDIRECT/INDEX/ROW etc. here's my 2c (see also here). Function inputs and outputs can essentially be divided into three cases: single values, arrays or references. If a function input argument takes single values by default and an array is specified, then the result depends on the type of output of the function. If the output type of the function is: A) a single va

Categories : Excel

Getting a unique count of individual occurences in a range of integers from SQL
the below SQL will give you the number of times the year appears in the table and the year select count(Year), year from MonthlySales group by year if you want the amount a specific year appears, just add a where cause like select count(Year), year from MonthlySales where year = ? group by year

Categories : PHP

Excel - save range of cells in multiple sheets to pdf with no whitespace
Revised answer following clarifications in comments (further revised for formatting): Sub copyValueTable() Dim ws As Worksheet Dim heads Dim new_sheet As Worksheet Dim rownum Dim ns_rownum Dim filename Dim filepath Dim rowcnt ' add a new workbook for the summary... Set new_sheet = Sheets.add rownum = 1 rowcnt = Sheets(2).Range("A1").End(xlDown).row ' loop through rows in outer loop For rownum = 2 To rowcnt Debug.Print "..on student row " & rownum & " in outer loop..." ns_rownum = 1 ' initialise for loop through sheets ' loops through sheets (except new)... For Each ws In Worksheets If ws.Name <> new_sheet.Name Then Debug.Print "....on sheet " & ws.Name

Categories : Excel

Excel coloring cells from a named range using the ranges colors
I agree with @Stephan1010 that conditional formatting would be the best route. It should be a little more manageable too because you only have 4 colors, which means 4 conditions with an OR statement containing all of the applicable statuses. You wouldn't have to amend all 30 if a color/status were to change... That being said, while it will severely impact the performance of your workbook, it could be done in vba via the worksheet events. The first piece of code would capture any change to Column C in worksheet 1 and update the background color (other changes can be done as well of course) Private Sub Worksheet_Change(ByVal Target As Range) minCol = Target.Cells(1, 1).Column maxcol = minCol + (Target.Columns.Count - 1) If minCol > 3 Or maxcol < 3 Then Else For Each cell In Tar

Categories : Excel

How to automatically update cells range in Excel depends on value of given cell?
You want the Indirect function. Pass it a string containing a cell reference, and it'll work as if you referenced the cell directly. To have a range, put the call to Indirect before or after the : that defines the range: For example: A1:INDIRECT(...). Here's my test spreadsheet: | A | B -+----+----------------------------- 1| 11 | Sum this many cells 2| 22 | 2 3| 33 | Here's the sum: 4| 44 | =SUM(A1:INDIRECT(("A"&B2))) 5| 55 | In cell B4, I create a range by specifying the beginning of the range (A1) and use Indirect for the end of the range. I use a hard-coded "A", and concatenate that with the integer value from another cell, and pass the result of the concatenation ("A2") to Indirect.

Categories : Excel

vb excel keep 1 instance of duplicate items in a range
Put this in H1: =COUNTIF(G$1:G1;G1) Fill down to end Make an autofilter on column G Filter out values of 1 Select the remaining rows by row header Right click on row header > click Delete ...

Categories : Vb.Net

copying range of cells and pasting to the first empty row more effective code? Excel
First, you don't have to use .Select or .Activate on cells/worksheets that you'll manipulate, just reference them directly. That can really slow down your code. The .Copy method can take a Destination parameter, so you don't have to use .Paste somewhere else. Also, the .End property isn't very reliable. It's better to use UsedRange instead. You can do that pretty much in only 1 line. Worksheets("New Search").Range("A3:J3").Copy Destination:=Worksheets("Past Searches").UsedRange.Columns(1).Offset(1, 0)

Categories : Excel

Excel VBA Setting Cell Colour based on range of individual cells
Perhaps you could try a different approach. Assuming that those 4 digit error cell numbers somehow correspond to [Row, Column] cell referencing you could try this: Dim varSplit As Variant varSplit = Split(errorCells, ",") 'Read into an array each value to access Then loop over the varSplit object using For Each loop and: Split up each string into row and column parts Access your Range(s) that need to be coloured by using: Cells(Row,Column).Interior.Color = RGB(216, 80, 83)

Categories : Excel

How to sum cells in a column if a condition is met in another column in SQL
Select year(order_date) as order_year, month(order_date) as order_month, sum(sales_price) as tot_price, group_concat(cust_name) as customers_in_month from mytable group by order_year, order_month SQLFiddle demo

Categories : Mysql

Looping through Excel workbooks, copying a range of cells from each and pasting to a master workbook
foreach (string stdFile in fileNames); The semi-colon at the end of this line terminates the statement immediately, so stdFile is not available in the code further down. Remove the semi-colon. There is a straight-forward introduction to Excel Interop here at dotnetperls.

Categories : C#

How to insert a formula at the end of a column that change monthly in Excel VBA
Short of the code, it seems what you need to know is how to determine how many columns are relevant. =MATCH with searching for "Total" in your labels rows should find the column number containing 256 in your example and much the same searching for "Description" the column number containing the boy. Subtract the former from the latter and deduct one for the quantity of 'month' columns.

Categories : Excel

How to loop through each column in Excel and apply column width based on condition
Something like Sub Autofit() Dim col As Range For Each col In ActiveSheet.UsedRange.Columns 'Only columns that actually have values col.Autofit If col.ColumnWidth > 50 Then 'Set your values here col.ColumnWidth = 30 col.WrapText = True End If Next End Sub Note, this uses Excel widths, not Pixels

Categories : Excel

R: aggregate and count rows that match a condition, group by unique values and transform table
I think you mean how many times appears the product and not how many different products , for each ID. Here a solution using data.table and reshape but in 2 steps : First I compute the number of product in the long format , then I transform my data to the wide one. I think that using plyr and ddply it is better here. library(data.table) DT <- as.data.table(dat1) DT[,n := .N,by= ID] reshape(DT,direction='wide',idvar='ID',timevar='Product',drop='Store') ID Purchase_date.Product_A n.Product_A Purchase_date.Product_C n.Product_C 1: 47 01-01-2012 2 NA NA 2: 26 NA NA 01-17-2012 1

Categories : R

Excel Macro - Copy Range Of Cells in Sheet2 to Sheet 1 below Cell With Specific Text
Activesheet and Activecell are too vague. If you are working with a multisheet program it is good practice to specify which sheet you are working in or use a "With Sheets("Sheet1")" for a multiple of "Sheet1". This may not be a cut and paste but you get my meaning: Sub Sheetspractice() NumRows = Sheets("Sheet1").Range("L2:L" & Rows.Count).End(xlup).Row Sheets("Sheet1").Range("L2").Select For x = 1 To NumRows Sheets("Sheet2").Range("A1:G24").Select Selection.Copy Sheets("Sheet1").Cells.Find(What:="Recess Size", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Sheets("Sheet1").Paste Sheets("Sheet1").Range("A1"

Categories : Excel

Find cells within a range if one cell equals a criteria copy its value to another column
Your lucky day! I had a free spare minutes and wrote this code for you. It will ask you for the Name - you simply select the cell with the name you wan't to generate data for It will create a table in columns G:J and stick in the results of matches in columns A:D Sub Findining() Dim r As Range, i As Long, j As Long, rng As Range Range("G:J").ClearContents For i = 1 To 4 Cells(1, i + 6) = Cells(1, i) Next i Set r = Application.InputBox("Select Name", Type:=8) If r.Columns.Count > 1 Or r.Rows.Count > 1 Then Do Until (r.Columns.Count = 1 And r.Rows.Count = 1) MsgBox "You can only select 1 name" Set r = Application.InputBox("Select Name", Type:=8) Loop End If For i = 2 To Range("A" & Rows.Count).End(xlUp).Row

Categories : Excel



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.