Posts by jweaver

  • Concatenate a counter to a variable in a loop

    Re: Concatenate a counter to a variable in a loop


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


    Code
    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

  • Concatenate a counter to a variable in a loop

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


    Code
    '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:

    Code
    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?

    Thanks!
    Jim

  • FileSearch works in VBA 6.0 not 6.3

    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

  • FileSearch works in VBA 6.0 not 6.3

    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).



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



    Thanks, Jim

  • lines on 2 axes

    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

  • Macro works in module, not in commandbutton

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


  • Macro works in module, not in commandbutton

    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.

  • Macro works in module, not in commandbutton

    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.
    If

    Code
    Set Tbl = Range("A1").CurrentRegion

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


    Code
    Range("A1").Select


    By the way, I'm using Excel 2000.


    I does not recognize

    Code
    DataOption1:=xlSortNormal


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


    MANY thanks for your help, Jim

  • Macro works in module, not in commandbutton

    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.


    Range("B12").Select
    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

  • Changing caption of command button

    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:= _
    24).Select


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

  • Macro to find number used in TextBox

    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)
    Else
    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