Google Apps script is based on javascript. VBA is based on Visual Basic. Hardcoding it is a possibility but you end up with some funky array and you’re then limited to VBA’s clunky array methods (or lack thereof). Function createChargeList() Dim var(5) var(0) = [{“No.”, “Name”, “Cooldown”, “Power”, “Energy Loss”, “Type”, “Damage Window Start”}] var(1) … Read more
this will iterate column A and reverse all numbers found: Sub FLIPNUM() With Worksheets(“Sheet1”) Dim rngarr As Variant rngarr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value Dim i As Long For i = LBound(rngarr, 1) To UBound(rngarr, 1) Dim str() As String str = Split(rngarr(i, 1)) Dim j As Long For j = 0 To UBound(str) If … Read more
Here’s how to connect the macro to a button Private Sub Button1_Click() Dim val As Long val = Application.InputBox(Prompt:=”Enter Amount”, Type:=1) Range(“C43”).Value = Range(“C43”).Value + val End Sub 12 solved excel popup button to ask how much money to add to an existing cell with an existing amount [closed]
So, brutal but does work: =LEFT(A1,FIND(“,”,A1,FIND(“,”,A1,FIND(“,”,A1,1)+1)+1)-1) You can play with MID() and RIGHT() to get others…. See solved How to split a cell of many numbers by group of 3-3 in each next cells?
Something like this should get you started. I have tried to comment it pretty thoroughly so as to explain what is happening in the macro: Sub CopySomeCells() Dim targetSheet As Worksheet ‘destination for the copied cells’ Dim sourceSheet As Worksheet ‘source of data worksheet’ Dim rng As Range ‘range variable for all data’ Dim rngToCopy … Read more
The first code creates and uses a one-dimensional array.The second code creates a two-dimensional array. That is why Join() fails in the second case. 1 solved Not understanding Array range reference [closed]
Don’t use the Name property. Use Value. How are you populating the array? If it has base index of 0, then increment Count after setting the field value. 3 solved How to set column values in a record set in access vba
Thanks to Cindy’s nice tip (I could also have found relevant information in Loop through Word document, starting from beginning of file at start of each loop), and in case this could help someone some day: define the format you are looking for thanks to Word’s Macro Recorder position yourself at the beginning of your … Read more
To delete repeating item section for specifically named RSCC just edit the code you found in the other question. Set cc = ActiveDocument.SelectContentControlsByTitle(“RepCC”).Item(1) ‘to delete the first RepeatingSectionItem cc.RepeatingSectionItems.Item(1).Delete ‘to delete all but the first RepeatingSectionItem Dim index As Long For index = cc.RepeatingSectionItems.Count To 2 Step -1 cc.RepeatingSectionItems.Item(index).Delete Next index ‘to delete the entire … Read more
This should work: Sub test() Dim wbk As Workbook strFirstFile = “C:\source.xls” strSecondFile = “C:\destination.xls” Range(“A1:HC35”).Copy Set wbk = Workbooks.Open(strSecondFile) With wbk.Sheets(“Sheet1”) Range(“A1”).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End With End Sub solved Macro for copying cells from one workbook to another
One way to tell if the entry has been previously split is as follows If the regex.test fails, then If the results line passes, then the item has been previously split if not, then it is a blank, or a malformed entry Note that a lot of this could be avoided if you were not … Read more
Go to the worksheet of interest and: 1) Click the top left to grey triangle to select all cells 2) Right click Format Cells 3) Ensure Locked box is ticked 4) Go to Review tab and select Protect Sheet and enter your password e.g. “password” In the code associated with the check box you would … Read more
Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort on … Read more
To calculate Years of Increases enter the following formula in Cell L2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>A2:I2=FALSE,1,””)))),0) and to calculate Years of Steady Profits enter below formula in Cell M2 =IFERROR(COLUMN(J2)-COLUMN(INDEX(B2:J2,,MATCH(9.99E+307,IF(B2:J2>=A2:I2=FALSE,1,””)))),0) Both the above formulas are array formula so commit by pressing Ctrl+Shift+Enter. Drag/Copy down as required. See image for reference. In case you want this formula to be … Read more
Try conditionally formatting A2:A12 with the following formula: =IF(ISERROR(SEARCH(“*”&$A2&”*”, $A$1)>0), FALSE, TRUE) (see screenshots below). 1 solved Highlight duplicate values in excel