range cell variables

  1. T

    Macro has hard code first cell, how to find next cell with data

    Hello, In the link below some code is hard code referring to specific cells. I thought the source was always going to be the same column, however I just ran it a few times and found a glitch. In this line of codews2.Range("A1").Value = ws1.Range("BL3").Value BL3 was always the cell or so I...
  2. M

    Userform_Initialize() Dim Range prevents userform from opening on other worksheets

    I'm creating a userform (SOP421) that will serve as a "quiz builder"; i.e. the label captions and option button captions are pulled from specific cells in Sheet3("TRAINING") so that the questions and available answers can be modified from within the worksheet. Initially, I got the form working...
  3. L

    copy past range using variable for start cell address and 2nd variable for end cell address

    Please help I have searched the web and tried every solution I have found, none worked. I am trying to copy a range of cells, say T2,AB5 to another sheet. Problem is I don't know where this well appear so have to set a variable. It may appear several times throughout the data sheet. each...
  4. T

    Range Conditional Calculation

    <tbody> A B C 1 Range No. of Deliveries Inventive 2 0-4 Zero 3 5-8 ₹ 5.00 4 9-14 ₹ 10.00 5 15-18 ₹ 15.00 6 18 Onwards ₹ 20.00 7 8 No. of Deliveries...
  5. D

    Macro to Copy Formulas to Bottom Alongside a Query Table

    I have an ODBC Query that pulls GL activity from our ERP system into Excel. The size of this data increases the number of rows every month. In columns to the right of this data, I copy down some vlookup and other formulas that I use to organize the raw data and do sumifs on on other pages to...
  6. 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...
  7. T

    Help with match function - defined match value as well as range

    Hi everyone, I am having trouble with a match function. I don't know what I am am doing wrong. If I use the match function and manually specify the matchval and matchrng I get the answer that I am looking for, yet when I use VBA I get an error (Runtime error 1004, application-defined or object...
  8. L

    Copy cells from 1 sheet to another in a loop using a different starting and ending position

    Hi could you please help me with this loop. I’m trying to copy all cells from my current sheet starting at “B9” down. To a New sheet Starting at cell “A34” I marked out the 2 areas where I’m stuck. Thanks Range("B9").Select ‘ starting point Dim x As Variant Dim Icount As...
  9. S

    Formula returning unexpected reference.

    I am trying to pull a list of equipment from a tasking list that reference an equipment code. On a seperate tab I have input a count in Cell F6 with: =COUNTIF('CP Board'!B1:B1999,"*PS") and starting in A2 I am trying to use : =IF(ROWS(A$2:A2)>F$1," ",INDEX('CP Board'!B$1:B$2000, SMALL(...
  10. R

    Selection -> Range -> Chart

    Hey guys, since I am a real loser at using range, I gotta ask you the following. I want to create a chart from an area of data. Please see the following code and tell me what I am doing wrong. Many thanks! ' variables Dim AAAA as Worksheet Dim BBBB as Range 'requested range...
  11. g_purve

    Need to control multiple plot axis range with a variable or a designated cell location

    Thanks in advance for taking the time to help, you guys never let me down. Little background on what I am doing and why its useful, if you don't care skip this paragraph. So I am a test engineer that tests small handheld engines and certifies them to EPA, CARB, EU, etc. I have...
  12. M

    VBA code that references a worksheet [given URL] , then copies and pastes desired range

    Hi everybody, First of all, thank you all for your previous collaboration. I am currently engaging more with Excel [2010] and VBA, and with the stumbles along the way, your answers to other people's questions have been great answers to my questions as well. Usually with the Record Macro feature...
  13. J

    Using VBA to filte where criteria is variable

    Hello again, I am attempting to filter a file using VBA and the criteria will vary each time the macro is run. Also once filtered I will need to select the filtered cells and delete the rows, of course the range will vary depending on the filter criteria. Is there a way to write code for this...
  14. Prevost

    Creating Named Ranges Using VBA - Run time error 1004

    Hi There. I am trying to create named ranges. All the range names are in the first row and below them is their respective range entries (the cell immediately below the name cell is intentionally left blank). I check the first row cells until I get to the end (where an empty cell is) to get the...
  15. T


    Can somebody please explain what this is doing is layman's terms? Cells(Rows.Count, PageUp.Column).End(xlUp)
  16. J

    Loop through multiple ranges

    First off, thanks for looking at this. I am having a problem trying to figure out ow to move to the next set of cells. I have a list of data on say Sheets("Sheet1") that I need, starting in BA8 that goes down 6 rows to column DW. I need to loop through each columns set of six cells, copy and...
  17. L

    VBA - Using Declared Range Variable in looping script across multiple sheets

    I am trying to adjust the code so that it cycles through the sheets using a range variable rather than hard coding Range ("O4"). However, when I run the code, it works on the first sheet and then gives me a run-time error '1004' when it gets to "MyRange.Select" - Any ideas what I am doing wrong...
  18. J

    Relative range using variable

    I am trying to get the following to work: (rowCount is my variable) Range("L" & rowCount:"R" & rowcount).Select but I am getting a Compile Error:Syntax Error. Can anyone help me and let me know where I am going wrong with this? Many thanks J
  19. D

    Set a range = to array in different but open workbook

    Hi guys I can't figure this one out. (I'm a novice) I really want to learn to use arrays to and from ranges because it will be much faster than the looping methods I currently use. I searched the forum but see nothing exactly like my problem. The code I pasted is part of one of my procedures and...

Some videos you may like

This Week's Hot Topics