Posts by Binning

    Hi there,

    I'm trying to replicate a formula into a newly created column based on the number of entries in the previous column. The formula currently looks like this:

    However I keep returning the error "object doesn't support this property or method"

    Any suggestions?

    Re: Cut unnecessary data and paste in other tab

    I've solved this using filters but I'm not sure this is the most effective way of doing it.

    Hi there,

    I'm looking for a macro that searches column D for all values above 0, selects the entire rows, then moves the rows to the next available cell on a seperate sheet. The macro I'm currently using is:

    However this only copies the last row as oppposed to all of them. I would also like it to delete the rows on sheet "SOD_Data" once they have been copied across.

    Any ideas? I also need all the new data that's being copied across to follow the color index logic (i.e. if last range was white, this new range will be green and vice versa).

    Hi there,

    I'm trying to input a check into my macro. Basically the logic I'm trying to follow is:

    If Row 1 = Row 2 then delete row 2,
    ElseIf Row 1 <> Row 2 then Display MsgBox "Row 1 and Row 2 are different. Do you wish to continue?"
    If user selects yes, resume
    If user selects no, end sub and display MsgBox "Macro ended. Please correct changes manually"

    Can anyone help me if this isn't too much trouble?


    Re: Colouring recent data

    I've managed to figure this out on my own if anyone is interest. It was actually a lot more simple than I previously thought. This changes it to light green.

    Sub ChangeColour()
    If Selection.Cells(1, 1).offset(-1, 0).Interior.ColorIndex = 35 Then
        Selection.Interior.ColorIndex = 0
        Selection.Interior.ColorIndex = 35
    End If
    End Sub

    Hi there,

    I'm currently running a macro which takes data from one workbook containing information on a specific day and inputs it into a master document containing information for all days. Currently, when the information is added to the new file the standard procedure is to change the ColorIndex of the new range to a light green if the last entry's ColorIndex was white, and vice versa.

    Currently the only macro I can come up with changes the background colour to a random colour using the following:

    This is fine but starts to look very untidy and very unprofessional with the colours being all over the place and some darker than others, making the text hard to read. Basically I need something that checks the ColorIndex of the cell above. Cell above is currently defined as:

    Selection.Cells(1, 1).Offset(-1, 0).Select

    i.e. the cell above the top left cell in the current selection.

    If the ColorIndex is white, the newly pasted range will be changed to a light green. If the ColorIndex of the cell above is a light green, the newly pasted range will be changed to white.

    I hope this isn't too much trouble.


    Re: Unprotect sheet, multiple password combinations

    Hi Roy,

    I'm not sure what's happening but when I run the macro with your code nothing seems to be happening, however when I have ActiveSheet.Unprotect Password:-"aDMIN" (which is the password on this sheet currently), everything runs fine. Any suggestions? Also, I don't need to include 'On Error Resume Next' throughout the macro, it was only included for the errors associated with a wrong password entry.

    Thanks again

    Hi there,

    Currently looking for a macro which tries a combination of three different passwords to unlock a sheet. The password can either be 'Admin' 'ADMIN' or 'aDMIN'. If all of these fail then I would like a prompt box asking the user to input the password manually.

    Can anyone help me here?

    Currently just running 'On Error Resume Next', however this will resume the macro even if the next combination of passwords are incorrect.


    Re: Replicate Text Alphabetically

    I've figured out two long winded ways of doing it. The first way is using a custom list which doesn't exactly suit me.

    For anyone else who is interested, the second idea was ="XXXX"&CHAR(ROW(A1)+64)

    If anyone else has an easier way I'd be very happy to know.

    This should be an easy question.

    I know that when you replicate numbers down rows on excel it will ascend/descend depending on the pattern before. For instance, if I have "1" in A1 and "2" in A2, if I replicate it down to A4 it will go 1,2,3,4. However, I was wondering if this feature was possible with letters, such that I could have:


    All through the alphabet without having to insert these manually.

    Any suggestions? Right now when I replicate it down it will just copy the cell contents.

    Re: Loop copy and paste until final sheet


    Thanks for the response but I'm having trouble with the layout of the workbooks I'm working from. So far as I can see this is taking information from every worksheet in the workbook, however I only want to take information from a number of them. It's also proving difficult as there are a number of hidden sheets.

    I think going forward I'm just going to use the original macro, but thanks for the help!

    Re: Find on Workbook

    I've now noticed that when I unmerge the cells, it finds the correct result. However for the sake of going forward the cells will always be merged together. Any ideas?

    EDIT: Nevermind, I've found another unique identifier which is unmerged in the worksheet I require.

    Thanks guys, formulas now work!

    Re: Find on Workbook

    I don't know what's causing this issue, but when I change "IFDS" to another constant nothing happens. However, when I use excel's standard search function (CTRL + F), and run a search on the whole workbook, it returns the first value just fine.

    Is there any reason for this? Is it worth mentioning that the other word is part of a merged cell?

    Re: Find on Workbook

    Hi Chirayuw,

    This works however it lands on the last IFDS that it found. Is there any way to exit the sub after the first value has been returned?

    Hi there,

    I'm currently running this macro:

    I'm not entirely sure if this is correct, but what I'm looking to do is the following:

    1. On the currently selected sheet, copy all data into the "Pasted Data" sheet on "Test.xlsm"
    2. On the "Calculations sheet", copy all information across to the next available row on "Master Sheet"
    3. Select the original Workbook and move to the next tab
    4. Repeat until there are no more tabs remaining

    So far as I can tell steps 1-3 are working (however I'm not entirely confident with my code to move to the next sheet as I'm not sure it will end the sub on the last sheet). Basically I just need help looping this until the information on the final tab has been copied across.


    Hi there,

    I've had a browse around the forums and most of the code already posted for what I'm trying to do requires a message box to be filled in to query what the user is looking for. However, in this situation the word I'm looking for will be constant.

    All I need to be able to do is run a search on all sheets for the word "IFDS" and then for the macro to activate the first sheet with that word on it.

    Preferably I would like the macro to find the first value, select the entire sheet, copy the sheet to a new workbook, find the next value and repeat until all "IFDS" values have been found, however if this is too complex then the first requirement will be fine.