Posts by Jaclyn

Re: Automatically updating the Last Cell reference

Yes, I think I get you. I'll play some more.
Thanks Tony!

Re: Automatically updating the Last Cell reference

I would have to do that after every reference to LastCell I make won't I? They are 30 odd cells of data to transfer so is there any way of avoiding having to do it every time.
Thanks

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:

LastRow = Sheets("Data by Rows").Range("A65536").End(xlUp).Row
Cells(LastRow, 1).Select
LastColumn = ActiveCell.End(xlToRight).Column
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:

Cells(LastRow + 1, 2) = Application.VLookup("X Velocity", Range("VelocityParameters"), 2, False)
Range(LastCell).Offset(0, 1) = Application.VLookup("Y Velocity", Range("VelocityParameters"), 2, False)
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.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
        Select
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.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
        Select
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.

Re: Inserting Named Ranges into Formulas

Thanks!
Not sure how that actually works...but it works!
.

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

Sub AutoAverage()


For x = 0 To 20
For y = 0 To 171
FirstRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _
.Range("a5").Offset(12 * y, 12 * x)
LastRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _
.Range("a16").Offset(12 * y, 12 * x)
Workbooks("Mega Spectrums.xls").Sheets("NP - FT01").Range("a5").Offset(y, x).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(FirstRowRef:LastRowRef)"
Next y
Next x


End Sub

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.

Set PDD = Range(Range(PDDCornerCell), Range(PDDCornerCell).Offset(10 + NumberofUnits, UsedNumberofColumns - 1))
Set PDRD = Range(Range(PDRDCornerCell), Range(PDRDCornerCell).Offset(10 + NumberofUnits, UsedNumberofColumns - 1))
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.

Private Sub cbViewCalculatedData_Click()


Dim aNames As Object
Dim NumberofUnits As Integer
Dim x As Integer


If ActiveSheet.Name <> Me.Name Then Exit Sub        '' calling sheet must be the active sheet!


If Not WizardInProgress() Then


On Error GoTo cbVCD_NBG




Application.ScreenUpdating = False


For Each aNames In ActiveSheet.Names
    
    If (Mid(aNames.Name, 25, 4) = "_O_2") Then
        If cbViewCalculatedData.Value = True Then
            ' Check for active values
            NumberofUnits = Sheets("Unit_Flotation_Overview").Range("UnitList").Rows.Count
            For x = 1 To NumberofUnits
                If (Range(aNames.Name).Cells(x, 1).Interior.ColorIndex = 15 Or _
                    Range(aNames.Name).Cells(x, 1).Interior.ColorIndex = 37) Then
                    Range(aNames.Name).Columns.Hidden = False
                End If
            Next x
        Else
            Range(aNames.Name).Columns.Hidden = True
        End If
    End If
  
Next aNames


   
cbVCD_NBG:


Application.ScreenUpdating = True


End If


End Sub

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.

For x = 0 To NumberofSizes - 1
    Range(CellsCornerCell).Offset(1, 5 + (x * NumberofMinerals)) _
    .FormulaArray = "=index(Configuration!SizeNames, x+1, 1)"
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:

For x = 0 To 4
    Range(Range(CellsCornerCell).Offset(0, x), Range(CellsCornerCell).Offset(2, x)).Select
        Selection.HorizontalAlignment = xlCenterAcrossSelection
        Selection.BorderAround (xlContinuous)
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!