1. P

    Using Lastrow

    I’m trying to learn to write a sub without having to continuously repeat the Range on every line of code by using the command Lastrow. Below is a simple example of a Range A1:H15. <!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings>...
  2. B

    generate a random number to enter in a cell

    I am am looping through a range on my computer (Excel 2013) and where I find a number 1 I want to replace it with a random number between the bottom number in Cell I9 and the top number in cell I11 So what I need my code to do is Activecell.value= "Randbetween Range ("I9").value and Range...
  3. How_Do_I

    Unique in Range

    Hello, how would I count the unique in a range like this and extract them in to a vertical list please...? Excel WorkbookBCDE2Plum3PlumWatermelonApplePeach4MelonKiwifruitBlackberry5RaspberryBlueberryCranberryBananaSheet1
  4. K

    Modification in the formula

    Hello Folks , I am working on a file where in i am setting control limits "Upper Control Limit" and "Lower Control Limit" Sheet is working fine for me however need a small logic to be build in the below formula . =MOD(COUNTA(C24:C523)-1,K7)+1 In the above Formula "C24:C523 is the range...
  5. A

    Repeating same range of data

    Hello, How can I dos this? I want to repeat a range of data which contains 3 colums and 70 rows 20 times. First colum contains a reference number, same for all 70 rows, second column a vale which doesn't change and third colum the name of 70 different countries. I want this range to repeat again...
  6. A

    Populate Range from string

    I'm passing a Range to my Form via a Property Dim Rng as Range Public Property Let rRange(z as Range) Set Rng= z End Property Then I attempt to populate that range (which is a single cell) Private Sub cmdApply_Click() Worksheets("Sheet1").Range(rng).Value = Me.txtTest End Sub I get error 1004...
  7. A

    Get Column Name from Range

    If a range is Set MyRange = Application.Range("Sheet1!A2:G2") Is it possible to get the name (Header) of the column anyhow ? I tried ? MyRange(2).Name but no go.
  8. A

    Range Code Fails reading Comment text

    Just wondering why this goes to error 91 "object or with block not set" Dim MyRange as Range Dim a Set MyRange = Application.Range("Sheet1!A2:G2") a= MyRange(1).comment.text ' Set a= MyRange(1).comment.text Both 'a=' lines result in the same error 91 Thanks.
  9. I

    IF X then display Range A in Range B

    hi, I'm looking for a formula to display a range of cells if condition is met. For example IF A1 equals to "test" then display range F1:G4 in range B1:D5. Any ideas? Thank you
  10. H

    Multiple Range Copy

    Hi - I have this code to copy rows to another sheet. I thought I could extend the range by add this code. But no, any suggestions on how I include the extra columns in the copy range? Tried this ws1.Range((ws1.Cells(thisRowA, "B"), ws1.Cells(thisRowA, "H")), (ws1.Cells(thisRowA, "K")...
  11. R

    For Each Visible cell in range, get me the left 6 characters

    Hi all, venturing into the wild world of For Each instead of Do Until loops, have the following: Dim LastrowAD As LongDim cl As Range Dim visrng As Range LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row Set visrng = Range("A2:A" & LastrowAD) For Each cl In...
  12. M

    Name manager - adding data manually as reference

    Hello, Normally we create named ranges by referring to excel lists - we select the data range that is located in certain cells in worksheets. I tried to create named ranges by manually adding data to "refer to" part. But it didn't work - all the data that I added appeared in one cell, so not...
  13. T


    In this code: Dim Rng As Range Set Rng = Sheet1.Range("A1:D4") Dim RngElement As Range For Each RngElement In Rng RngElement.Value = 100 Next RngElement it populates every cell in the range A1 to D4 with the value of 100. I was of the understanding that the variable RngElement...
  14. kweaver

    VBA cannot find SUMIFS in WorksheetFunction class

    What's wrong here? Dim Arg1 As Range 'the range i want to sum Dim Arg2 As Range 'criteria range Dim Arg3 As Range 'the criteria (range) Set Arg1 = Sheets("TextFile").Range("M5:N10000") Set Arg2 = Sheets("TextFile").Range("K5:K10000") Set Arg3 =...
  15. K

    Count number of times a cell reference appears within formulae in a range

    Hi Guys, In each of cells G85:96 I have a simple CountA formula like these: in G85 =COUNTA(B21,B43,B44,B45) in G85 =COUNTA(B21,B32,B44,B51) ...in which I have manually selected those cells in column B. What I'd like to do in say H21 is have a count of the times that cell B21 appears in...
  16. A

    match on multiple columns

    Hi, i have a list of items on column A, for example - A100 A101 B100 C100 D202 A103 and another list of items in range D1:E10 the list in the range contains multiple items in different order than the original list. (partial) example- <tbody> D E A100 A101 D202 B100 Z400 A103...
  17. I

    Inputbox to select the range from user to apply a specific function.

    Hi All, I have following VBA code to remove extra hidden characters from a range. It works fine. But many times I need to change the range for removing extra hidden characters and for this everytime I have to go edit the macro. I want an inputbox to be appeared to select (from user) the range...
  18. G

    How can I set a range variable that only includes cells in that range with data?

    I have a very slow subroutine that uses a For Each statement to iterate through about 30 cells in a pre-defined range, checking if they have text in them, and then performing a bunch of formatting based on the text in that cell. I'm wondering if there is a faster way to do this that would...
  19. M

    VBA Modifed Date

    What i would like to do is if any cell in C5:H10 is modified i want the date and time to be placed in column B. Currently this is what i have Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C5:H105")) Is Nothing Then Exit Sub ' C1:C100 is the price range...
  20. Nelson78

    Vba: search a string in a range, if found highlights the cell

    Hello everybody. I'm dealing with searching a string in a range: X = "ABCDEF" Dim foundVal As Range Set foundVal = Worksheets(2).Range("C2:C21").Find((X), LookIn:=xlValues, lookat:=xlPart) My deadlock is: I need to highlight, for example in yellow RGB(0, 255, 0), the cell where the...

Some videos you may like

This Week's Hot Topics