Posts by skamat

    Re: Run Time Error 429


    I am having same version of Office, but windows version is different in both PC,


    The PC where I get error I have windows XP, and other PC where the file is working fine has Windows 7.


    This workbook was created in windows XP, but edited and imporved VBA in Windows 7 PC.


    All the references are proper.

    I am not sure if below question is allowed in this forum as there is no help required for VBA program.


    I am using Windows XP and Office 2007. Whenever I try to run a VBA Excel File I get a Run Time Error 429 , Active X component cant create object.


    The same file works perfectly fine in another PC, I have even tried to re-install office 2007 but same problem still comes.

    How do I remove duplicate entries from 4 columns. I have made the VBA code but it does not work and removes duplicates only from first 2 columns


    Code
    Range(Cells(1, 1), Cells(1, 1).End(xlDown).Offset(0, 1)).Select
        Application.DisplayAlerts = False
        Selection.RemoveDuplicates Columns:=2, Header:=xlNo
        Application.DisplayAlerts = True

    I hope the title is proper as I do not know the right subject for my query.


    One of my employee has made a data as shown in sheet1, (although the data is very long this one is a brief one)



    Brief description of data is as under


    Colomn A has the serial number mentioned 1, and for column F there are types mentioned from a to i, and column B to E has just one entry in row 8. This means all the types mentioned in column F from a to i have the same values that are given in column B to E.
    Then there is a space in row 17.


    Next


    Colomn A has the serial number mentioned 2, and for column F there are types mentioned from a to b, and column B to E has just one entry in row 18. This means all the types mentioned in column F from a to b have the same values that are given in column B to E.
    Then there is a space in row 20.


    Same for serial numbers 3, 4 , 5 , 6


    What I need to change is


    For Serial number 1 and data for B8 : E8 should be copied till row 16
    For Serial number 2 and data for B18 : E18 should be copied till row 19


    same for serial number 3 , 4, 5, 6.


    The blank rows in 17, 20, 23, 27, 31 should be removed.


    The list is very long and as the employee who has made this is on leave it becomes very tedious to manually rectify the data.
    Is there any VBA code for this.


    The data would look somewhat as in sheet2


    The serial numbers are just mentioned for easy reference and is not present in actual sheet.

    I have created a message box as below


    Code
    MsgBox("Continue further", vbYesNo + vbMsgBoxHelpButton)


    Here we get 3 buttons, Yes, No and Help.


    Now actually I do not want the help button, but the button mentioning "Information"
    So when you click on information you get the message showing what this action is going to do if you press Yes Button.
    I am not aware how the message also pops up if you select Information button.


    But when I write vbinformation, the Help button goes away.

    I want to find the largest and smallest value for a number
    for Eg.


    68734 the largest value is 87643 and the smallest value is 34678


    39823 the largest value is 98332 and the smallest value is 23389


    43089 the largest value is 98430 and the smallest value is 3489

    Re: highlight blank cells


    The code worke perfectly well, with only one problem.


    As you know when we type any text and press enter the cursor moves one step ahead.


    In this also after I press enter the cursor moves one step ahead with green box still highlighted. Now when I again take the cursor back to that box the green box becomes white.


    Is there anyway by which as soon as I press enter the green box becomes white.

    How di I highlight blank cells in a range


    This has been asked before wherein all blank cells in a range color changed to green and solution was given as below

    Code
    Dim rng as Range
    On error resume next
    set rng = range(range("B7"),   range("G" & Range("A"&Rows.count).end(xlup).row)).specialcells(xlcelltypeblanks)
    on error goto 0
    if not rng is nothing then
         
        rng.Interior.ColorIndex = 4
    end if


    what i want is that the moment I type in the green cell the color should go bacl to normal.


    sample sheet attached

    I have to check the work of my fellow associates everytime.


    Please see the attached sheet and is there any way by which the VBA calculates the total for columns H and K and if there is a mistake it corrects the mistake.


    The list is endless and keeps on growing.


    Mistakes should be pointed in red, but the red color should not be saved when saving the excel sheet, but the mistakes corrected should be saved.