Posts by matt_boy

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Locking select worksheets


    Format=>Cells=>Protection=>Locked


    You could just make all cells on the worksheet locked apart from the ones you require to change, then protect the worksheet.

    Re: Run Time 1004 Error


    Hi


    could you not do this i.e make the sheet visible whilst the code is running then unhide again? If you turn off screen updating then it still cannot be seen whilst the sode is running:

    Code
    Application.ScreenUpdating = False
    Worksheets("YTDdb").Visible = xlSheetVisible
    Worksheets("YTDdb").Activate
           'your code i.e.set etc etc
    
    
    Worksheets("YTDdb").Visible = xlSheetHidden
    
    
    Application.ScreenUpdating = True


    Hope that helps

    Re: Doiing a loop based on the number of non-empty rows


    Would it not be better to find the last row that has something entered into it then loop through all the rows above? You can then loop through all these rows and if nothing is entered in a particular row, dont copy that row etc. If this is the case then you require something like this:



    If you require any help on this just let me know.


    Hope that helps

    Re: If statements


    Copy this statement and paste into C1


    =IF(AND(A1=0,B1 = 0),0,IF(AND(A1=1,B1 = 1),1,IF(AND(A1=1,B1 = 0),0,IF(AND(A1=0,B1 = 1),0,""))))


    Hope this helps

    Re: Toggle Format


    This works over a selection of cells that contain etiher type of formatting i.e if the selection contains blue cells they will turn white and white cells will turn blue:



    Hope this helps

    Re: Toggle Format


    It seems to work for me, but only when the selection contains only one style of formatting, if your selection contains both white and blue it doesnt work, was this what you were trying to achieve?

    Re: Find and replace query


    No Will not at all, I appreciate the help, I myself am fairly new to VBA. For a relative novice like myself, it is just nice to have code that works! I appreciate the help in understanding good coding techniques. I am teaching myself at the moment pretty much from scrath using this website, so the only way I will learn is if you guys explain why certain methods are used as opposed to others. It is very nice to have the most efficient code possible however being new it is near on impossible to know what is best.

    Re: Find and replace query


    Ammended above code slightly in order to get what u stated:



    Hope this helps

    Re: Find and replace query


    IS this any good?



    Hope this helps


    Matt

    Re: Compare two colums to eachother


    Hi again


    removing the file extensions worked and the code will run now!! Happy days


    Thanks for the help batman however it would be mostly appreciated if you could still post the example using conditional formatting as I may decide to use this instead and it will help my knoledge of excel. I can get it to work on the same sheet however referencing another sheet seems to cause problems.


    All your help is greatly appreciated


    Matt


    Just got your example as i posted this will let you know if i get any other problems


    Once again thanks v much

    Re: Compare two colums to eachother


    Hi Batman


    I tried using the code you said but I still coundn't get it to work :?


    So then I tried the Conditional Formatting and was getting that to work however when I tried to use other worksheets (like i was doing in the coded version) excel told me I may not use references from other worksheets or workbooks for conditional formatting which blows that option out of the water (which is kinda unforunate because a lot easier), even though it allows you to input reference to other workheets. So now Im back to my original problem. Is there any way you could post what you said you got to work?


    This is what I did with your code, sorry if you think im being stupid!!



    Just gettin the same error as before


    Thanks v much


    Matt

    Re: Compare two colums to eachother


    Help needed again!!!!


    The colums I'm using are on 2 different sheets and for some reason I can't seem to get this to work. Sorry if I'm being stupid, I am trying!! Here is what I've tried to do on top of Bills code.


    It keeps coming up with runtime error 1004
    Unable to set the colourindex property of the interior class


    Please could someone let me know what I'm doing wrong and explain it to me, it's doing my head in :yikes:


    Many many thanks


    Matt


    By the way if anyone could let me know how to improve on my code, I know it properly isn't written in the best way, then please let me know. I know there are meant to be certain methods which are deemed as good practice however i don't know what these are (I'm only a beginner to VB). Thanks

    Re: Compare two colums to eachother


    Hi Gents


    Thanks ever so much for the suggestions!! Tried both methods and couldnt get the conditional formatting to work (not too sure what i was doing wrong but I dont doubt it was me getting it wrong!) however the code worked a treat with no changes. Apologies if my question was none too clear originally. Really appreciate the help though.


    Thanks Bill and Batman!!!!!


    Matt

    Hi all again


    Quick one (I hope).... If i have two columns, what would be the best way to compare all the values in these two columns and if any values exist in both columns, albeit anywhere in these columns, then the cells in both columns are changed to green, whereas any cells that dont exist in both columns are changes red? Any suggestions? I can't think how to go about this.


    Many Thanks in advance


    Matt