![]() ![]() Perhaps the most valuable feature of Excel is its ability to produce mathematical outputs using the data in a workbook. zip file containing this book to use offline, simply click here. You can browse or download additional books there. More information is available on this project's attribution page.įor more information on the source of this book, or why it is available for free, please see the project's home page. Additionally, per the publisher's request, their name has been removed in some passages. However, the publisher has asked for the customary Creative Commons attribution to the original publisher, authors, title, and book URI to be removed. Normally, the author and publisher would be credited here. This content was accessible as of December 29, 2012, and it was downloaded then by Andy Schmitz in an effort to preserve the availability of this book. See the license for more details, but that basically means you can share this book as long as you credit the author (but see below), don't make money from it, and do make it available to everyone else under the same terms. If (worksht.This book is licensed under a Creative Commons by-nc-sa 3.0 license. Private Function GetNextAvailableRow(ByVal worksht As Worksheet) As Long WorkSplitSheetRow = workSplitSheetRow - 1 If CStr(workSplitContent(workSplitSheetRow).Value) = "Allocate" Then WorkSplitContent(workSplitSheetRow).EntireRow.Delete RangeToCopy.Copy Destination:=targetWorksheet.Range("A" & CStr(targetWorksheetNextAvailableRow)) TargetWorksheetNextAvailableRow = GetNextAvailableRow(targetWorksheet) Set rangeToCopy = workSplitWorksheet.Range("A" & CStr(workSplitSheetRow) & ":H" & CStr(workSplitSheetRow)) 'Copy a specific Range rather than the whole row If CStr(workSplitContent(workSplitSheetRow).Value) = "allocate" Then Set workSplitContent = workSplitWorksheet.Range("D1:D" & rowsInWorkSplitWorksheet)ĭim targetWorksheetNextAvailableRow As Longįor workSplitSheetRow = 1 To workSplitContent.Count Set workSplitWorksheet = Worksheets("Work Split") Set targetWorksheet = Worksheets("Allocation") Change the name here to use a different target 'In the example, the target worksheet is "Allocation". Also, use spacing and indentation to make code easier to read as well. Single letter identifiers make reading and understanding code VERY difficult. The re-worked example below also addresses the comment about using a different (new) worksheet destination.Ĭomment: It is highly recommended to use meaningful names for variables. ![]() Identify the specific Range within the Copy command. ' Using 'Union' will get rid of the following complications. ![]() XRg(K).EntireRow.Copy Worksheets("Allocation").Range("A" & J + 1) 'If StrComp(CStr(xRg(K).Value), "allocate", vbTextCompare) = 0 Then ' You have to do better if you want to allow 'allocate' and 'AlOcAtE': 'For I = 1 To xRg.Count ' in this case even 'For I = 1 to I' works ('D1'). ' Introducing 'K' is confusing because you can just reuse the 'I': ' A rule of thumb is to never use it without the closing 'On Error GoTo 0'. ' Google it, and read what Chip Pearson writes about it. ' Never ever use this before you fully understand what it does. Set xRg = Worksheets("Work Split").Range("D1:D" & i) Worksheets("Allocation").UsedRange) = 0 Then J = 0 'Dim dws As Worksheet: Set dws = wb.Worksheets("Allocation") 'Dim sws As Worksheet: Set sws = wb.Worksheets("Split") ' You don't want to repeat 'Worksheets("Allocation")', so use a variable: 'Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code srg (Source Range), dCell (Destination Cell Range) ' Use 'Option Explicit', which will force you to declare all variables. If Application.CountA(dCell.Resize(, )) = 0 Then ' If you really want to write to the first row if it's empty: Set dCell = dws.Cells(dlRow, dCol).Offset(1) ![]() If StrComp(cString, sCriteria, vbTextCompare) = 0 Thenĭim dws As Worksheet: Set dws = wb.Worksheets(dName)ĭim dlRow As Long: dlRow = 'Cut' (Copy & Delete) Criteria Rows Option ExplicitĬonst sfRow As Long = 1 ' This should be 2 if you have headers!?ĭim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this codeĭim sws As Worksheet: Set sws = wb.Worksheets(sName)ĭim slRow As Long: slRow = ĭim scrg As Range: Set scrg = sws.Columns(sCop圜ols)ĬString = CStr(sws.Cells(r, sCritCol).Value) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |