I think this is what you meant, the following code loops through all rows with data, and for each row checks if the word “man” is inside that strings (using the Instr function). Sub FindMan() Dim FindString As String Dim Sht As Worksheet Dim LastRow As Long Dim lRow As Long ‘ modify “Sheet1” to … Read more
This should work: document.body.innerHTML = ‘<table>’+data.map(row=>'<tr>’+row.map(cell=>'<td>’+cell+'</td>’).join(”)+'</tr>’).join(”)+'</table>’; solved how do I make table from arry DATA [closed]
As can be found here, assume your dates are in the range, of say, A2:A1000, you could use the following formula: =INDEX(A2:A1000,MODE(MATCH(A2:A1000,A2:A1000,0))) 1 solved Find which cell value has the most number of rows? [closed]
Please try this, it should work for you, Sub AddCommas() Dim s As String Dim i As Long Dim j As Long For k = 1 To 20 For i = 1 To Rows.Count s = “” If Not IsEmpty(Cells(i, k).Value) And Int(Len(Cells(i, k)) / 10) >= 1 Then For j = 1 To Int(Len(Cells(i, … Read more
Your description is vague, but to get what you described exactly: Public Sub Process() If Range(“A2”).Value <> “” Then Range(“B2”).Copy Range(“C3”) Range(“B2”).EntireColumn.Delete End If End Sub Edit In worksheet code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = Range(“A2”).Address And Range(“A2”).Value <> “” Then Range(“C3”).Value = Range(“B2”).Value Range(“B2”).EntireColumn.Delete End If End Sub I think … Read more
I think there are still some questions to be answered (see my comments) but the following is a starting point with some assumptions made. 1) I leave the number of columns for the output table in a constant variable so you can choose how many columns you want: Const numberOfColumnsInTable = 4 2) I make … Read more
To get the corresponding value in Column C where column A is max: dim t as long dim rslt as string With Worksheets(“RESOURCE”) ‘ Change to your sheet t = Application.WorksheetFunction.Match(Application.WorksheetFunction.Max(.Range(“AX6:AX29”)),.Range(“AX6:AX29”),0) rslt = .Range(“A6:A29”)(t) Debug.Print rslt End With But this can be done with the following formula on the sheet: =INDEX(RESOURCE!A6:A29,MATCH(MAX(RESOURCE!AX6:AX29),RESOURCE!AX6:AX29,0)) 2 solved VBA: For … Read more
You need to avoid using Select and Activate. Set the workbooks, worksheets and ranges to variables and use them. Also when pasting just values avoid the clipboard for speed. Sub GetTW_Data() Dim tWb As Workbook Dim ebayWb As Workbook Dim tWs As Worksheet Dim ebayWs As Worksheet Dim rng As Range Set tWb = ThisWorkbook … Read more
I answered my own question: I counted the values within the red circle, by using: countif (setting the criteria (so the value would count the values i wanted) and set a ranger (so it wouldn’t count anywhere else) So for the bad description, and thanks to those who gave me ideas. Niall solved How to … Read more
You can do a regular expression replace in Notepad++. Find : FF.*?\-\-\- Replace with: (Blank Text) Search Mode: Regular Expression Check . Match New Lines checkbox 3 solved Notepad++ regex / Excel macro to delete specific range of lines
I’m pretty sure that the problem is the space in the name of your Faculty Name column and that this has nothing to do with the use of AND or OR. Please try: sql = “Update [Sheet1$] set A1 = ‘a’ ” + “where Designation = ‘Prof(senior)’ and [Faculty Name] = ‘bob'”; 1 solved IF … Read more
You could use a VBA macro like I created below. In my example I made the macro tied to a button click Sub Button1_Click() Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”) URL = “http://www.yourdomain.com/page.php?variable=” & ActiveWorkbook.Worksheets(1).Range(“A1”).Value objHTTP.Open “GET”, URL, False objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)” objHTTP.send (“”) End Sub Your PHP script on your … Read more
If the search date is in G1… If the search store is in G2… If the data are in columns A, B, C, and D… You can use this formula to return the first PO# where the search date is between the Start and End dates and where the search store matches Store #: =INDEX(D:D,MATCH(1,(A1:A999<=G1)*(B1:B999>=G1)*(C1:C999=G2),)) … Read more
Look into dplyr https://cran.rstudio.com/web/packages/dplyr/vignettes/introduction.html install.packages(“dplyr”) library(dplyr) df <- df %>% mutate(phys_pos=cumsum(length)+position) I am assuming your data.frame is named df Or with base R df$phys_pos <- cumsum(df$length) + df$position 1 solved In R; I would like to do something in R rather than excel because excel can’t handle the calculation. In excel the calculation is: =A2+SUM($B$2:B2)
A simple macro to do your task, Sub copyrow() Dim i As Long, j As Long j = Sheets(“SheetA”).Cells(Rows.Count, “A”).End(xlUp).Row For i = 2 To Sheets(“SheetB”).Cells(Rows.Count, “A”).End(xlUp).Row If InStr(Cells(i, 2), “abc”) > 0 Then Sheets(“SheetA”).Cells((j + 1), “A”) = Cells(i, 1) Sheets(“SheetA”).Cells((j + 1), “B”) = Cells(i, 2) j = Sheets(“SheetA”).Cells(Rows.Count, “A”).End(xlUp).Row End If Next … Read more