range

  1. O

    Dividing a Defined Range by a Defined Range

    Hello, Been trying to figure this out for a few days now, and starting to drive me crazy. Sub Annualize() Dim WB As Workbook Set WB = ThisWorkbook Dim WS As Worksheet Set WS = WB.ThisWorksheet Dim Range1 As Range Set Range1 = WS.Range("B2:B4") Dim Range2 As Range Set Range2 =...
  2. M

    VBA: Find and Group All Occurrences of Value, Run Sub, Move to Next Group

    Hi, I need some help getting started on a code. I have a range ("B4:B") of variables that are related by the first four characters (the year). Currently an input box will appear asking which year to run the code in, and the default value is based on the left four characters of the active cell...
  3. S

    Take numbers in a list and convert to ranges for consecutive numbers

    I have a problem that I'm sure there is a formula that can perform this function. I have a list of numbers, some are consecutive and some are outliers. I need to take this data and create a "From" "To" column on those numbers that are consecutive. Below is an example of the data that I have...
  4. A

    conditional range

    I need to create a range between B5:M5 that is defined by the location of P1 (phase 1) and P2 (phase 2) in Row 4. The range should begin one column to the right and one row down from P1 and end at the cell one column to the left and one row down from P2. P1 and P2 are both movable and so...
  5. MikeMcCollister

    How do I get a sub-range in VBA

    I have a table called "MainChecking". I can get the range for a column of the table by using the .Range function. Now I want a subset of that range but I can't figure it out. For example, this range may have 500 rows in it with just one column. I want to get a range that will be from row 229 to...
  6. A

    Method Clear Contents of Object Range Failed

    Hi I have the following simple code, never had an issue with it before but all of a sudden I am getting the "Method Clear Contents of Object Range Failed" run time error. From what I have read this isnt so much an error with the code but more to do with the system is that correct? If anyone...
  7. S

    VBA - send email with multiple ranges in body with 'If Target.Value'

    Hi, I am trying to have a VBA to send out an email if when several rows which have a special character in column A, The VBA I have to add this character is : (wingding font for the check symbol) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub...
  8. D

    Replace word loop

    Hello, I am trying to build some macro that would replace some word from a table, based on a list of words, and paste each time the table one after the other. Example: Cat1 10 Replace Cat Horse1 10 Cat2 20 With Horse Horse2 20 Cat3 30 Cow Horse3 30 Cat4 40 Dog...
  9. lockarde

    Sort range based on value

    Good morning all, I have an issue with a spreadsheet I use to track job details. Ideally, these details are supposed to be entered as they come in, but sometimes people wait, and suddenly there are details from several days or even weeks past. Is there a way to sort all of these details by...
  10. D

    Highlight cells with Conditional Formatting using large range of values

    Hello all, this is my first post. I’m having an issue with Conditional Formatting. I work in a warehouse, and I am having issues in a certain section of the warehouse with items being split across several different stock locations rather than being consolidated into 1 or 2 locations. There is...
  11. L

    How to loop through cell to increment value on a different cell through VBA

    Hi I am trying to create a VBA script. I have Customer Number on Column A, Date on Column B and Seq on Column C. I start with Seq 1 for the first customer and date. If the next row has the same customer and the same date, I put seq 1 on column 1. On change of any date for the same customer...
  12. D

    Loop with Range that requires offset

    Hi all, Hope someone can help, im trying to make a loop that relies on a range, which should be changing every cycle. Range("Q244").Select Do Until ActiveCell.Offset(0, -1) = "" Set RangeLoop = Range("C8:C25") With ActiveCell RangeLoop.Copy...
  13. P

    Using Table Headers as ranges in Data Validation

    Hi Members, I have a table named "Books". My table is very simple with 3 columns. Book.ID, Book.Name, Book.Author All i want to do is to avoid duplicate values in Book.ID column of a table using data validation. I simply used the formula "=COUNTIF(Books[Book.ID],Books[@[Book.ID]])=1". But it...
  14. M

    VBA - Need help formatting a row range based on string or format in column A

    I have the code below that works. It searches column A looking for "00" as the left most characters and then formats that cell with white text and black interior. Dim cel As Range For Each cel In Range("A6:A300") If Left(cel.Value, 2) = "00" Then cel.Font.Color = vbWhite cel.Interior.Color =...
  15. L

    How to return the starting balance of a bank account in a master list of bank accounts

    Hi everyone, I've built a workbook that evaluates a client's banking information and processes multiple bank accounts at a given time. I'd like to find a formula that will return the starting balance of each unique bank account from my dataset to reduce human error with data input. The...
  16. M

    Advice needed

    I have been asked to write a macro for the attached spreadsheet. This counts the number of days each project remains in a certain status. For it to calculate the days a date has to be inserted into the column immediately to the right of the column containing a date or a formula that would...
  17. C

    Loop a range and skip rows in Excel Worksheet

    I have an excel worksheet that accepts input from another excel file. This excel file has structured data in which I need to separate individually as sheets. I already have the following code to copy and format that data in a certain range but I need to loop this process for the whole worksheet...
  18. D

    how to use range and calculate offset

    I have the following code : Sub checkit() Dim c As Range Dim z1 As Integer Dim LastRow As Integer With Worksheets(1).Range("A1:O100") z1 = 0 LastRow = UsedRange.Rows.Count For Each c In Range(.Cells(2, 9), .Cells(LastRow, 11)) If c.Interior.Color = RGB(255, 255, 0)...
  19. O

    Trying to create a loop

    ThisWorkbook.Save Sheet4.Range("A1").Value = Environ("Username") Sheet4.Range("B1").Value = Time() Sheet4.Range("C1").Value = Date Sheet4.Range("D1").Value = ActiveWorkbook.Name I'm sorry if this is a stupid question, I'm new to this, how can I get this code to repeat itself. It works but...
  20. D

    Copy/Paste Range issue with VBA when cells are empty (but with formulas inside)

    Hello, Currently having some issues when copy/pasting some range of rows. There are some formulas in there that would be populating depending of another table. So in the end there would be some rows populating from such table, and eventually remaining rows without populated values would remain...

Some videos you may like

This Week's Hot Topics

Top