Posts by gollem


    why let the user give a name of a file? You can get the problem that when there are duplicates on the system, the program doesn't know which file to use. Another aspects is that it could take a while, so for a little program performance lowers.

    Searching a file is no problem I guess, but I should use the search in a directory or let the user select the specific file.

    What do you mean by giving in a range? For example C5 => gives the data of cell C5?

    The code for selecting a file:

    Dim strFile         As String
    strFile = Application.GetOpenFilename

    Code for opening a file:

    Code strFile

    Code to get data from workbook:

    activesheet.range("C5").value  'gives value of cell "C5"

    Hope this gives some ideas.



    all the things you mentioned can be done. But you have to be more specific and perhaps you should ask step by step the things you want to do.

    You want to search the system for a file? Why not let the user select the file? Otherwise you could get problems with duplicate files. What data would you like to find in that file? Where should the data filled in?

    Do you want that the program starts when pressing a button?



    Hi, I'm glad it helped.

    About your question I 'm sure if I understand:

    Do you want that for example:

    sheet1 => data1
    sheet2 => data2
    sheet3=> data2
    sheet4=> nothing
    sheet5=> nothing

    the program loads the data until sheet4 and when it notices that there is no data, the program stops looping, loading data from the other sheets?

    If this is the case try to add the if-structure below:

    After exiting the loop, the program will start(with the next loop) writing the data.



    I've made one mistake already. Sorry for that: I've declared dbTable as integer => it should be a double.

    If you are certain that in every A5 cell is a number, you could try this first.

    The changing of sheetnames doesn't matter except for your last sheet(in mine example the "test" sheet).

    I've adtapted the example for the string version, I will also give a short explanation of the program:

    Dim intCounter              As Integer
        Dim intIndex                As Integer
        Dim strTable(100)           As String

    intCounter is a counter that we use to loop every worksheet, so instead of using a name I use a number(index) to select a sheet. Sheets(1).select, sheets(2).select, ....

    intindex is used for loading the table(first entrance=0)

    strTable(100) is an array of strings in this case who can have max 101 items(0-100) => in this array we load every A5 value of every sheet, example:
    sheet(1) strtable(0)=5
    sheet(2) strtable(1)=2
    sheet(3) strtable(2)=7

    intCounter = 1
        intIndex = 0

    First sheet and first entrance of the table

    Do While intCounter < ActiveWorkbook.Sheets.Count
            'Select next sheet
            'Load value in table (from every sheet)
            strTable(intIndex) = ActiveSheet.Range("A5").Value
            intIndex = intIndex + 1
            intCounter = intCounter + 1

    Looping the sheets and loading the data of every sheet(except last sheet)=> automatically

        'Start filling data on row 7
        intCounter = 7
        intIndex = 0
        Do While strTable(intIndex) <> ""
            ActiveSheet.Cells(intCounter, 1).Value = strTable(intIndex)
            '1 3 5 7 ....
            intIndex = intIndex + 2
            intCounter = intCounter + 1

    Selecting the "test" sheet => has to be your last sheet where you want to fill in the data. The counter is here used to present the row here you want to put the data. The intindex is the entrance of the table.

    So if you initialise the index to 0 (you start with the value of sheet1), +2 after filling in means that you get strtable(2) => means values of sheet3(sheet2 is skipped). If you want to start with the value of sheet2 and then sheet4, .... you should initialize the index to 1.

    I hopes this clears things up for you.



    check the code and the attachement. The attachement has example solution, you should be able to adapt the code for your needs.

    The example uses an array of numbers, if the values are text, you have to adapt the table in a string.


    Ok, didn't read your question correctly.

    This should do the job. I've adapted the code, fixed a little mistake and simplified a little bit.

    Hope this works for you.



    you should sort the data on that column and then use a loop to filter the doubles.(see attachement or code below)

    Hope this helps.


    Hi Suju,

    see the attachement.

    Option Explicit
    Private Sub cmdClickForStrategy_Click()
        Select Case True
            Case optSAVolatile.Value And optPPUL.Value And optSARPLim And optSADet And optLow.Value
                MsgBox "yOUR STRATEGY IS: Long Straddle or Long Synthetic Straddle"
        End Select
    End Sub



    this is indead possible. Just use some counters do to this, something like this for example:



    you can use ADO to do this. First select the Microsoft activeX Data objects library(2.1) for example(Tools -references).


    You should be able to adapt this for your needs (add a loop to use all data)



    try to use a loop function, something like this:

    'Do until row 2 of book B is "" (assuming when you start macro you are in workbook B)

    Do While ActiveSheet.Cells(2, 1).Value = ""
    'print the stuff
    'Clear row

    'Copy row

    'Delete row

    (Just add a check if a cell from row2 is empty=> no more records)