The following array formula returns the first entry in column B where it is not null and also where column A has cell value aaaaa. = IFERROR(INDEX(B1:B6,MATCH(1,(A1:A6=”aaaaa”)*(B1:B6<>”null”),0)),”no match”) Note this is an array formula, so you must press Ctrl+Shift+Enter on the keyboard after typing the formula rather than just pressing Enter. To return a similar … Read more
You should never divide by zero. locate two consecutive peaks in column B calculate the time difference between the corresponding times in column A divide 1 by this time difference By the way this is not an Excel issue: 1 solved Divide by zero error when calculating frequency?
Try this code below : Sub sumTotal() ‘dim array to store unique names Dim uniqueArray As Variant ‘Sheets(your chosen sheet) With Sheets(1) ‘find last cell with value in A Set last = .Range(“A:A”).Find(“*”, Cells(1, 1), searchdirection:=xlPrevious) ‘for each cell in column to last value found For n = 1 To last.Row ‘if name isnt in … Read more
Change: MsgBox strOutput, “Final Salary” To: MsgBox strOutput, vbOKOnly, “Final Salary” Or: MsgBox Prompt:=strOutput, Title:=”Final Salary” You are trying to put a string in where a button type is supposed to be. 1 solved Tax calculation in vba, run type error ’13’ type mismatch [closed]
I understood your goal as: single chart per sheet that exists in the All Data list Your code was creating (as @vba4all suggested) too many charts. I added: sheetsHandled as Collection to hold the list of sheets that already got their charts. sheetName to hold the name of the sheet used many times in code. … Read more
Try this code Sub Loop_Through_Text_Files_Count_Lines() Dim fso As Object Dim pth As Object Dim strFolder As String Dim strFile As String Dim r As Long With Application.FileDialog(msoFileDialogFolderPicker) If .Show Then strFolder = .SelectedItems(1) & “\” Else Exit Sub End With Set fso = CreateObject(“Scripting.FileSystemObject”) strFile = Dir(strFolder & “*.txt”) Do While strFile <> “” r … Read more
In C2 enter: =INDEX($A$2:$A$99,ROUNDUP(ROWS($1:1)/3,0)) & “_” & INDEX($B$2:$B$33,MOD(ROWS($1:1)-1,3)+1) and copy downward: NOTE: The number 3 in the formula above is there because there are 3 items in column B To “generalize” the formula replace: 3 with: (COUNTA($B$2:$B$99)) solved How to concatenate values from one column with each vales from second column? [closed]
You’ve literally not provided any code that you’ve attempted to use to do this, more people would gladly help if you would have at least attempted to code it… Nonetheless, you did not provide your sheet names, here is some code you can put to the test. Sub CopyValue Dim Wb1 As Workbook, wb2 As … Read more
I am guessing you were translating this from another language? You really should have pointed out which lines were failing and what you researched. The “”: Smart quotes. This is typical of when copying between applications, so be careful. The speech marks for use in the visual basic editor need to be “” in order … Read more
In Excel 2016 (but NOT Excel 2013), you can use the following array-entered formula. =TEXTJOIN(“,”,TRUE,IFERROR(1/(1/(ISNUMBER(FIND(“,”&TRIM(MID(SUBSTITUTE(B2,”,”,REPT(” “,99)),seq_99,99))&”,”,”,”&A2&”,”))))*TRIM(MID(SUBSTITUTE(B2,”,”,REPT(” “,99)),seq_99,99)),””)) seq_99 is a Named Formula Refers to: =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99) To enter an array formula, after entering the formula in the cell, confirm by holding down ctrl + shift while hitting enter. If you do it correctly, Excel will place … Read more
Final try on this one Use Home…advanced editor… and paste this function into PowerQuery. Name it: fnReadFile (zFile)=> let Source = Csv.Document(File.Contents(zFile),[Delimiter=”#(tab)”, Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]), #”Added Index” = Table.AddIndexColumn(Source, “Index”, 0, 1), // Date row includes text “Date: ” DateFind = Text.Replace(Table.FindText(#”Added Index” , “Date”){0}[Column1],”Date: “,””), // Row preceeding data includes text “>>>” CaratFind = … Read more
Alright, I thank you @QHarr for your help. In the end, non of those were what was needed as an answer, but I have found the way. I created, on a separate sheet called “PivotTable”, a table based on cells with the following (and similar) formulas: =INDEX(Metrics!$B$2:$ZZ$2,1,Metrics!A57,1) “B2:ZZ2”, had their numbers changed in each cell … Read more
website: not really. You need msoffice to run the sheet, so unless you manually execute the sheet previously so that the data is available for read (not execute) you can’t do it with ms excel. You can do it with a programming language, depending on what you need and what you are familiar with you … Read more
You could you a simple userform to select and parse data selection into the row you clicked. First use an Event when you click in the column you want the reminder to pop up in. In this case I choose “D”. Private Sub Worksheet_Change(ByVal Target As Range) Dim arr If Not Intersect(Target, Range(“D:D”)) Is Nothing … Read more
Here is a working code of a VBA function that fits your needs: Sub RedistributeData() Dim X As Long, LastRow As Long, A As Range, Table As Range, Data() As String Const Delimiter As String = vbLf Const DelimitedColumn As String = “B” Const TableColumns As String = “A:B” Const StartRow As Long = 1 … Read more