Posts by jweaver

    Re: Concatenate a counter to a variable in a loop

    Thats it - for the most part. Modifcations below did the trick. Thanks!

    For j = 1 To 4 
        BeforeCycle(j) = ActiveSheet.OLEObjects("BeforeCycle" & j).Object.value
        TempCycle(j) = ActiveSheet.OLEObjects("TempCycle" & j).Object.value
        TimeCycle(j) = ActiveSheet.OLEObjects("TimeCycle" & j).Object.value
        AfterCycle(j) = ActiveSheet.OLEObjects("AfterCycle" & j).Object.value
        DwellCycle(j) = ActiveSheet.OLEObjects("DwellCycle" & j).Object.value
      Next j

    I have a variable in a loop and want to concatenate a loop counter to it. For example instead of doing this:

    'value () is an array
     value(1) = Cycle1.Value  'Cycle1.Value is the value of a textbox control 
     value(2) = Cycle2.Value  'there are four on this worksheeet
     value(3) = Cycle3.Value
     Value(4) = Cycle4.Value

    I need something like this:

    For j = 1 to 4
        value(j)  =  ?   ' I don't know how to do this part          
     Next j

    It would be no problem if I could use and array for textbox controls, like
    Cycle(j).Value, but this can't be done can it?


    Re: FileSearch works in VBA 6.0 not 6.3

    Thanks for your response- The .LookIn property does not determine the path properly. So I used a routine that extracts the path from a string (path+filename) and used that- LookIn = path

    When doing this all works fine.

    thanks again- Jim

    Hi, The following works fine w/VBA6.0 but when running on another machine which has VBA 6.3 it doesn't. .FindFiles.Count returns 0. When I remove the path (c:\) from txtFileName, and use .LookIn, it works. Any idea what the problem is ( it may not really have anything to do with VBA 6.0 vs 6.3).

    With Application.FileSearch  
                    .filename = txtFileName & "_*.txt" ' txtFileName = "c:\Demo"
                a = .FoundFiles.Count 
    'rest of code not shown...

    Thanks, Jim

    I'm trying to plot two different data sets on different Y-axes. The x-axis is time, and when I pick the default 'Lines on 2 axes' chart, it automatically formats the x-axis as category instead of Value. Then I can't zoom in etc.. How can I force x-axis to be Value? Thanks, Jim

    Andy, I've always just deleted that line and never took the time to figure out why its there. Jim W.

    It turns out that whichever sheet the commandbutton is activated, it always uses that as its default sheet, even if within its procedure another workbook is activated. That workbook has to be respecified every time you want to do something like .sort. However this is not always the case, which is confusing. But I got it working. thanks, Jim W.

    I'm trying to sort rows of data in ascending order according referenced to the first column. A sample file, Data_ex.xls, is attached- this happens to be in order already, but an actual file will not be. I don't care about spaces etc..

    I am using a commandbutton from another workbook to sort Sheet1 of Data_ex.xls. The strange thing is, if I do it from a macro module in that other workbook, it works fine.

    I selected the whole sheet to sort because in general I don't know how many columns there will be.

    Set Tbl = Range("A1").CurrentRegion

    works then great. However i still get same error at:


    By the way, I'm using Excel 2000.

    I does not recognize


    in the .sort method. Is that why or is there something else i'm missing?

    MANY thanks for your help, Jim

    I hve a macro that works fine as a module, but when trying to get it to work through a command button it gives an error. The code is as follows:

    Private Sub CommandButton2_Click()
    'This code works fine if in a module. I cut/pasted the code here.

    ActiveCell.FormulaR1C1 = "2" 'Works so far, just testing
    Windows("Data.xls").Activate ' activate another workbook
    ' Application.Run Range("ScOnWindow") 'no need for this line-
    'why does macro record it?

    Cells.Select 'trying to select all cells, then sort
    ' this is where it gives an error

    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    The error message: Run-time error '1004'
    Application-defined or Object-defined error

    Any ideas?
    Thanks JW

    That doesnt seem to work by itself... Here's the code generated via record macro:

    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
    , DisplayAsIcon:=False, Left:=531, Top:=91.5, Width:=92.25, Height:= _

    Where can I put CommandButton1.Caption = "New Name" ?

    Here's the problem. When the following code is run, IsOpen() checks to see if a .xls workbook is open. BUT, the TextBox1.Value is only a Title, not the name of the .xls file. Therefore the routine always adds another workbook instead of using the same one.

    If IsOpen(TextBox1.Value) Then
    Set NewBook = Workbooks(TextBox1.Value)
    Set NewBook = Workbooks.Add
    NewBook.Title = TextBox1.Value
    End If

    Can IsOpen() be changed to check if the .xls file which was previously created, check Title of the file instead?

    When i say Title, it refers to that found under File/Properties menu for any workbook.

    Is this possible?

    Thanks, JW