w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
VBA macro to split one worksheet into several workbooks based on number of rows in the worksheet
Although there are MANY ways to do this (some even extremely easy with VBA) What I think would be the easiest that requires no VBA would be to do the following: Add in an extra "helper" column In that column, put in the following formula =FLOOR(ROW()/100,1) Do a filter on the data Now, filter for each number 0,1,2,3,...,7 in your helper column. Each set will have your next hundred records - Copy and paste them into a new workbook and save it as needed. Also, from this point it's TRULY a trivial case to record a quick macro to do that last part. Even though it isn't exactly what you asked for, I hope the simplicity of it helps.

Categories : Excel

Get the Number of Rows in Column of a worksheet
Try something like this: $xl = New-Object -COM "Excel.Application" $xl.Visible = $true $wb = $xl.Workbooks.Open("C:path oyour.xlsx") $ws = $wb.Sheets.Item(1) $rows = $ws.UsedRange.Rows.Count foreach ( $col in "A", "B", "C" ) { $xl.WorksheetFunction.CountIf($ws.Range($col + "1:" + $col + $rows), "<>") - 1 } $wb.Close() $xl.Quit()

Categories : Powershell

Move rows to existing/new worksheet based on cell value
All you really have to do is set your : sh33tName so it matches your master worksheet custNameColumn so it matches your column name with the customers names stRow row at which the customer names start Option Explicit Sub Fr33M4cro() Dim sh33tName As String Dim custNameColumn As String Dim i As Long Dim stRow As Long Dim customer As String Dim ws As Worksheet Dim sheetExist As Boolean Dim sh As Worksheet sh33tName = "Sheet1" custNameColumn = "I" stRow = 2 Set sh = Sheets(sh33tName) For i = stRow To sh.Range(custNameColumn & Rows.Count).End(xlUp).Row customer = sh.Range(custNameColumn & i).Value For Each ws In ThisWorkbook.Sheets If StrComp(ws.Name, customer, vbTextCompare) = 0 Then

Categories : Excel

CountIf of values from cells in rows between two designated dates into different worksheet with VBA
I am not sure why you use ColumnW with 15 columns (don't really have any idea of what is where!) but am curious whether this is any help (for running in a new sheet): Sub Macro1() Range("D3").Select ActiveCell.FormulaR1C1 = _ "=COUNTIFS(Sheet1!R2C1:R5000C1,"">=""&R1C1,Sheet1!R2C1:R5000C1,""<=""&R2C1,Sheet1!R2C:R5000C,RC1)" ActiveCell.Copy Application.CutCopyMode = False Selection.Copy Range("E3:W3").Select ActiveSheet.Paste Range("D3:W3").Select Application.CutCopyMode = False Selection.Copy Range("D4:D8").Select ActiveSheet.Paste End Sub This assumes: in that sheet (not Sheet1) A1 contains your start date, A2 your end date and A3:A8 your six possible responses. Based on Record Macro, I trust you can adjust as requi

Categories : Excel

Excel VBA: Looping through rows and copy cell values to another worksheet
Private Sub CommandButton1_Click() Dim Z As Long Dim Cellidx As Range Dim NextRow As Long Dim Rng As Range Dim SrcWks As Worksheet Dim DataWks As Worksheet Z = 1 Set SrcWks = Worksheets("Sheet1") Set DataWks = Worksheets("Sheet2") Set Rng = EntryWks.Range("B6:ad6") NextRow = DataWks.UsedRange.Rows.Count NextRow = IIf(NextRow = 1, 1, NextRow + 1) For Each RA In Rng.Areas For Each Cellidx In RA Z = Z + 1 DataWks.Cells(NextRow, Z) = Cellidx Next Cellidx Next RA End Sub Alternatively Worksheets("Sheet2").Range("P2").Value = Worksheets("Sheet1").Range("L10") This is a CopynPaste - Method Sub CopyDataToPlan() Dim LDate As String Dim LColumn As Integer Dim LFound As Boolean On Error GoTo Err_Execute 'Retrieve date value to search for LDate =

Categories : Excel

Macro to iterate through all rows in all columns of a worksheet and compare a cell to copy the row to a text file
You can use the .Offset() method of Range object and vbTab constant to create tab delimited spaces The .Offset(rows, columns) takes 2 parameters. Number of rows up or down of the current cell. To navigate up you give it a negative value to go down a positive one. Same applies to columns except that you use a negative to move left and a positive to go right. vbTab creates a tab between two objects in the context of "this is " & vbtab & " tab delimited" I have modified your comparison method a bit and added the StrComp() function. It is more reliable and you can specify the comparison parameters. Please see how to compare strings in VBA for more details about the = operator in string comparison. I have changed your Sheet4.Range("K1:K300") as this is a bit misleading and wrong

Categories : Excel

block rows for select query till update query is done its job
In Oracle, readers never block writers and writers never block readers. So another session will always be able to read the current committed state of a row regardless of what the update process is doing. If you want to prevent the rows from being read, you would need to code some additional locking logic. The reader could also do a SELECT ... FOR UPDATE with or without a WAIT so that it would block until the writer's transaction commits. You could also build locking logic using a different table. You could build some logic where the writer updates a row in a different table indicating that the update process is underway and all the reader processes could read that row.

Categories : Hibernate

What is the VBA code for putting cell data from one worksheet to another worksheet that includes text?
[EDIT]: After getting some more information about the request, give this code a try: Sub Add_Sub_Page() Dim wsTemplate As Worksheet Dim strSheetName As String Dim lPayRequestNumber As Long Set wsTemplate = Sheets("Template") lPayRequestNumber = Sheets("Billing Summary").Range("Q10").Value strSheetName = Sheets("Invoice Summary").Range("T10").Value If Evaluate("ISREF('" & strSheetName & "'!A1)") Then MsgBox "Sheet '" & strSheetName & "' already exists.", , "Exiting Macro" Exit Sub End If wsTemplate.Copy Before:=Sheets(8) With ActiveSheet .Name = strSheetName .Range("A1").Value = "Pay Summary # " & lPayRequestNumber End With End Sub

Categories : Excel

Excel Macro Pt. 1 - Search Worksheet Column for USER generated INPUT and Move Row to new Worksheet
Using .Select is your culprit. If you don't select another sheet, you will not have to save your position. See this for an excellent reason why Select is not a good idea. this entire code block: 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Sheet2 in next row Sheets("Test").Select Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select ActiveSheet.Paste can be put into a single line that doesn't lose your place 'Copy from row LSearchrow to LCopyToRow in Test sheet Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy _ Sheeets("Test").Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)) (broken into 2 lines for readability) and this won't change workseets, so your place is no

Categories : Excel

How to preserve a formula's reference to a worksheet when the worksheet is deleted and replaced using C#?
What you will have to do is, in the sheet that contains the formulas, firstly convert them away from formulas (this will stop Excel from creating errors in your formulas when you delete the sheet that thse formulas are referencing). So, for example, do a Find/Replace and replace "=" with, for example "#" (or any other character or set of characters you're sure you're not using in that sheet). Thsi will stop the formula from creating an errored reference. Then delete the sheet, add in the new sheet Then re-replace the values in your formula sheet back from "#" to "=". So long as your new sheet has the same name as the old sheet, you should be good to go. Hope this helps.

Categories : C#

How to COMPARE to two columns in different worksheet and add a new column to worksheet 2 in excel
Add the following to a new column in the second sheet and copy it down. Adjust the ranges accordingly. This will extract the Project ID from the first sheet (in column B) based on the the Project # in the second sheet (in column A). =VLOOKUP($A6,Sheet1!$A$6:$B$112,2,0)

Categories : Excel

Copy contents of current worksheet to vba created worksheet
Would you consider copying the worksheet as a whole? Here's a basic example, you'll have to customize your workbook organization and naming requirements. Sub CopyWkst() Dim wksCopyMe As Worksheet Set wksCopyMe = ThisWorkbook.Worksheets("Sheet1") wksCopyMe.Copy After:=ThisWorkbook.Sheets(Worksheets.Count) ThisWorkbook.ActiveSheet.Name = "I'm New!" End Sub

Categories : Excel

Sorting an SQL table with rows that relate to each other
for 1 st question - going query select * from time_table where option = 0 order by DepartureDateTime, Option2, Option3; 1st question - returning query select * from time_table where option = 1 order by ArrivalDateTime, Option2, Option3; This result base on understand what you asked. its not really clear. Please add some details for 2nd question. cant understand the issue. give example with data.

Categories : Mysql

"Group" some rows together before sorting (Oracle)
One way to get this is by using analytic functions; I don't have an example of that handy. This is another way to get the specified result, without using an analytic function (this is ordering first by the most_recent_date for each ID, then by ID, then by Date): SELECT t.ID , t.Date FROM mytable t JOIN ( SELECT s.ID , MAX(s.Date) AS most_recent_date FROM mytable s WHERE s.Date IS NOT NULL GROUP BY s.ID ) r ON r.ID = t.ID ORDER BY r.most_recent_date , t.ID , t.Date The "trick" here is to return "most_recent_date" for each ID, and then join that to each row. The result can be ordered by that first, then by whatever else. (I also think there's a way to get this same ordering using Analytic functions, but I do

Categories : SQL

Duplicated rows when sorting dgrid 0.3.6
Check the default implementation of Cache.js, especially the query and queryEngine functions. By default they always get first to the master store, which in your case is the JsonRest store. Only after the data has been loaded, the caching store is updated (in your case the Memory store). Now, if you check function _setSort in DGrid List.js file, and function refresh in DGrid OnDemandList.js you'll sill see that by default DGrid calls the query method of the current store to obtain the new list of items sorted differently. In your case that store is the dojo/store/Cache. So, summing up, when the user clicks the column to sort, DGrid queries the Cache, which in turn queries JsonRest, which in turns queries the server, which then returns new data, which then the Cache stores in the Memory s

Categories : Javascript

MySQL sorting rows by record groups
You need to join in the field value for size first: select t.* from t join (select t.object_id, max(case when field = 'order' then value end) as ordering from t group by t.object_id ) tsum on t.object_id = tsum.object_id order by tsum.ordering, t.object_id, id; This assigns the same "ordering" to everything with the same object_id.

Categories : Mysql

Sorting rows in csv file using Python Pandas
Typing help(df.sort) gives: sort(self, columns=None, column=None, axis=0, ascending=True, inplace=False) method of pandas.core.frame.DataFrame instance Sort DataFrame either by labels (along either axis) or by the values in column(s) Parameters ---------- columns : object Column name(s) in frame. Accepts a column name or a list or tuple for a nested sort. [...] Examples -------- >>> result = df.sort(['A', 'B'], ascending=[1, 0]) [...] and so you pass the columns you want to sort as a list: >>> df quarter week Value 0 5 1 200 1 3 2 100 2 2 1 50 3 2 2 125 4 4 2 175 5 2 3 195 6 3 1 10 7 5 2 190 >>> df.sort

Categories : Python

sorting in mysql if multiple rows matches
If three rows have the same timestamp value, then they will appear as three rows after the order by. In arbitrary order. If you want a stable sort (that is, one that always returns the same values), then include an id in the order by clause. Your group by should ensure that you get different ids in the two rows coming from the query. EDIT: If you need all records matching 1 or two timestamps, do something like this: select table1.email from table1 join (SELECT time_stamp FROM table2 INNER JOIN table1 ON table2.wid = table1.id WHERE table1.time_stamp >= DATE_SUB(NOW(),INTERVAL 10 MINUTE) AND table1.status<>200 ORDER BY table1.time_stamp ASC LIMIT 2 ) limits on table1.time_stamp = limits.time_stamp GROUP BY table1.Id

Categories : Mysql

ASPxGridView rows disappear on paging and re-sorting
The ASPxGridView was not being bound to data on every server request. The page was not handling postback and adding a handler in the Page_Load method like so: protected void Page_Load(object sender, EventArgs e) { if (Page.IsPostBack) { targetASPxGridView.DataBind(); } // Rest of code } allows the GridView to stay visible and maintain data when being resorted or having its columns adjusted.

Categories : C#

Active Worksheet vs Calling Worksheet
Use Application.Caller.Parent.Range(...) like in example below Function AcquisDate() Application.Volatile True AcquisDate = Application.Caller.Parent.Range("AcquisDateCell") End Function

Categories : Excel

VBA sorting Excel rows in ascending order but excluding first row?
Looks like the sorting glitches up a little when there isn't anything to sort, since obviously when there's only one cell, it doesn't have anything to sort and might as well just be skipped. So I'd just recommend checking that there's more than one row filled in first, and skip the sorting if there isn't. ' Sort by REPTNO field Dim xlSort As XlSortOrder xlSort = xlAscending With Worksheets("Export") If .Range("B3") <> "" .range("B2:B" & rowCnt).Sort Key1:=.range("B2"), Order1:=xlSort, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End if End With

Categories : Excel

jQuery sortable, how to limit sorting rows only by clicking on specified element
You can set the handle option: $("#fieldsTable").sortable({ items: 'tr', handle: '.dragImage' }); Assuming that you set a CSS class of dragImage on the image within the row. More info in the API docs.

Categories : Jquery

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

I want to delete a worksheet when I delete a row in another worksheet (EXCEL 2007)
use selection change to grab the sheetname for the row they are on, and the change event to delete the sheet. Something like this: Option Explicit Dim SheetName As String Private Sub Worksheet_Change(ByVal Target As Range) If SheetName = "" Then Exit Sub If Target.Rows.Count > 1 Then Exit Sub If Cells(Target.Row, 1) = "" Then Exit Sub 'Inserted a row? If Cells(Target.Row, 1) <> SheetName Then Worksheets(SheetName).Delete End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Rows.Count = 1 Then SheetName = Cells(Target.Row, 1) Else SheetName = "" End If End Sub I'm assuming your sheetname is in the 1st column - change the 1 in the Cells( reference if not

Categories : Excel

Selecting Rows Containing Each Unique Field Value & Sorting By Another Field With A Limit
$q1 = "SELECT map from tablename GROUP BY map ORDER BY map"; $r1 = $db->mysqli_query($q1); while ($mapRow = mysqli_fetch_assoc($r1)) { $map = $mapRow['map']; $q2 = "SELECT * FROM tablename WHERE map='". $map ."' ORDER BY time LIMIT 15"; $r2 = $db->mysqli_query($q2); while ($dataRow = mysqli_fetch_assoc($r2)) { $map[] = $dataRow; } }

Categories : PHP

Defining Multiple Comparators for Sorting and Reverse Sorting Appcelerator Backbone Models
To sort in descending order for strings(case insensitive) or just numbers, try this: var sort_descending = false; /* assuming you are able to set this variable when you need to sort */ var sort_attribute_name = "title"; /* assuming you can set the attribute name on which you want to sort */ book_list.comparator = function(book){ /* book_list is an instance of book_collection */ if(sort_descending){ if(isNaN(book.get(sort_attribute_name)){ var str = book.get(sort_attribute_name); str = str.toLowerCase(); str = str.split(""); str = _.map(str, function(letter){ return String.fromCharCode(-(letter.charCodeAt(0))); /* from an SO answer*/ }); } return -book.get(sort_attribute_name); } return book.get(sort_attribute_name); };

Categories : Sorting

CUDA parallel sorting algorithm vs single thread sorting algorithms
Sorting is an operation that has received a lot of attention. Writing your own sort isn't advisable if you are interested in high performance. I would consider something like thrust, back40computing, moderngpu, or CUB for sorting on the GPU. Most of the above will be handling an array at a time, using the full GPU to sort an array. There are techniques within thrust to do a vectorized sort which can handle multiple arrays "at once", and CUB may also be an option for doing a "per-thread" sort (let's say, "per thread block"). Generally I would say the same thing about CPU sorting code. Don't write your own. EDIT: I guess one more comment. I would lean heavily towards the first approach you mention (i.e. not doing a sort per thread.) There are two related reasons for this: Most

Categories : Sorting

How to pad till end-of-day in pandas?
Creating a set of filled values with a frequency of minutes per day In [120]: idx = [ pd.date_range('20130101',periods=10,freq='T')+timedelta(i) for i in range(5) ] In [121]: idx = idx[0] + idx[1] + idx[2] + idx[3] + idx[4] In [122]: s = Series(randn(len(idx)),index=idx) In [123]: s Out[123]: 2013-01-01 00:00:00 1.285575 2013-01-01 00:01:00 1.056882 2013-01-01 00:02:00 -0.690855 2013-01-01 00:03:00 1.235476 2013-01-01 00:04:00 -0.729948 2013-01-01 00:05:00 0.114036 2013-01-01 00:06:00 0.994977 2013-01-01 00:07:00 -0.455242 2013-01-01 00:08:00 0.645815 2013-01-01 00:09:00 -0.738772 2013-01-02 00:00:00 0.464686 2013-01-02 00:01:00 -0.872786 2013-01-02 00:02:00 0.112433 2013-01-02 00:03:00 -0.398235 2013-01-02 00:04:00 -0.442196 2013-01-02 00:05:00

Categories : Python

Waiting till process end
Ok for properties like mmsys.cpl solution is: Process sound = new Process(); sound.StartInfo.FileName = "rundll32.exe"; sound.StartInfo.Arguments = "shell32.dll,Control_RunDLL mmsys.cpl"; sound.Start(); sound.WaitForExit(); because that windows is runs via rundll32 command not explorer.exe, so i can check that process. But still dont know how to open device manager. i found 2 commands: devmgmt.msc hdwwiz.cpl but both run and create second process which i cannot track. Any solution for that ?

Categories : C#

PHP array_intersect() till the first match
How about this? foreach ($arr1 as $key => $val) { if (in_array($val, $arr2)){ // do something, maybe return so you wouldn't need break break; } }

Categories : PHP

Summing up till a certain interval
No for loop needed, if indeed this interval is constant like in your example: Ans=sum(reshape(data,3,[])) note that I reshape the vector data to a matrix that has the right number of columns, so the value 3 relates to the interval size you wanted...

Categories : Matlab

DIV stick to top till pushed offscreen by next DIV
You might want to check out this jsfiddle. http://jsfiddle.net/kennis/JTvFZ/ I think it could get you in the right direction. // Index of the currently 'active' section var activeCache = null; // Actual rendered height of a header element var cloneHeight = function(){ var $clone = $('<div class="clone"></div>').appendTo('body'), cloneHeight = $clone.outerHeight(); $clone.remove(); return cloneHeight; }(); // Top offsets of each header var offsets = []; // Figure out which section is 'active' var activeHeaderIndex = function(){ var scrollTop = document.body.scrollTop; for ( var i = 0; i < offsets.length; i++ ) if ( offsets[i] - cloneHeight > scrollTop ) return Math.max( i - 1, 0 ); } // Build the 'offsets' array $('.he

Categories : Javascript

Get sublist till the end with Apache Commons
You don't need Apache Commons for this. It can be implemented like this: l.subList(first, Math.min(l.size(), first + pagesize)); To go even further, you can import Math.min statically and reduce some more clutter: import static java.lang.Math.min; ... l.subList(first, min(l.size(), first + pagesize));

Categories : Java

Inserting dot after 2 chars till the end of file in C++
For a simple application like this, open the output file before you start reading, and don't close it until you're done. As written, the output file gets opened every time a character is read and then overwrites whatever was in the file before. You could open the file in append mode to stick new data on the end, but it's much simpler (and faster) to just keep it open.

Categories : C++

Update data row wise till last row using VBA
Its done... I used Do until loop as follows : Sub Macro6() Dim rw As Integer, clm As Integer, clcnt1 As Integer, clcnt2 As Integer, clmcnt As Integer Dim str As String, str1 As String, str2 As String rw = 1 clm = 1 clmcnt = 0 Do clcnt1 = Range("B1").End(xlDown).Row Rows("1:1").Select Range("E1").Activate Selection.AutoFilter Range("B" & rw).Select Windows("dfg.xlsx").Activate clcnt2 = Range("B1").End(xlDown).Row clmcnt = cntcnt + 1 str = Cells(rw, clm + 1).Value Selection.Copy Windows("testing1.xlsm").Activate ActiveSheet.Range("$B$1:$E$" & clcnt1).AutoFilter Field:=1, Criteria1:=str, Operator:=xlAnd Windows("dfg.xlsx").Activate Range("D" &

Categories : Excel

Reading from a particular fill till the last line in C++
Blank lines, plural? You should check if it reached EOF before pushing to the list. infile.open( filename ); getline( infile, textMessage ); while( !infile.eof() ) { text_list.push_back( textMessage ); getline( infile, textMessage ); } edit: KasF's answer is better.

Categories : C++

JS object sorting date sorting
Both console.log show the same array because when you use console.log(sortable), sortable is passed by reference, and console output happens AFTER finishing your script - when sortable has been already sorted. Making your code simple: var arr = [3,2,1]; console.log(arr); // Produces `[1,2,3]` because its printed // to the console after `arr.sort();` arr.sort(); console.log(arr); // Produces `[1,2,3]`, as expected Demo: http://jsfiddle.net/Rfwph/ Workaround If you want to be able to do console.log with an array to see it before being modifyed, you can use .slice(0) to copy the array, i.e. to obtain another array which contains the same elements as your array. var arr = [3,2,1]; console.log(arr.slice(0)); // [3,2,1] arr.sort(); console.log(arr); // [1,2,3] Demo:

Categories : Javascript

C++ Bubble Sorting and Selection Sorting
#include <iostream> #include <string> #include <iomanip> using namespace std; void sortArray (int [ ], int ) ; void showArray (int [ ], int ) ; int count = 0; int main () { const int SIZE = 6; int values[SIZE] = {7, 2, 3, 8, 9, 1} ; cout << "The unsorted values are: "; showArray (values, SIZE) ; sortArray (values, SIZE) ; cout<< "The sorted values are: " ; showArray (values, SIZE) ; return 0; } void sortArray (int array [ ], int SIZE) { int temp; bool swap; do { swap = false; for (int count = 0 ; count < (size - 1 ) ; count ++) { if (array [count] > array [count + 1] ) { temp = array [count] ; array [count] = array [count + 1] ; array [count + 1] = temp ; swap = true ; count++; dispCount(); } } } while (swap) ; } void dispCount(){ cout <<

Categories : C++

Android button disable till the Toast is seen
Put the below code before the progress dialog starts Button myBtn=findViewById(R.id.button1); myBtn.setVisibility(View.INVISIBLE); //myBtn.setEnabled(false); After Toast.makeText() is called, put the below code: myBtn.setVisibility(View.VISIBLE); //myBtn.setEnabled(true); Note that setVisibility will make the button visible/invisible, setEnabled(false) will turn your button to non-clcikable mde.

Categories : Android

How to run procedure every week till end date in oracle
Consider what Bob Jarvis commented, but if you still need to solve your problem , one possible solution is to use dbms_lock.sleep( Number_of_seconds ); , so a basic pseudocode would be : run your DML statements calculate number for seconds in a week use dbms_lock.sleep( Number_of_seconds ); to suspend the procedure repeat until end date is equal or less than SYSDATE. see more information about that procedure in here

Categories : SQL



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