w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Display a non contiguous range of cells to a list box
perhaps Private Sub CommandButton1_Click() Dim rng As Range Set rng = Range("A1:E10") With ListBox1 .ColumnCount = 3 .ColumnWidths = "50;50;50" ' load 1st, 3rd and 5th columns of range into listbox .List = Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3, 5)) End With End Sub

Categories : Vba

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

Excel VBA, How to use the selection in a dropdown list on one sheet format(color) a range of cells on another sheet
Couple changes you need to make. You'll need to leverage the Worksheet_Change event on your sheet1 to update your sheet3. In your code, you'll need to refer to sheet3 directly because you won't be able to select it from the sheet1 worksheet event. Using the Target Value, evaluate whether or not it meets your specified condition and update sheet3 as desired Open the VB editor and open the Sheet1 code. Then paste the following code. Private Sub Worksheet_Change(ByVal Target As Range) ' Target.Value will be contain the value of the changed cell If Target.Value = "Yes" Then With ThisWorkbook.Sheets("Sheet3").Range("J3:J5").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0

Categories : Excel

vba range (cells(), cells()) copy past to other worksheet with out activating the other worksheet
You didn't really give us a lot to go on here, so I'll post one way and see if it gets you going: Sub test() Dim columnReferenceSource As Long Dim rowReferenceSource As Long Dim columnReferenceDest As Long Dim rowReferenceDest As Long Dim r As Range rowReferenceSource = 696 columnReferenceSource = 60 rowReferenceDest = 23 columnReferenceDest = 60 Set r = Range(Sheets("A").Cells(rowReferenceSource, columnReferenceSource), Sheets("A").Cells(rowReferenceSource, columnReferenceSource + 18)) r.Copy Destination:=Sheets("B").Cells(rowReferenceDest, columnReferenceDest) End Sub Obviously you'll have to set the row and column numbers for your situation. If you can provide more details as to what you're trying to do, I'm sure we can give a m

Categories : Excel

How do I get a range of cells in PHPExcel?
There is, the rangeToArray() method: $objPHPExcel->setActiveSheetIndex(0)->rangeToArray('A1:C3'); Wondering why I bother documenting these methods in the first place, but here's the argument list as well: /** * Create array from a range of cells * * @param string $pRange Range of cells (i.e. "A1:B10"), * or just one cell (i.e. "A1") * @param mixed $nullValue Value returned in the array entry * if a cell doesn't exist * @param boolean $calculateFormulas Should formulas be calculated? * @param boolean $formatData Should formatting be applied to cell values? * @param boolean $returnCellRef False - Return a simple array

Categories : PHP

Add cells to range of columns only
Sub Add_Project() Dim strNewProject As String Dim iRow As Long strNewProject = InputBox("Enter Project Name") If Len(strNewProject) = 0 Then Exit Sub 'Pressed cancel If WorksheetFunction.CountIf(Columns("B"), strNewProject) > 0 Then MsgBox "Project already exists" Exit Sub End If iRow = WorksheetFunction.Match(strNewProject, Columns("B")) + 1 Intersect(Range("A:K"), Rows(iRow)).Insert xlShiftDown Cells(iRow, "B").Value = strNewProject End Sub

Categories : Excel

Clear cells out of the used range
You can delete everything outside of your actual used range. You can either do this manually with selecting a row and ctrl+shift+down (and ctrl+shift+right with a column selected) or you can do it programatically by identifying where your data actually ends (either via a COUNTA or xlRight style of command) and then delete everything after those points. The manual way is good for if it's just a one one off cleanup operation, and if you're doing it in the macro then you may as well just use the method for determining the range to keep to go straight to selecting it, and not bothering to delete outside of the range.

Categories : Excel

Why does Range work, but not Cells?
The problem is that Cells is unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you call Range or Cells or Rows or UsedRange or anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to: In a sheet's class module: that sheet regardless of what's active In any other module: the ActiveSheet You qualify the Range reference, but the Cells reference is unqualified and is likely pointing to the Activesheet. It's like writing ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value which of course doesn't make any sense unless ThisWorkbook.Worksheets(1) happens to be active. I often like to use a With block so that I make sure ev

Categories : Vba

Function using IF and AND and OR on a range of cells
Beware, not properly tested by me!: =--AND(NOT(ISERROR(VLOOKUP("stringA",A:A,1,0))),OR(NOT(ISERROR(VLOOKUP("stringB",A:A,1,0))),NOT(ISERROR(VLOOKUP("stringC",A:A,1,0))))) Note I have covered the entire column cell1:cell10 (I assumed ColumnA) but range can be restricted to A1:A10 say, if desired.

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

Find a string on a cells range
Try the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) For i = 1 To Cells(1, 1).End(xlToRight).Column If InStr(Cells(1, i), "Test") Then Cells(2, i) = "OK" End If Next End Sub

Categories : Excel

Select range without filtered cells
There are functions like DAverage and Dsum that allow you to do calculations over a range and specify certain criteria.

Categories : Excel

Need If statement to check range of cells
This is pretty close to what you need. c probabaly should be a variant... With Worksheets("Raw Data").Range("EF4:EF500") Set c = .Find("Barker Library") If Not c Is Nothing Then Call LocationPivot("Barker Library") End If End With

Categories : Excel

VBA seems to disappear after I protect a range of cells
If the VBA changes cells that you have locked then you need to update the VBA to unlock the worksheet before running the rest of the code. Then complete the Sub by locking the worksheet. 'Add to beginning of the Sub Sheets("worksheet_name").Unprotect Password:="password" 'Add to end of the Sub Sheets("worksheet_name").Protect Password:="password"

Categories : Vba

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

Can you use Cells instead of Range inside of an Autofill function?
You're very close. Here's a version with your variables declared (which you really should do) and the Select statements eliminated (also a good practice): Sub ExampleThing() Dim Counter As Long Dim i As Long Counter = 13 For i = 1 To Counter Range("A" & i) = Rnd Next Range("B1").Formula = "=(PI()*A1)" Range("B1").AutoFill Destination:=Range(Cells(1, 2), Cells(Counter, 2)), Type:=xlFillDefault End Sub

Categories : Vba

Protect everything but the value of a cell or range of cells in a spreadsheet
You could theoretically write a script update the formatting back to your style during the onEdit() trigger. Something like - some pseudo code: function onEdit(event){ var ss = event.source.getActiveSheet(); var range = event.source.getActiveRange(); if(range.getBackgrounds()){ //or whatever you want to stay the same range.setBackgrounds(blue)// } SpreadsheetApp.flush(); return; } Edit: I don't know how you could protect your data validation though.

Categories : Google Apps Script

Use VBA to copy all visible and non-empty cells in a range
Full admission that I did not try your code, but you can also try the following With Sheets("Sheet1").Range("S2:S5000") Application.Intersect(.SpecialCells(xlCellTypeVisible), _ .SpecialCells(xlCellTypeConstants)).Copy End With Making the open ended assumption that the reason for copying is to paste somewhere else, you can update the above code by using With Sheets("Sheet1").Range("S2:S5000") Application.Intersect(.SpecialCells(xlCellTypeVisible), _ .SpecialCells(xlCellTypeConstants)).Copy _ Destination:= Sheets("destSheet").Range("destRange") End With

Categories : Excel

VLOOKUP and fill all cells in a date range
You are making conditions between different types/values (which, logically, do not hold): dispo.Cells(1, c).Value = txtdepart would work if both variables have the same type (and content), that is, string = string or date = date. Apparently you are expecting dates an thus you can use the following code to make completely sure: If Format(CDate(dispo.Cells(1, c).Value), "dd-MM-yyyy") = Format(CDate(txtdepart), "dd-MM-yyyy") Then Equivalent problem for If dispo.Range("A" & l).Value = vehicule Then Both members have to be identical. Above you are writing vehicule = 1 and thus this condition wlll only verify if dispo.Range("A" & l).Value equals 1. No idea what you want to write in the "x" part.

Categories : Excel

Macro to execute only if range of cells all contain the word "OK"
In two steps might be simplest: If a contiguous range, say E5:J5 where Row5 is the relevant one, add =COUNTIF(E5:J5,"OK") somewhere in your worksheet, say K5 (adjust formula to suit), then in your macro, test the value in K5 and if not =6 exit with your error message, else proceed to your 'push order' macro.

Categories : Excel

Determine range encompassed by merged cells
Try this Sub Demo() Dim r As Range Set r = Range("YourRange") With r.MergeArea MsgBox "Merged Range start row = " & .Row & vbNewLine & _ "Merge Range stop row = " & .Row + .Rows.Count - 1 End With End Sub

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

vba pass a group of cells as range to function
As written, your function accepts only two ranges as arguments. To allow for a variable number of ranges to be used in the function, you need to declare a ParamArray variant array in your argument list. Then, you can process each of the ranges in the array in turn. For example, Function myAdd(Arg1 As Range, ParamArray Args2() As Variant) As Double Dim elem As Variant Dim i As Long For Each elem In Arg1 myAdd = myAdd + elem.Value Next elem For i = LBound(Args2) To UBound(Args2) For Each elem In Args2(i) myAdd = myAdd + elem.Value Next elem Next i End Function This function could then be used in the worksheet to add multiple ranges. For your function, there is the question of which of the ranges (or cells) that can passed

Categories : Excel

How to Copy-PasteSpecial cells from filtered range
Four things: .SpecialCells(xlCellTypeVisible) returns a reference to a range, but you don't use it You can't use both Destination:= ... and .PasteSpecial with one Copy. Choose one. You mean .PasteSpecial Paste:=xlPasteValues not .PasteSpecial Paste = xlPasteValues You activate and filter sheet "Additional Existing Raw Mat.", then refer to a filter on Sheet12. Are you sure thats right? Update: How to use Copy PasteSpecial .Copy Sheet8.Range("H" & AddRow + 1).PasteSpecial Paste:=xlPasteValues

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

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

Change by val target as range to ignore blank and 0 value cells
If c.Value != "" should work for blank cells. At least it worked here. As for ignoring the value 0, couldn't you just change the if clause to if c.Value > 0?

Categories : Excel

Ignore blank cells in dynamic named range
I can't understant what you really want/need. If your list as blanks in between, so not sorted, you must use MATCH with 3rd argument 0. Then, =OFFSET(Tasks!$G$2,0,0,MATCH("*",Tasks!$G$2:$G$22,0),1) will return a range starting in cell $G$2 with a number of rows equal to the number of cells before first blank cell. Putting this in a single cell, will simply show cell $G$2 value. What kind of use do you want for the range?

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

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

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

return the next cell with text in a range that includes blank cells
Try this regular formula: =IF(ROWS(A$1:A1)>ROWS(NoBlanksRange),"",INDEX(BlanksRange,SMALL(INDEX((BlanksRange<>"")*ROW(BlanksRange)-ROW(INDEX(BlanksRange,1))+1,),COUNTBLANK(BlanksRange)+ROWS(A$1:A1))))

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

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

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

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

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



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