Get instant live Excel expert help to learn how to find range in Excel

I would like to add a new value to column U based on defined ranges where the range is defined by equal values in column M. I would like the value added to Column U to be placed in the top row of each set of ranges. if the value in column T is positive, I would like to add it to column U, if it is negative I would like to add a zero.

I am trying to create a formula which will replicate the results I have typed into column U. The ranges are defined by equal values in Column M, which then looks to column T and pulls the number. If the number is zero or larger, the number is placed in the top row of the range in column U, if it is less than zero a zero is added to the top row in column U.

I have a source data sheet and I working on a summary sheet to pull data together. e.g.: Summary Tab Date range; 1.7.17 - 31.7.17 Shirts Dress Skirts On source document - I have individual lines which are coded to the above categories and other sub-categories, with delivery dates. the source doc dates range over 12 months I want to be able to paste in updated source data & have the summary page update - changing the date range above to move through months. The basic category SUMIF are fine. But when I need to sort by both the Category & then again by sub-cat I get error. I also need a formula that adds up only the numbers in that category with a delivery date within {that range}?? Can you help?

Checking a range of data for a condition (when sales launch), and returning the corresponding launch year (found in a row at the top of the data).

I have this code - and would like to have it applied to all the cells (G2:G66666) but that the code applies to its adjacent cell on the left. Option Explicit Sub test() Dim a, i As Long, mytxt As String, temp As Long, msg As String, lastRow As Long a = Range("a1").CurrentRegion.Value For i = 1 To UBound(a, 1) a(i, 1) = GetAsValue(a(i, 1)) a(i, 2) = GetAsValue(a(i, 2)) Next mytxt = Range("f2").Value temp = GetAsValue(mytxt) If temp = 0 Then Range("G2").Value = "Invalid postalcode" Exit Sub End If For i = 1 To UBound(a, 1) If (Not IsError(a(i, 1))) * (Not IsError(a(i, 2))) Then If (temp >= a(i, 1)) * (temp <= a(i, 2)) Then msg = a(i, 3) Exit For End If End If Next Range("G2").Value = msg End Sub

Hello, I am looking to write a macro for a command button that hides certain columns based on a drop down selection that contains the column headers. I have data from columns P:BE and for example the header in column P is "BEV01" so if I select "BEV01" from my drop down list, the command button will search that and if it says "BEV01" it will hide all other columns besides this. However when I go to write an elseif for the second column header "BEV02" I get an error "13 item does not match. So the code works for when BEV01 is selected in the drop down in cell K2 but not for when BEV02 is selected in K2. I am eventually going to continue this code for the entirety of the columns. See code below: what can i do to fix this? thanks Private Sub CommandButton1_Click() If Range("K2").Value = "BEV01" Then Columns("Q:BE").EntireColumn.Hidden = True ElseIf Range("K2").Value = "BEV02" Then Columns(":BE").EntireColumn.Hidded = True Else Columns("Q:BE").EntireColumn.Hidden = False End If End Sub

I have a table which lists the schedule of classes for a school. The table is broken down into one hour time slots (columns) and subjects per days of the week (rows). In each time slot per subject is a class number. Each class takes one subject per day. My goal is to say to Excel: "Look in this range and tell me which subjects class X is taking"

Need code improvement help Want to specify a list of extensions to search for and not have to run code multiple times to grab them. Keep in mind the following instructions: 1. "Source" sheet is the file that contains your database 2. You wil have to add a blank sheet to workbook and name it Destination 3. .pdf and .xlsx and more are the extensions extracted to Destination using this code. 4. Run the code once, all .pdf extension files will be xtracted to Destination, then run the same code again, all .xlsx will be extracted to Destination below the .pdf's Sub Extraction() Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long I = Worksheets("Source").UsedRange.Rows.Count J = Worksheets("Destination").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Destination").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Source").Range("F2:F" & I) On Error Resume Next Application.ScreenUpdating = False For Each xCell In xRg If CStr(xCell.Value) = ".pdf" Or CStr(xCell.Value) = ".xlsx" Or CStr(xCell.Value) = ".xls" Or CStr(xCell.Value) = ".doc" Or CStr(xCell.Value) = ".docx" Then xCell.EntireRow.Copy Destination:=Worksheets("Destination").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1 End If Next Application.ScreenUpdating = True End Sub

Keep in mind the following instructions: 1. "Source" sheet is the file that contains your database 2. You wil have to add a blank sheet to workbook and name it Destination 3. .pdf and .xlsx and more are the extensions extracted to Destination using this code. 4. Run the code once, all .pdf extension files will be xtracted to Destination, then run the same code again, all .xlsx will be extracted to Destination below the .pdf's Sub Extraction() Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long I = Worksheets("Source").UsedRange.Rows.Count J = Worksheets("Destination").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Destination").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Source").Range("F2:F" & I) On Error Resume Next Application.ScreenUpdating = False For Each xCell In xRg If CStr(xCell.Value) = ".pdf" Or CStr(xCell.Value) = ".xlsx" Or CStr(xCell.Value) = ".xls" Or CStr(xCell.Value) = ".doc" Or CStr(xCell.Value) = ".docx" Then xCell.EntireRow.Copy Destination:=Worksheets("Destination").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1 End If Next Application.ScreenUpdating = True End Sub Code Snips for re-use To Search for Extensions in bulk Sub ConvertWordsToPdfs() Dim directory As String directory = "C:\Wordup" ' The starting directory Dim fso, newFile, folder, files Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(directory) Set files = folder.files For Each file In files Dim newName As String newName = Replace(file.Path, ".doc", ".pdf") Documents.Open FileName:=file.Path, _ ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _ PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _ wdOpenFormatAuto, XMLTransform:="" ActiveDocument.ExportAsFixedFormat OutputFileName:=newName, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False ActiveDocument.Close Next End Sub OPENING MULTIPLE FILES Sub OpenMultipleFiles() Dim Filter As String, Title As String, msg As String Dim i As Integer, FilterIndex As Integer Dim Filename As Variant ' File filters Filter = "Excel Files (*.xls),*.xls," & _ "Text Files (*.txt),*.txt," & _ "All Files (*.*),*.*" ' Default filter to *.* FilterIndex = 3 ' Set Dialog Caption Title = "Select File(s) to Open" ' Select Start Drive & Path ChDrive ("E") ChDir ("E:\Chapters\chap14") With Application ' Set File Name Array to selected Files (allow multiple) Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True) ' Reset Start Drive/Path ChDrive (Left(.DefaultFilePath, 1)) ChDir (.DefaultFilePath) End With ' Exit on Cancel If Not IsArray(Filename) Then MsgBox "No file was selected." Exit Sub End If ' Open Files For i = LBound(Filename) To UBound(Filename) msg = msg & Filename(i) & vbCrLf ' This can be removed Workbooks.Open Filename(i) Next i MsgBox msg, vbInformation, "Files Opened"' This can be removed End Sub

I want cell H5 to count every date is that is in June from B13:B102 . I have my helper 2 helper cells set with the date range starting at M5 and ending on O5. What is wrong with my formula?? =COUNTIFS($B$13:$B$102, ">="&$M$5,$B$13:$B$102,"<="&$O$5)