Posts by Boo Trumble

  • Hiding Empty Rows Without Macro Button

    Re: Hiding Empty Rows Without Macro Button


    Blue,


    Sorry for the delay in replying.


    To answer your questions:


    1. The code can be autorun by inserting it into the Workbook_Open() event


    2. Re: the master file (Spreadsheet) being updated automatically when an officer enters data in a row that IS blank - technically this can be achieved but the actual process doesn't make sense - why would you want to look at the master spreadsheet when someone is in the middle of entering data - I suggest that this would be slightly risky?

  • Running macro when opening a spreadsheet

    Re: Running macro when opening a spreadsheet


    Open the VBE (Visual Basic Editor), select "This Workbook" from the list of Excel objects. In the editor itself choose "Workbook" and there are various options then available - one of these is open.


    What you will end up with is:


    Code
    Private Sub Workbook_Open()


**Your code goes here**


End Sub


    This should do what you are looking for...

  • running account total

    Re: running account total


    Macca,


    A few questions:


    1. Will there ever be any blank rows?
    2. Will there ever be an instance where there are incoming but no outgoings and vice versa?


    Need to ask to know what you are trying to do.


    It may be as simple as (assuming you are starting on row 3 i.e. H2 contains your brought forward total):


    Code
    =IF(AND(F3<>0,G3<>0),+F3-G3+H2,0)


    Or it may be slightly more complex if the answer to any of the above 2 questions is yes...

  • If Or Statements

    Re: If Or Statements


    Hi,


    Your formulas a bit on the long side but should still work. A couple of reasons why it currently doesn't work:


    1. You are including cells in columns A & B in your formula. These columns are used for an employee number and name and shouldn't be in a formula that sums the total number of hours worked for a week.


    E.g. From your example, the first condition checks to see if cell A8 > 8 (it is 545 in the example provided so the answer is true). You then go on to say that if A8 >8 then add 8 to the total. This is an employee number and nothing to do with hours worked so gives a skewed answer to your formula. Same thing applies to cell B8


    2. Your formula misses out column G (cell G8 in your example). This is the hours worked for Friday - it looks like you should be including this in the total unless Friday's are when you put your feet up...


    3. The formulas where you check to see if a cell contains text is actually correct. However, the way you've set out the formula in general means that a cell containing text will return 0.


    E.g. From your example, when looking at cell C8, the formula checks to see if the value is > 8 (H is deemed to be > 8 in this case!) so a value of 8 is returned. However, you then subtract 8 if the cell contains text (which it does) so you arrive back at the answer of 0.


    The reason you are arriving at a result of 24 in cell K8 is as follows:


    Cell A8 > 8 so add 8 to total
    Cell B8 > 8 but is also text so add 0 to total
    Cell C8 > 8 but is also text so add 0 to total
    Cell D8 >8 but is also text so add 0 to total
    Cell E8 = 8 so add 8 to total
    Cell F8 = 8 so add 8 to total
    Cell G8 is not in the formula so not in total


    Total = 24 (per cell K8)


    You can see from the above that the problem is nothing to do with Excel not reading the H as text.


    Okay, after all my rambling, the answer to your question is:


    =IF(ISTEXT(C8),8,IF(C8>8,8,C8))+IF(ISTEXT(D8),8,IF(D8>8,8,D8))+IF(ISTEXT(E8),8,IF(E8>8,8,E8))+IF(ISTEXT(F8),8,IF(F8>8,8,F8))+IF(ISTEXT(G8),8,IF(G8>8,8,G8))


    Should go in cell K8.


    B :) :)

  • Hiding Empty Rows Without Macro Button

    Re: Hiding Empty Rows Without Macro Button


    Blue,


    Have you thought of filtering your data range (Data > Filter > Autofilter) and then selecting (nonblanks) from the column B drop down arrow?


    If this is too simplistic, then use the following code:



    If it's the bit that you don't want to have a button on the worksheet that you are more concerned about...basically you need to have something that will kick off the code so you could assign a keyboard shortcut to your macro instead. There's quite a few ways you could initiate the running of the code (it just depends on your preference).


    Hope this helps


    B :roll: :roll:

  • Pulling data from one worksheet to another worksheet

    Re: Pulling data from one worksheet to another worksheet


    I don't think you need to use any code to achieve this.


    If I read the problem correctly, you are trying to pull information from one worksheet into another worksheet using a unique reference (SKU#) as the identifier. Once you've found the reference number in a list of data, you want to pull information from that row from column X into your other worksheet.


    A couple of things:


    1. For the solution I've come up with to work, the data should be in one worksheet (with a tab for each list of data e.g. 1 tab for the Inventory DB information and another for the Web information)


    2. I've assumed that all of the SKU# reference numbers are unique


    I've attached a small example file to show how this might work...


    Hope this helps,


    B :roll: :roll:

  • cells protect

    Re: cells protect


    2 parts to this one:


    Part 1
    Format > Cells > Protection
    Untick the "Locked" checkbox for any cells you want a user to be able to change/edit


    Part 2
    Tools > Protection > Protect sheet
    Now the users will only be able to edit/change cells that you have unlocked in part one


    In part one, remember to unlock all of the cells that a user may want to change/update/edit because once the protection is put on in part 2, any locked cells will be just that i.e. locked so that they cannot be modified.


    Hope this helps,


    Boo :)

  • Transfering Specific cells in multiple columns

    Re: Transfering Specific cells in multiple columns


    Monte,


    I've updated your spreadsheet to do what you want (hopefully) and attached it.


    Unfortunately the sheet itself is 45KB in size so I have to include the code as part of this message rather than within the sheet - you should copy/paste the following code in the VBE:


    For the "Clear data" button:


    Code
    Private Sub Cmd_Btn_1_Click()
Application.ScreenUpdating = False
Range("N8:N27").ClearContents ' clears the contents of the cells N8 to N27
Application.ScreenUpdating = True
End Sub


    For the "Update sheet" button:


    It may not be the most efficient code but it will do what you are looking for.


    Note that I have also input formulas into your original sheet in cells K8:M27.


    Hope this helps,


    Boo :)

  • Missing object / reference

    Re: Missing object / reference


    Just a quick note to say thanks to RoyUK & Norie :ole: for their tips/hints on this one - I checked at work today and it was one of the references that I had ticked that was missing (the word Missing was actually preceding the reference - that was the clue!). As a result of unticking the box the macro ran as it should have.


    :thanx:

  • Missing object / reference

    Re: Missing object / reference


    Roy / Norie,


    Thanks for the help - I'll try to work through this logically (starting with the updating of variable names, checking the references more carefully etc.).


    Last quick (hopefully) question - I'm assuming I can turn on the option explict option at any time and this will apply to my current project? I'll then go through the code and update it to declare the variables (as good coding practice suggests).


    Thanks again.

  • Missing object / reference

    Re: Missing object / reference


    Norie,


    I had a quick look at the Tools > References and didn't see anything amiss (I'm not really sure what should/shouldn't be ticked).


    Is this specific to each file? I would have thought that the settings here would be global (i.e. regardless of file) - the strange bit is that older versions of the same macro still run and the lines I added in were just a few bog standard bits of code to do some tidying up. I looked at the new lines of code first thinking I had made a stupid mistake but nothing jumps out.


    I also put break-points in the code to try to narrow down my search but the code doesn't even get past a simple For...Next statement before returning the error - it's not making sense to me?

  • Missing object / reference

    Hope someone can help on this strange (for me anyway) occurence in Excel VBA:


    I've written an Excel VBA procedure on my home pc for a work based project (sad, I know) - when I take the file to work I get a missing object/reference error. The strange thing is, this piece of code used to work on my work pc (I took it home to enhance it - well, that was the objective anyway!).


    I seem to have done something to the whole code as even the simplest of instructions don't work in this file i.e.


    Code
    For I = 1 to 100
Cells(i,1).value = I
Next I


    (this is obviously not the code but just an example of what doesn't work)


    brings up the missing reference error. 2 Parts to this question:


    1. I have changed object names on a userform and made the necessary updates to the code - could this error be caused by this? (I don't want to start going through all of the code searching for an object name I've missed if the error is nothing to do with the renaming exercise.


    2. VBA help suggests looking at the "Reference view" which will show variables that are not properly referenced. I tend not to declare my variables (bad habit but old habits die hard) but I can't see why this would be a problem.


    As I say, the code works fine on my home pc so I would have thought it's something to do with set-up. Older versions of the code run on my work's PCs and this version is essentially the same thing but with a few renames/lines of code.


    Apologies if the details are a bit sketchy (I'm trying to cast my mind back to Friday after a heavy weekend!)


    Any ideas would be greatly appreciated.

  • Trying to make a way to return a constant number in my formula

    Re: Trying to make a way to return a constant number in my formula


    BuzzB,


    Not really sure that this is a VBA question but why not try:


    =if(d35>0,d35,0)


    The ISERROR statement is checking to see if the cell contains an error rather than if the cell (D35) is greater than 0. The formula above will give you:


    The value of cell D35 if D35 > 0
    The value 0 if the value of cell d35 0 or <0


    Hope this is what you are looking for...

  • Code moving values between sheet if there is a match

    Re: Code moving values between sheet if there is a match


    Hueby,


    I may be missing something here but why not just use Excel's VLOOKUP function rather than trying to produce code - or does it specifically need to be VBA coded?


    i.e. it sounds to me like you are looking for a match for data in column F of the unit 2 worksheet with data sitting in column A in the table worksheet - if a match is found, copy the corresponding data sitting in column B of the table worksheet over to column N of the Unit2 worksheet - exactly the type of thing VLOOKUP should be used for...


    Apologies if I've misunderstood...