Posts by Mahesh1984s

    I have the word document which contain bullet point and multilevel list against bullet point. I am looking to perform following steps through vba. Please assist on following code.

    Steps:

    1. Loop through each bullet point

    2. Search string Supply Place and Payment notification and Notification Address

    3. If bullet point have multi list (as 3rd point mentioned below) search amount, if amount found show msgbox

    Sample Word Document text

    • Supply Place notification Address 1

    • Payment notification Address 2

    • Payment notification Address 3:

    (i) Place of Payment: India

    (ii) Payment amount INR 10,000,000 only,

    • Notification Address 4

    (i) Place of Payment: USA


    I want to restrict user input entry on worksheet cell, not mentioned in array find below mentioned code and example. this code is not working don't know the reason.
    i dont want to use data validation on worksheet for this. i will appreciate any help on this.



    e.g if user enter 4 in range A2 it will not allow and show error alert, user can enter only 1,2,3 values on worksheet.


    Re: worksheet data on userform_compile error


    I found the reason for this error, find below mentioned steps, i was not aware of this found information on below url. hope someone find this useful.



    On Excel - Developer Tab - Insert - Active X controls - More controls - Microsoft Office Spreadsheet 11.0
    On MS Visual Basic window- Click on toolbox - Right click on control tab - Additional controls - from available controls select Microsoft Office Spreadsheet 11.0



    http://www.engram9.info/excel-…-spreadsheet-control.html

    Re: worksheet data on userform_compile error


    Thanks for the reply, find attached sample workbook
    I think even after installing OWC11.dll, Microsoft Office Spreadsheet 11.0 Control missing in excel 2007.
    this dll file install "microsoft office web components" in VBA project references (MS office 12.0 Object library)

    Files

    • Sample_UF.xls

      (32.77 kB, downloaded 37 times, last: )

    i want to display worksheet data on userform and print it.
    i am trying below mentioned code but getting error as "compile error: method or data member not found"
    i will appreciate any help on this



    using excel 2007, i have installed OWC11.exe from the microsoft website,Microsoft Office Spreadsheet 11.0 Control
    Tools-references-microsoft office web components 11.0 [added]



    I have two workbooks A & B. data in both workbook is related. both are in same folder. i want when i open workbook A it will show msgbox it is related with workbook B & vice verse. if i move A or B workbook; to another folder. (Assume its workbook A) along with A workbook ; another B workbook should be copy & vice verse



    or it will only allow to move or copy when i select both the workbooks.



    it seems difficult..i will appreciate any help on this.


    Posted in another forum as well
    www.mrexcel.com/forum/showthread.php?t=618295

    Re: Add sheet and change cell reference


    Thanks dbrown14 for your help & time.i found the soultion

    Code
    1. Sub test()Dim i As Long, x As Long, a As LongDim path1 As Stringpath1 = "=+'C:\Users\Desktop\F.Y 2010-11\[Book1.xls]Main'!"With ActiveSheetd = DateValue("01-" & .Name)x = 3a = x + 1Cells(6, 3).Formula = path1 & "C" & xFor i = a To 6ActiveSheet.Copy after:=Worksheets(Worksheets.Count)ActiveSheet.Name = Format(DateAdd("m", i, d), "mmm-yy")Cells(6, 3).Formula = path1 & "C" & iNext iEnd WithEnd Sub

    i have sheet1 which is link with other workbooks i want, when i add new sheet the cell address will increase by one
    if cell address is A2 then when i add new sheet(copy of sheet1) in same cell it will reflect A3
    i will appreciate any help on this, i need vb if possible provide formula also

    Sheet 1

    Code
    1. Cell("B6").value ='C:\Users\Desktop\F.Y 2010-11\[Book1.xls]Main'!$A$2
    2. Cell("B7").value='C:\Users\Desktop\F.Y 2010-11\[Book2.xls]Main'!$A$2
    3. Cell("B10").value='C:\Users\Desktop\F.Y 2010-11\[Book3.xls]Main'!$B$10
    4. Sheet 2
    5. Cell("B6").value ='C:\Users\Desktop\F.Y 2010-11\[Book1.xls]Main'!$A$3
    6. Cell("B7").value='C:\Users\Desktop\F.Y 2010-11\[Book2.xls]Main'!$A$3
    7. Cell("B10").value='C:\Users\Desktop\F.Y 2010-11\[Book3.xls]Main'!$B$11

    Re: delete column


    Hi Bill

    i have resolve my query for my attachment, find below code.
    but curious to know about my first posted code



    Never Give up

    Re: delete column


    Thanks 4 reply

    i have mention my query in attach sheet,
    purpose for keeping c as range because i want to delete columns with
    certain criteria.

    Files

    Hi
    below code not working as he suppose to...
    when i run macro it delete entire column against range("A1:A5")
    i want to delete column from 4 to 1 , Column B is my first column
    i will appreciate any help on this

    Code
    1. Sub test()
    2. Dim rng As Range
    3. Dim c As Range
    4. Set rng = Range("A1:A5")
    5. For Each c In rng
    6. For i = 4 To 1 Step -1
    7. c.Offset(0, i).EntireColumn.Delete
    8. Next i
    9. Next c
    10. End Sub

    Need help for macro

    i have below table,code required to check which is the first string within cell value appearing first in Table & then assing no of that string (i.e TEST) in output column, how can i use Instr function or any other way i can do it, i will appreciate any help on this
    TEST No
    ECG 1
    MER 2
    BPT 3
    CBC test 4
    CXR test 5

    Cell Values Output required
    ECG,MER,BPT,CBC test,CXR 1
    CBC test,MER,BPT 2
    ECG,BPT, 3
    for 1 output ECG is First string found within cell
    for 2 output MER is First string found

    Files

    • Sample data.xls

      (17.41 kB, downloaded 39 times, last: )

    Hi rbrhodes & PCI

    Thank you so much for reply. i will continue to get desire results. thank you so much for sharing
    valueable information with sample workbooks.
    i got some info on below mention link.
    http://http://www.rondebruin.nl/copy5_3.htm</a>

    Can i add this code in what i mention in my thread, it crate new workbook for each unique value in column.
    so is it possible to merge both code, its Autofilter unique value & create new workbook.

    Hi

    i will appreciate any help on below mention query.

    1.I have workbook consist of 4sheets
    2.I want to filter each cell value in column A from sheet1
    3.and same cell value will get Autofilter in sheet2, sheet3, sheet4 also
    i got below mention code on net but it autofilter only particular Criteria in all sheets
    i want to Autofilter each value from sheet1 same will get Autofilter on sheet2,sheet3 & sheet4
    & create newworkbook and paste value in respective sheets of new workbook for each value in sheet1