Posts by venkat1926

    Re: Function COLUMN INDIRECT in VBA


    first portion is a sheet formula
    later portions of codes of excel vba


    if you want to use vba


    suppose A1 has
    P4
    P is in caps


    try this macro


    Code
    1. Sub colnumber()
    2. Dim j As Range
    3. Set j = Range("D18")
    4. j.Clear
    5. j.Formula = "=code(left(a1,1))-64"
    6. MsgBox j
    7. End Sub



    you will get 16

    Re: Macro to save workbook based on cell data from specific worksheet


    see whether this macro works


    Code
    1. Sub workbooksave()
    2. Dim path As String, ddate As Date, nname As String
    3. path = "e:\excel\"
    4. ddate = Worksheets("Master worksheet").Range("A1")
    5. nname = Day(ddate) & Month(ddate) & Right(Year(ddate), 2)
    6. ActiveWorkbook.SaveAs Filename:=path & nname & ".xlsm"
    7. End Sub



    modify

    Re: How to compare 2 results among years and among different schools?


    that pivot table did not give correct resulte


    see attached file with macro in vbeditor. run the macro test and see whether you get what you want


    I am repeating the macro here also.


    Files

    • moreiravtr.xlsm

      (18.56 kB, downloaded 300 times, last: )

    Re: Vba insert values table


    presume the data is in two columns A and B from row 1 down


    15 15
    20 21
    18 24
    17 18


    try this macro and see column E


    Re: Seaching selected words using VBA


    I have attached the file. check whether macro "test_revised" works ok.
    just run "test_revised"
    it clears the data from col F onwards to UNDO the result of already run the macro and runs the macro. I think the problem may be my sheet starts from col C .does your real document start from A


    if it woks ok what is the difference in configuration of my sheet and your real document.

    Re: Seaching selected words using VBA


    Quote

    a word from column D can correspond with multiple terms (The word "niet" can be found in every cell in column B blad 2). So I need the corresponding results to apear in the same row starting with column F


    I have modified the macro and given the macro new name "test_revised"
    Note: your E6 in blad1 is blank. I have inserted x in that cell to avoid that blank



    " it also has to work with selected or idividual cells in column D, and not just the entire column."


    instead of looping through D1 to D6 you can remove that FOR -NEXT and use the cell address with a dot e.g. .range("D2"). try modifying the new macro for this and give a new name, save the file and test


    after running test_revised Blad1 will be like this



    1 lokaal A Spijbelaars
    2 donkere C Little Bit Love
    3 drugs F Little Bit Love The Drug Lord
    4 zin F The Drug Lord
    5 wereld A Little Bit Love The Drug Lord
    6 niet x Little Bit Love The Drug Lord Spijbelaars

    Re: Seaching selected words using VBA


    not clear what you mean by this
    "the titles from those stories"


    I think what you want is for e.g that if "lokkal" from column D of Blad1 is found in column B of Blad 2 you want corresponding term in column A of Blad to be found.
    I have given a macro and see column F of blad1



    you can create a command button and assign this macro for that button
    I am also attaching the file

    Re: Ranking based on an arbitrary order


    some apparent inconsistencies. 8 blocks of data in sheet 1. only 6 cutom lists in sheet 2


    for whatever is worth I have prepared a macro "test" I dont know whether the macro gets what you want see attached file.


    the macro is also repeated here


    Re: excel sorting by custom list


    thank you very much. that is what I actually did and used a macro to add a custom list and also sort according to custom list. some how the ascending order within custom list takes place.
    Then I manually sorted with custom list and then order remains same as custom list and not in ascending order,.
    that means that something is wrong in the macro., I shall check the macro and if the problem persists I shall revert to the newsgroup.
    thanks again

    the problem is more complicated but I am simplifying it so that I can understand



    my customlist is
    venkat
    john
    lyndon


    sorting should be done just as above and not


    venkat
    lyndon
    john
    (descending order)


    nor do i want
    john
    lyndon
    venkat
    (ascending order)


    is it possible?


    sorting arranges the custom list either in ascending or descending order


    of course I can have a helping column identifying by numbers e.g


    venkat 1
    john 2
    lyndlon 3


    But I want to avoid this.

    Re: Ranking based on an arbitrary order


    why dont you make arrays in fueil2 in each column as different custom lists and sort according to particular list


    you can see custom list
    in excel 2007
    click excel icon on the top left end
    click excel options
    popular(first item on the left)
    click edit custom list(6th item from the top)



    once this is added to custom list you can sort according to this custom list



    of course you can write a macro for adding to custom list


    once custom lilst is used you can delete them from custom list
    you can try on that line

    Re: (VBA) Compare data from two different workbook and highlight MATCHES


    sheets(1) means first sheet in the workbook
    sheets("1") means the sheet NAMED 1 in the workbook



    if you are not very familiar with macro try to have two sheets in one workbook,make that workbook active and write the macro


    later you can save each sheet in two workbooks


    it is better to start with easier configuration rather than complex

    Re: vba to select multiple sheets based on a condition


    this macro is not tested
    open a new workbook and call it "new". you can rename later
    activate the workbook which has the filtered data


    now try this macro (I have not tested)
    if you want you can modify the macro