Do like this. Sub test() Dim vDB, vR() Dim i As Long, n As Long Dim s As String vDB = Range(“g1”, Range(“g” & Rows.Count).End(xlUp)) n = UBound(vDB, 1) ReDim vR(1 To n, 1 To 2) For i = 1 To n s = vDB(i, 1) vR(i, 1) = Split(s, “https://stackoverflow.com/”)(1) vR(i, 2) = Split(s, … Read more
Try this formula: =IFERROR(INDEX($A$2:$A$27,AGGREGATE(15,6,ROW(2:$26)/((NOT(ISNUMBER(SEARCH(“.”,MID($B3:$B$27,LEN($B2)+2,999)))))*(SEARCH($B2 & “.”,$B3:$B$27)=1)),COLUMN(A:A))),””) The AGGREGATE() Function was introduced in Excel 2010. 5 solved How to assign children to parents using Excel?
Or this way: Sub AssignWSobjectsToWorksheets() Dim i As Integer, n As Integer Dim ws() As Worksheet n = ThisWorkbook.Worksheets.Count ReDim ws(1 To n) For i = 1 To n Set ws(i) = ThisWorkbook.Worksheets(i) Next End Sub Note that Sheets is the collection of all tabs(e.g. including charts), Worksheets just of spreadsheets solved loop to set … Read more
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
cell A1 = **cris** cell B1 = **john** cell C1 = **bob** cell D1 = **stev** cell E1 = **david** cell F1 = **michie** cell G1 = **toney** cell H1 = **jack** cell I1 = **sese** and cell J1 = **cris** cell K1 = **john** cell L1 = **bob** cell M1 = **stev** cell N1 … Read more
If you have Excel 2013+ with the FILTERXML function you can: convert the string into an XML, using the spaces for the different nodes “<t><s>” & SUBSTITUTE(A$1,” “,”</s><s>”) & “</s></t>” use an Xpath to extract the nodes containing the # “//s[contains(.,’#’)] in the formula, [” & ROWS($1:1) & “]”) becomes a position argument in the … 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?
import csv one_year_reserved = { ‘australia-central’: 0.0097, ‘usgov-virginia’: 0.00879, ‘us-south-central’: 0.00731, ‘france-south’: 0.01119, ‘us-west’: 0.00719, ‘europe-north’: 0.00822, ‘asia-pacific-east’: 0.0097, ‘japan-east’: 0.00799, ‘west-india’: 0.00833, ‘united-kingdom-west’: 0.00685, ‘usgov-arizona’: 0.00879, ‘brazil-south’: 0.00982, ‘australia-east’: 0.00776, ‘us-west-2’: 0.00605, ‘asia-pacific-southeast’: 0.00776, ‘south-india’: 0.01005, ‘us-central’: 0.00731, ‘us-east-2’: 0.00605, ‘south-africa-west’: 0.01016, ‘canada-central’: 0.00674, ‘south-africa-north’: 0.00811, ‘canada-east’: 0.00685, ‘us-east’: 0.00605, ‘korea-south’: 0.00639, ‘united-kingdom-south’: 0.00685, … Read more
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]
=COUNTIF(H:H,”=”&A6) is working. Comparing all the referrals(H:H) with each email (A) and appending count to every respective column. solved Count the referrals in excel
Either LINQ: List<Guid> guids = guidStrings.Select(Guid.Parse).ToList(); or List.ConvertAll which is a little bit more efficient because it knows the size: List<Guid> guids = guidStrings.ConvertAll(Guid.Parse); 0 solved Convert a Collection of Strings into Collection of GUID
EDIT:?? EDIT: ADD MY SAMPLE INPUT & OUTPUT RESULT EDIT: Variable added, ChuckSize EDIT: also change the lane startCol = objSheet1.Range(“A1″).column The “A” to “S”, to whatever column your PID is at, assumption made: Your data starts from row 1 A solution using @Tim’s Solution + the 2D Array optimization tech. Sample Input: A A … Read more