Check out the Quick Guide on using Apache POI‘s Excel Component API. It sounds like you want to open a file, set some cell values, and write back to a file. See the section “Files vs InputStreams” for how to open a file and “Creating Cells” for how to set values for cells and write … Read more
You can try this: Sub find() Dim x As String, i As Long, lastrow As Long, y As Long Dim sh1 As Worksheet, sh2 As Worksheet Set sh1 = Sheets(“Sheet1”): Set sh2 = Sheets(“Sheet2”) x = “ra01” With sh1 lastrow = .Range(“F” & .Rows.Count).End(xlUp).Row For i = 2 To lastrow If InStr(.Range(“F” & i).Value, x) … Read more
Create a new module in your VBE and stick this in there: Sub capturetime() Dim timeWS As Worksheet Dim timeRange As Range ‘Change “Sheet3” to whatever worksheet is your click log Set timeWS = ThisWorkbook.Sheets(“Sheet3”) ‘find the last cell in column A of your log Set timeRange = timeWS.Range(“A” & timeWS.Rows.Count).End(xlUp).Offset(1) ‘Write which button was … Read more
This function takes the Value in Sheet “Feuil2”, Cell A3 and compares that to the values in sheet “SUIVI STABILITE”, cells from D4 to AL4. It then adds all of the values in sheet “SUIVI STABILITE”, cells from D24 to AL24 that are in the same columns as the matches from the first 2 conditions. … Read more
A solution in Matlab: A = [1 5; 1 5; 1 6; 2 4; 2 2; 3 8; 3 4]; nMaxDays = max(A(:, 1)); nMaxSamples = max(accumarray(A(:, 1), 1)); mnSamplesMatrix = nan(nMaxSamples, nMaxDays); for (nDay = unique(A(:, 1))’) vnThisDay = A(A(:, 1) == nDay, 2); mnSamplesMatrix(1:numel(vnThisDay), nDay) = vnThisDay; end 0 solved Matlab/Excel/R: Transforming an … Read more
Your description is not that detailed, but I took a shot, and from what I understand you might be looking to have something like this: Where you would put a number into B1, and then column C would print out as many statements as requested. Here’s the formula to that: C1=IF(ROW()<=B$1,”Enter load for Span ” … Read more
Try using Public Variable like this: Public n As Long ‘~~> Declare a public variable at the top of the module Then in your sub try something like this: With Sheets(“Sheet1”).Range(“A6”).Offset(n, 0) ‘~~> change to suit If n = 0 Then .Value = 1 Else .Value = .Parent.Range(.Address).Offset(-1, 0) + 1 End If n = … Read more
If I understand your question correctly: for each Cell in H2:H98, you’re looking for a match in A2:A98. It won’t necessarily be on the same row. If you find a match in Column A, you want to take the value from Column B and put it in Column I on the same row as the … Read more
I could able to get the below code with my friends help. Sub splitData() Dim wb As Workbook Dim wsSample As Worksheet Dim wsMacro As Worksheet Dim lr As Long Dim i As Long Dim j As Integer Dim wRow As Long Set wb = ActiveWorkbook Set wsSample = wb.Worksheets(“Sample_Raw_Data”) Set wsMacro = wb.Worksheets(“Macro Results”) … Read more
This function will work for you Function ReturnUnique(cell1 As Range, cell2 As Range) As String ReturnUnique = “” Dim v1 As Variant, v2 As Variant v1 = Split(cell1.Value, “,”) v2 = Split(cell2.Value, “,”) Dim i As Long, j As Long Dim bool As Boolean For i = LBound(v1, 1) To UBound(v1, 1) bool = True … Read more
I believe this is what you are looking for below, this will take a value from cell B1 and compare against values in Column A, and if the values are less than the value in B1, it will clear the contents of that cell: Sub ClearLowerThan() Dim c As Range, Rng As Range Dim LastRow … Read more
Sub CopyToRange() Dim vDB, vR() Dim rngDB As Range Dim Cnt As Long, i As Long, j As Integer Dim n As Long Set rngDB = Range(“a1”, Range(“a” & Rows.Count).End(xlUp)) vDB = rngDB Cnt = 272 For i = 1 To UBound(vDB, 1) Step Cnt n = n + 1 ReDim Preserve vR(1 To Cnt, … Read more
Since you don’t seem to have a problem using helper columns, this can be done in a fairly straightforward way, given that your data is already sorted by date. RAW DATA TAB Add a new column (we’ll call it column X) which checks to see if your cell is the first cell which starts a … Read more
While I agree the OP does not appear to have done their fair share of figuring this out (how hard is Google?), I know someone else will be looking in the future. Posting information will help them. @OP, I’m not going to do all your file handling work for you but here is the basic … Read more
This should give the basic idea – Use the item you want to count as the key, and the count itself as the value. If it is already in the Dictionary, increment it. If not, add it: Const TEST_DATA = “Apple,Orange,Banana,Banana,Orange,Pear” Sub Example() Dim counter As New Scripting.Dictionary Dim testData() As String testData = Split(TEST_DATA, … Read more