Posts by Jaclyn

    Hi,


    I think this is a fairly straight forward problem but I can't figure it out. I am using VBA to copy data, cell-by-cell from one sheet to another. One of the sheets continuously updates while the other sheet records all of the data. I have been figuring out how to find the last cell so that vba will automatically find where it has to put the data.


    This is my code for finding the last cell:

    Code
    1. LastRow = Sheets("Data by Rows").Range("A65536").End(xlUp).Row
    2. Cells(LastRow, 1).Select
    3. LastColumn = ActiveCell.End(xlToRight).Column
    4. LastCell = Cells(LastRow, LastColumn).Address


    This is a bit roundabout, but each set of data is copied on a different row, but as I said its coping cell-by-cell, across the columns in a row, so this is the easiest way I figured to do it.


    My problem is that I can't figure out how to continuously update LastCell after it copies each cell. This is an example of the copying code:


    Code
    1. Cells(LastRow + 1, 2) = Application.VLookup("X Velocity", Range("VelocityParameters"), 2, False)
    2. Range(LastCell).Offset(0, 1) = Application.VLookup("Y Velocity", Range("VelocityParameters"), 2, False)
    3. Range(LastCell).Offset(0, 1) = Application.VLookup("Angle", Range("VelocityParameters"), 2, False)


    NB. the first line finds the new row in which to place the new data set


    Ideally, I'd like LastCell to automatically update so I don't have to kept telling it where the data needs to go.


    Any advice would be much appreciated.
    Thanks in advance.

    Hello,


    I have been trying to make a combobox for which the list is a named range. However, this range needs to be transposed. ListFillRange doesn't seem to let me transpose the list first. I've tried transposing the list somewhere else first and then adding it, but it seems to want a range as opposed to a reference to a range. I'm so confused now. This is the basic code that I wish would work.


    Code
    1. ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    2. DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
    3. Select
    4. Selection.ListFillRange = "=transpose(Stream_Data!StreamList)"


    While I'm at it, could someone explain to be how to refer to a combobox. ie. when you create it, you don't name it so how can you refer to it. That's why I have used the selection tool above to add the list.


    Any guidance would be appreciated!


    Ta,
    Jaclyn.

    Hello,


    I have been trying to make a combobox for which the list is a named range. However, this range needs to be transposed. ListFillRange doesn't seem to let me transpose the list first. I've tried transposing the list somewhere else first and then adding it, but it seems to want a range as opposed to a reference to a range. I'm so confused now. This is the basic code that I wish would work.


    Code
    1. ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    2. DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
    3. Select
    4. Selection.ListFillRange = "=transpose(Stream_Data!StreamList)"


    While I'm at it, could someone explain to be how to refer to a combobox. ie. when you create it, you don't name it so how can you refer to it. That's why I have used the selection tool above to add the list.


    Any guidance would be appreciated!


    Ta,
    Jaclyn.

    Hi,


    I am wanting to build a macro to calculate the average of a range of cells. I have about 2000 lines of data, and I want to average the first 12 cells (then paste the answer somewhere else), then average the next 12 cells, and so on. Using a loop to do this is simple enough. My problem is that I can't insert variables into the average formula as the cells to be averaged



    Thank you in advance
    Jac

    Hi,


    I am trying to copy and paste a range (a table) but the range is defined using a number of variables...as follows.


    Code
    1. Set PDD = Range(Range(PDDCornerCell), Range(PDDCornerCell).Offset(10 + NumberofUnits, UsedNumberofColumns - 1))
    2. Set PDRD = Range(Range(PDRDCornerCell), Range(PDRDCornerCell).Offset(10 + NumberofUnits, UsedNumberofColumns - 1))
    3. PDD.Copy Destination:=PDRD


    The number of rows and columns in the table may vary. This code will not work due to the range being an array (?). I have tried defining the range as something else first (.value=A), but that doesn't seem to work either. I thought if vba could tell me what the cell references should be based on the used number of rows and columns, I could then use these references in the copy/paste code. But I'm not sure how to do this.


    Any help will be much appreciated.


    Ta,
    Jaclyn.

    Hi,


    I have two checkboxes in a worksheet which when ticked, remove certain columns in a table in the same worksheet...let me rephrase...they are supposed to do this. I am trying to use some code that I didn't write (shown below) but to be honest have no idea how it works. The idea is that columns are removed based on the colour of particular cells in the columns.


    Could someone explain to me the idea behind doing this. What is the 'object' and how does this work?


    Any help is appreciated...I'm lost.


    Many thanks,
    Jaclyn.


    Hi,


    I have a For-Next loop which contains an index function. For the 'row' variable, I would like to use 'x+1', but it doesn't seem to like this. The index function is taking a name from the 'SizeNames' table and putting it in another table. I need to use the loop because the 'number of sizes' can vary and both the position of the SizeNames table and the destination table may vary.


    Code
    1. For x = 0 To NumberofSizes - 1
    2. Range(CellsCornerCell).Offset(1, 5 + (x * NumberofMinerals)) _
    3. .FormulaArray = "=index(Configuration!SizeNames, x+1, 1)"
    4. Next x


    Many thanks.

    Re: Auto-Create Checkboxes w/ VBA


    Thanks for that. I used the first lot of code. Although, I can't figure out how to change the caption name, font, things like that. I thought you would just add it on to the kend of that line.


    Cheers

    Hi,


    Apologies if this doesn't make sense...I don't entirely know what I'm talking about.


    I have written some code which, when run, creates a worksheet. It's pretty much a table. When the sheet is created, I would like two checkboxes to also be created beside the table. These will be used to expose/hide certain parts of the table (but I'll worry about that part later). I've tried different code I've found but nothing works and I think it's because it is for adding a checkbox to a form and not directly into Excel. I've been trying to use something along these lines:


    Set myLabel = frmMyForm.Controls.Add("Forms.Label.1", "lblPrompt")


    But I don't exactly know what this does!


    Also, would I embed this code along with my existing code or would it be a separate sub.


    Thanks for any help!

    Re: VBA Loop Ranges


    Ok, so pretty much what I want is a loop which runs 'for x = 0 To 11', but I don't want it to apply for '5 To 7'. What is inside the loop is the same (with only one variable). Is that a better explanation?

    Hi,


    I've built a simple loop as follows:


    Code
    1. For x = 0 To 4
    2. Range(Range(CellsCornerCell).Offset(0, x), Range(CellsCornerCell).Offset(2, x)).Select
    3. Selection.HorizontalAlignment = xlCenterAcrossSelection
    4. Selection.BorderAround (xlContinuous)
    5. Next x


    I also had another loop which is exactly the same but the range was x = 8 To 11. How can I join the two loops together? I thought it would be something simple like For x = 0 To 4 & 8 To 11, but nothing like that works.


    Cheers!