range address

  1. S

    Copy and paste the Range address displayed in the Msg Box below subroutine to any selected cell

    I have a following code which displays the Range address in the msg box after executing the code I am looking to copy and paste Range address to any of the Selected cell. Sub GetRangeAddress() Dim r As Range Dim s As String Select Case Selection.Areas.Count Case 0: MsgBox "Nothing...
  2. lynxbci

    Object required

    Hi, I am trying to select a column range by first finding which column has the title I require (in this case "UseThis") Then I want to select the range from that address (f.address) and offset it by a number of rows (in this case I am using 100) The code works and msgBox shows $H$1 as expected...
  3. Worf

    Worksheet page addresses with VBA

    Sometimes we need to know the addresses of all the pages that make up a worksheet. The code below lists them and offers an option to colour each page differently. Sub Main2() PageAddress2 True End Sub Sub PageAddress2(colorcode As Boolean) Dim c%, v%, h%, cln%, rw%, hgth%, wth%, i%, ws As...
  4. F

    Dictionary - defining range

    Hi I would like to add a list of items to a dictionary and then loop each item. The problem is, I don't know how to refer to a list range that is on another worksheet than the main data that is being filtered. "Items" are located on sheet wsList, so where am I suppose to add it? Dim dict As...
  5. R

    Run-time error '1004' Method 'Range' of object '_Global' failed

    Can't quite see/figure out what I've done wrong (I don't know what I don't know :confused: ). I'm getting: Run-time error '1004' Method 'Range' of object '_Global' failed I've tracked the error down (I think) to the FOR-NEXT loops in the following snippet of code (edited for...
  6. M

    How to copy selection.address cell values to another workbook.?

    hi, I am new to VBA and tryin to reduce some workload to make a small VBA program to copy range randomly with mouse and paste it to destination file. source file is "Mytest.xlsm" and destination file is "BatchEntry.xlsx" I can easily copy range by below code and successfully paste It to other...
  7. R

    Finding last used cell in a Range and ADD form values into the Worksheet

    Hello guys, Need some help here. I have a form and I need my code to update the form values into a specific range (M55:Q65) in my worksheet. <tbody> Rno M N O P 52 End of Booking Data 53 54 55 eDM Name Tot sends Uniq OR Uniq CTR 56 Need the data to updated 4rm this line...
  8. D

    Persisting Range Name Addresses to Avoid #REF!

    There must be a technique of which i am ignorant to preserve the addresses or the formulas a range name refers to. A workbook has been returned from coworkers with unexplained #REF # errors in some range name addresses. So, how is this best fixed, that is, where and how should this...
  9. C

    using variables in formulas

    I am trying to make the row number dependent on the "temp" variable. But I keep getting an error. How should I write the code so the it will run? Thanks. Dim Temp As Integer Temp = ActiveSheet.Range("A1").End(xlDown).Row - 1 Range("I" & Temp + 1).Formula = "= sum(I3:I" & temp")"
  10. O

    Define a Range for every N rows and do something if the cell is selected

    Hi Community, I am using Excel 2010. I am trying to Hide/Unhide rows when specific cells are selected in a Worksheet. I have managed to do so with the following code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim UnHideRange As Range Dim HideRange As Range Set UnHideRange...
  11. N

    VBA to replace row numbers in a range with variables e.g Range("$A$RowFirst:$B$RowLast")

    Hi I have 35 rows and 2 columns A and B I want to filter by TopTen values in column B in rows 8 to 25 I have used the following to find the first and last row number (thanks to WBD) Dim rowNumberFirst As Double On Error Resume Next Err.Clear rowNumberFirst =...
  12. M

    How to get cell ranges based on value in the cells

    I'm trying to cut down the number of variables I have for solver. I have a large group of cells that could all be changed, but have identified the ones that will actually need changed with a 1 as the value. How do I get the cell range(s) of just the cells with 1 in them? Here is an example of...
  13. J

    VBA for formatting cells to be same as other cells

    Hi looking for some help with formatting cells. I currently have a spreadsheet that automatically populates depending on a value that is in a certain cell (target address). The information (target range) is pulled from another sheet ( Sheet 25) and then dropped into the named area I need...
  14. K

    vba need help referencing other cells

    Hello all.. learning this vba stuff one google search at a time is tough! I'm knee deep into a project that's well beyond my skill set.. but learning fast. The trouble I'm having is how to reference other cells in proper syntax.. and understanding how to specify a range... can someone get me...
  15. A

    using range address from input box

    in below vba code , i would like to replace all "B" in this code by range selecting or entering from application input box , kindly advise , Sub ahmed_code() Dim lr As Long, r As Long, x As Long Application.ScreenUpdating = False lr = Range("B" & Rows.Count).End(xlUp).Row For r =...
  16. B

    Problem with top property in a range variable

    I'm using shapes.addline method in a sub to draw a line, passing in 2 ranges rFrom and rThru, and then using .top and .left on the ranges to supply the four points for the line. Seems pretty straightforward, but the line sometimes comes out at an angle when I've passed in ranges on the same...
  17. V

    Selecting a range with blank cells

    Hi, I have a range with blank cells like this <tbody> Store name Item 1 Item 2 Item 3 Store A 2 3 4 Store B 1 Store C 1 1 Store D 1 1 </tbody> If blank cell is in between the range, code is not copying entire range. Sub conc() Dim myRow As Long Dim storename...
  18. L

    Resize range does not resize address

    I have just found something that seems strange to me. If I resize a range the address of that range does not get resized at the same time. This then prevents being able to step through the range using a For Each loop. Question: do I really have to SET the range so that I can loop through it...
  19. C

    cba copy and sort calling a sub

    Hi all, VBA newbie here. I am trying to copy a range (three columns, varying number of rows) in a second worksheet without blanks and sorting the data according to one of the columns (integers). I am trying to do this with 1 sub to define the ranges and a second to actually do the sorting...
  20. G

    Setting a range equal to a group of variables

    Not sure where my syntax is wrong here. I've been trying to concat a few variables into a range based off the location in a shared folder for my coworkers to use when updating a sheet. Hopefully someone can point me in the right direction. When I've checked each variable by using msgbox within...

Some videos you may like

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top