variable

  1. E

    Title a Sub Routine with a variable.

    I have over a dozen checkboxes. They are titled checkbox1, checkbox2, checkbox3... etc. They each have a sub routine that tells them what to do when click. These subroutines are identical except for the number that indexes each checkbox. While this was easy to create using copy/paste. It is...
  2. H

    Variable workbook declaration

    Hello I have a workbook that contains data and a VBA to do some tidying up and formatting. I need to be able to open up 2 other workbooks to do a vlookup on, the problem I have is the 2 workbooks change name everyday. How do I declare these other 2 in my main workbook. Any suggestions would be...
  3. T

    For Each Loop

    Following from this thread: https://www.mrexcel.com/forum/excel-questions/1114405-collections.html I was told this piece of information: Syntax ... For Each element In group ... element ... For collections, element can only be a Variant variable, a generic object variable, or any...
  4. V

    Declare a variable to a cell

    Hi everyone! I've been stuck with declaring a variable to a cell value and need some help. Situation: I have a code that filters a table and transfers the filtered data to another sheet. The filter could be the date value but I can't set it properly. The result is always Error 1004. I've tried...
  5. I

    Missing values per case

    Hello! I am new to excel and have learned a lot from these messages boards and hope to keep the learning momentum going. I have a large dataset with over 25k rows and hundreds of columns. I would like to delete rows (participants) who have over 50% missing values per variables. Each column...
  6. 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...
  7. B

    Looking up data in other worbooks

    I have a table where I have to draw data from a number of workbooks While the table is across multiple columns, it is between rows 2 and 201 So in cell AB2 I have this formula =VLOOKUP($A3,[Trap12.xlsm]Squadding!$K$3:$P$202,2,FALSE) This is the formula I tried to pick up the variable which is...
  8. K

    VBA: save document to path from variable

    Hello all, In have a file path saved as the variable "File_Path". Now I want to save a workbook to that path. I'm using the following code: WB2.SaveAs Filename:= _ File_Path _ , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False It is not recognising "File_Path" as...
  9. R

    Declaring variable types within an array

    I have an array with a mix of data types (strings, dates, integers, etc...). When I use the watch window for debugging, some date values show up as dates, but others as a real number (which isn't very helpful). Not sure how that happens, but that question is for another time. In the...
  10. glfiedler

    VBA intellisense data

    I dimensioned a variable as Dim rowS without thinking. I have been coding for decades so I know using "rowS" as a variable is, of course, a bad idea even thought the vba editor is smart enough to take context into account and keeps running smoothly. As soon as I realized my mistake I deleted...
  11. Johnny Thunder

    Formula Help - SumProduct with multiple Conditions that include a wildcard

    Hello All, I am hoping this is an easy one, I have a sumproduct formula that looks at multiple conditions and it works great but the business just threw in a new variable and I was hoping it will be a quick modification to the formula to get it to work. Here is the formula...
  12. C

    VBA To find match of cell value and copy adjacent cell when match found

    Looking for vba code to see if data from 2 different cells on 2 different sheets if it matches then it copies the cell to the right on sheet 2 and pastes it to the cell on the right on sheet 1. All the data in sheet 1 column M is present on sheet 2 column A. So when it finds a match in column M...
  13. S

    Adding worksheet to a range variable

    Hi I tried to add the worksheet information to a range variable which was passed from a main routine. "Received_range" is the range variable received and all of the three lines below has resulted in error. a. Set localrange = Sheets("Sheet1").received_range b. Set localrange =...
  14. C

    Problem with VBA Loop

    Hi, I want to loop through rows in my worksheet and compare the cell values in particular columns with other rows defining a variable as True or False (my segment_trigger variable below) if the values are the same or different. I have written the following code but its giving me "Compile...
  15. T

    Object Variable or With block variable not set error

    Hello, Using stream.WhiteLine in a VBA script is giving me the Object variable or With block variable not set error msgbox. Clicking on the Debug button brings me to the line that is written stream.WriteLine " " This is happening at the end of the script. But, I've used the stream.WriteLine...
  16. F

    VBA: Insert formula with variable number of sheets

    Not sure how to get the right syntax here to represent a variable number of sheets. Sub TryThis1() Range("G2").Formula = "=AVERAGE('1:4'!E2)" End Sub The 4 in the formula is the variable part. Whatever number is in Sheets("CtrlPnl").Range("H12") is the total number of sheets. In this...
  17. spencer_time

    Use variable within excel function

    Hello, I have some test code that I can't get to work correctly. I am trying to figure out if it's possible to use a variable within an excel function, and if so, the proper way to do that. The following is what I currently have, it is not working: Sub embedVarFunc() Dim rng As Range...
  18. L

    Excel: Dynamic plot with specific variable on x-axis

    I have a variable Y Y = RANDBETWEEN(2,15) And a variable X X = NOW() I am using the following VBA code to refresh the date; to simulate a real time data flow: Sub Calculate_range() Range("A1:A7").Calculate Application.OnTime DateAdd("s", 2, Now), "Calculate_range"...
  19. MannStewart

    Need macro for OFFSET whcih derives ROWOFFSET from a variable in another cell

    Hi I have a worksheet with a working range of B5:B5000, G2:G5000, H2, V5:V5000. where, V5:V5000 is just =ROW($B5)...downwards, $G$2 contains a function formula that returns a daily overall air-pollution level in decimal value that I make it recalculate each time the worksheet is...
  20. spencer_time

    Active worksheet as variable broken

    Hello guys, I need help again. I have a snippit that works when the sheet is defined (sheet3) but doesn't work when I try to make a variable for the active sheet (ws). I get an error that says "object doesn't support this property or method" and when I press debug it highlights my variable ws...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top