|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.
|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:
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!!
|Count unique values in a column in Excel|
where COLUMNRANGE = the range where you have these values.
Press Ctrl+Shift+Enter to make the formula an array (won't calculate
|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
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:
Hope this helps!
|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:
|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
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
|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
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.
and then perform Write operation. This will give you the correct result.
For Detail check here
|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:
|Excel: get cell collection of cells that contain certain text or formula|
Paste this macro in your macro editor in your workbook (ALT+F11)
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
Hope this helps
|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.
ActiveCell.FormulaR1C1 = _
Range("H2:H" & WorksheetFunction.CountA(Columns(7))).Select
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.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
hope that helps!
|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
' 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
oDict.Item(wS.Cells(r, 1).Value) = oDict.Item(wS.Cells(r,
1).Value) + 1
|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
you can run this simple Sub to list all the Named Ranges and the ranges
they refer too
Dim rngName As Name
For Each rngName In ActiveWorkbook.Names
Debug.Print rngName.Name, rngName.RefersTo
Select the drop down and go to Data tab and then Data Validation
|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
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
SumVisibleWithCondition = sum
|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
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
(Obviously, better design would be to have your two sub-tables in their own
sheets within the workbook, if possible.)
|Excel: finding range of cells with value|
Of course change the ranges to match your data, the first range is the
criteria range, then the date, then the sum range
|Excel formula to output name in first column based on a value in the 3rd column|
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
|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 ..
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)
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)
Hope this help and everyone that need something like this ..
|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
'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
|Excel-VBA Before Save Lock a range of cells|
Your code will behave differently depending on the active sheet when
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:
Dim wSheet As Worksheet
Dim myCell As Range
For Each wSheet In Worksheets
For Each myCell In Range("H5:H24,J5:J24")
myCell.Locked = (myCell <> "")
wSheet.Protect Contents:=True, Password:="****",
NB: you have to put the code in a code module.
|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
|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
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
Set c = .Find("Low", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Low_count = Low_count + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
|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
|Copying a String Array into a Range of cells (in MS Excel using VBA)|
Dim strArr(3) As String
strArr(0) = "one"
strArr(1) = "two"
strArr(2) = "three"
Range("A1:A" & UBound(strArr) + 1) =
also, this for more examples and tutorial
this documentation explains why the WorksheetFunction.Transpose was used
|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
|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] )
|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:
You could define this as a name called Unique and use it in other formulas
eg =COUNTIF(x,Unique) returns an array of frequencies.
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
|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
|Excel - save range of cells in multiple sheets to pdf with no whitespace|
Revised answer following clarifications in comments (further revised for
Dim ws As Worksheet
Dim new_sheet As Worksheet
' 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
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
|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
For Each cell In Tar
|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.
|vb excel keep 1 instance of duplicate items in a range|
Put this in H1:
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 ...
|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
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
You can do that pretty much in only 1 line.
Worksheets("New Search").Range("A3:J3").Copy Destination:=Worksheets("Past
|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)
|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
group by order_year, order_month
|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
There is a straight-forward introduction to Excel Interop here at
|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
|How to loop through each column in Excel and apply column width based on condition|
Dim col As Range
For Each col In ActiveSheet.UsedRange.Columns 'Only columns that
actually have values
If col.ColumnWidth > 50 Then 'Set your values here
col.ColumnWidth = 30
col.WrapText = True
Note, this uses Excel widths, not Pixels
|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.
DT <- as.data.table(dat1)
DT[,n := .N,by= ID]
ID Purchase_date.Product_A n.Product_A Purchase_date.Product_C
1: 47 01-01-2012 2 NA
2: 26 NA NA 01-17-2012
|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:
NumRows = Sheets("Sheet1").Range("L2:L" & Rows.Count).End(xlup).Row
For x = 1 To NumRows
Sheets("Sheet1").Cells.Find(What:="Recess Size", After:=ActiveCell,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
|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
Dim r As Range, i As Long, j As Long, rng As Range
For i = 1 To 4
Cells(1, i + 6) = Cells(1, 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)
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row