Move up to next blank row after copy/paste from previous sheet.
Snowman got me rolling with code that does almost exactly what Im trying to do. After I hit end of day button Im trying to get the copied data to move up to the next blank cell in column B within a range. If I have any data in B2:B9,B11:B21,B23:B29 marked with a t next to it in column A and hit the end of day button only data in cells marked with that t are moved to the next sheet in the same cells they came from. What Ive noticed is only data in B2:B9 go to the exact same cell. B11:B21 are offset by 1, and B23:B29 are offset by 2. This is fine as the data is still moving. What I want it to do is for those ranges move up to the next blank cell in column B. So if I have data only in B4, and B6 I want that to transfer to next sheet in B2,B3. The same for the other 2 ranges. I also dont want it to overwrite anything that may already be in a cell on the next sheet. If I have "Tree" in B2 of the next sheet I want the data to go to B3,B4. Same for the other two ranges. I have tried xlUp, xlDn, and played with the code that was given to me by Snowman to try and make it work. Im not getting anywhere with this. I thought maybe if I create another macro that after I transfer the data would move everything up into blank cells then Id be okay with that also. Im not having any luck with that either. I even recorded a macro for copy/paste but that wont work if there is data in a cell already that I need to keep, and not be overwritten. I have attached a copy of the workbook. Hopefully this time it will allow it to be published with this query.
How can I adjust this VBA code to effect the sheet not the workbook
I have this code for clearing checkboxes from an inserted object, but it clears the entire workbook. Does anyone know how to adjust it to clear the sheet it is placed in. Please note I chose this code because the checkboxes are in groups and it was still able to clear the boxes. Thanks Sub Oval1719_Click() Dim sheet As Worksheet For Each sheet In Sheets On Error Resume Next sheet.CheckBoxes.Value = False On Error GoTo 0 Next sheet End Sub
finding words in a list
I am trying to create a spreadsheet using formulas to find words in a list that meet certain criteria. There are nearly 375000 words in the list and there are 50 criteria. This spreadsheet is intended to find solutions to the New York Times Letter Boxed puzzle. The puzzle is built around a square with three different letters of the alphabet on each side. The challenge is to solve the puzzle by using all twelve letters in as few words as possible. Solving the puzzle in two words is the ultimate goal. No word can have two letters from any of the four sides of the square adjacent to each other. There are 36 combinations of letters that cannot appear in a word used in the solution. There are 14 letters that do not appear on any side of the square and they cannot appear in a word used in the solution. One other condition is that the second word must start with the last letter of the first word. With my limited knowledge of Excel, I could not find a way to create a formula that would take into account all these criteria, so I have 50 * 370,000 cells all filled with formulas. The spreadsheet takes forever to calculate, and it only solves for the first word. In my example, there are 2024 possible first words if my formulas are working right. Only a fraction of these words will work with a second word that meets the criteria. The second word, as previously stated, must start with the last letter of the first word and must contain all the letters around the square that have not been used in the first word. It is permissible to also use letters in the second word already used in the first word. the same 50 criteria that apply to the first word apply to the second word. I think a macro would be better suited to solve this problem, but I am not good with macros. There are some websites that will solve the puzzle when you provide the twelve letters around the square. I don't care about those websites. It's fun to create excel spreadsheets that solve problems. If I were a programmer, knew VBA, or could write macros, I would do that, too. Here are the letters in today's letter boxed puzzle. Top - OLP Left - BTH Bottom - MAC Right - EIR Here is the link Letter Boxed Spreadsheet - Working I would appreciate any help you might be willing to provide. Thanks, Michael
Macro Error for Pivot Table
I can create a Pivot Table for the data I download and it works fine. However, when I made a Macro to do the same thing, I get an error. The error is: Run-time error '5': The following is a screenshot of the highlighted error in the Macro. I am now using Excel 2013, however I made an initial Macro using an older version of Excel and am now trying to make a pivot table using 2013 and installing it near the start of the existing Macro I have. How do I correct for this error?KB5002653 issue with the Kernel Function GetCommandLineW
Hello, Just to warn about this specific issue, since the installation of the KB5002653 , From an Excel 2016 VBA the function GetCommandLineW give a truncated/different result. Sample Code: Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long) Function ReadCmdLine() As String Dim pCmdLine As Long ' Pointer to the string Dim Buffer() As Byte Dim StrLen As Long ' Get the pointer to the command line string pCmdLine = GetCommandLine StrLen = lstrlenW(pCmdLine) * 2 If StrLen Then ReDim Buffer(0 To (StrLen - 1)) As Byte CopyMemory Buffer(0), ByVal pCmdLine, StrLen ReadCmdLine = Buffer End If End Function Before the KB I had : "C:\PROGRA~2\MICROS~2\Office16\EXCEL.EXE excelfile.xlsm /parameter" After the KB I have : ""C:\PROGRA~2\MICROS~2\Office16\EXCEL.EXE" excelfile.xlsm"
Help with VBA Code
Hello Experts...I am a novice, self taught, old golfer who has developed a workbook for calculating our golf game. The part I am struggling with is the calculation of team scores. I believe I need to have individual VBA macro buttons since the team game we play is different depending on how many golfers show up (typically 8-20). The scores are entered in the following format: The team score calculation needs to look at team #, gross score (yellow cells), calculate net score based on handicap (HDCP) and the number of scores needed for the team score. Net scores are determined by taking the gross score and determining if the golfer is eligible for a handicap stroke. Example: Bob M has a 7 HDCP (cell G2), on the #1 thru #7 handicap holes (row 16), one stroke is subtracted from his gross score to determine his net score. Team scores are reported relative to par (row 17) for the total 18 holes. The output should be a simple two column table with team # and 18 hole team score. Typical team games are: 2 golfer team best ball net score - The lowest net score of the two players. 3 golfer team 2 out of 3 net score total - The lowest 2 net scores of the three players. While there are additional games we play, if I can get these two programmed, I can use them as a guide to help script the remaining games. The workbook is 182MB so I can't attach it (it does a number of other things; team set up, payout calculations, etc...). Am I approaching this problem in the most efficient way or should I be focusing my efforts in a different direction? Thank you!
