Posts by Smallman

  • Code clean up help

    LOL - "while Smallman is sweating away". Yesterday I posted a few times on Ozgrid and then I went to the pub - the perfect day!!!! I am planning on repeating the dose today so this is my first look at your problem in 24 hours. And I would suggest I will be heading out to knock the top off a cold Christmas beer very shortly. It is early here in Oz but somewhere in the world it is 5pm. Bottoms up guys!!!!!


    Smallman

  • Code clean up help

    LOL - yes you know how I think...... I was going to say something about the selection but it was mostly recorded code and I figured the final version would have spelt that out. I wanted to get rid of the sheet references mainly and needed the file to nail that down. Up to the Op from here. At this time of year I am time rich and love to roll back the clock and do this sort of thing once more. Take it easy jolivanes!!!!!


    Mr Smallman :)

  • Code clean up help

    Hi


    If you remove the clear contents lines and replace them with this – you save 27 lines of code.


    Code
    Range("B2:G10000, K2:l10000, X2:AD10000, AT2:AQ10000, AV2:AV10000, AZ2:AZ10000, BB2:BB10000").ClearContents


    If you post your workbook I will make your code clean. A significant change would be to have 3 cells called


    bid breakdown type 1
    bid breakdown type 2
    bid breakdown type 3


    In these cells you would type your bid criteria – I never use input boxes in code as it stops the code where the method above will not.


    Hopefully we will see your file soon if not email it to me directly.


    Take care


    Smallman

  • Chart Point Format

    Hi


    It is about how you set your table up and you will need to set yours up in a different way - or at least calculate it differently. Here is a quick example of how I would do what you are trying to do. The colours line up because all the data needs to be on the same line.


    Have a look at the attached example. Hope this points you in the right direction.


    Take care


    Smallman

  • VBA Script Loop Help

    Re: VBA Script Loop Help


    Hi


    The problem with your first attempt is the macro pastes the formulas not the values.


    I have been working on this in the mean time while you cowboys have been chin wagging. That MakeList Function slows the whole file.


    I cleaned up the file to present the way I would have it. Took out the table and came up with the following.



    Goes nicely.


    Take care


    Smallman

  • VBA deleting rows based on one cell value

    Re: VBA deleting rows based on one cell value


    Hi


    One of the problems with this sort of approach is that people don't stand back programmatically and think how would I do this manually. Would you select every cell from rows 2 to 500 manually and delete each? No you would not. You would filter the data and remove the offending lines all at once.


    So too in code you would act in the same way.


    Code
    Sub GetRid()
    [b1:b500].AutoFilter 1, ">=2000"
    [b2:b500].EntireRow.Delete
    [b1].autofilter
End Sub


    Hopefully you get this working without a file - if not sing out and I will dump a working file in here. :)


    Take care


    Smallman

  • List value returns values from another named range

    Re: List value returns values from another named range


    You have supplied a problem without a file. The 5 minute solution takes longer as people need to re create your problem.


    Try and think of your problem from the other end, it makes life in forums easier for all.


    Take care


    Smallman

  • copy cells from one sheet to another when criteria is met.

    Re: copy cells from one sheet to another when criteria is met.


    Hi


    Moving data in batches is a nice way to shift the data. However you did not say if you wanted to overwrite the data on the other sheet or add to it. I chose the latter.


    Code
    Sub MoveData()
  Range("H1:H" & Cells(Rows.Count, 8).End(xlUp).Row).AutoFilter 1, ">1"
  Range("A2:H" & Cells(Rows.Count, 8).End(xlUp).Row).Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
End Sub


    Might be an idea to add a test for no values being above 100%.


    Take care


    Smallman

  • offset from first coulmn

    Re: offset from first coulmn


    Nice work Pike. Your VB is always top notch. :)


    As an alternative we can also use formula. The following:


    =IF(COUNTIF(I9:M9,"*"&$A$1&"*")>0,"X","")


    elegantly handles the problem. If you put the formula in D9 and drag down.


    Take care


    Smallman

  • offset from first coulmn

    Re: offset from first coulmn


    Hi Jeff


    Love to see your file. Looks like VBA is over engineering something formula might be able to be employed to do. Can you upload a moch up?


    Take care


    Smallman

  • VBA function to Calculate FIFO price

    Re: VBA function to Calculate FIFO price


    Hi Pradeep_atm


    Your answer will depend on the opening stock. T


    Take a look at this page, it has a couple of working examples of FIFO. Start at the bottom as it is a function and you should be able to manipulate it. If that is not appropriate use the macro. One will help as this is exactly what you are trying to solve. It is just a matter of manipulating the data to suit your needs.


    Hope this helps.



    FIFO Calculator



    Take care


    Smallman

  • FIFO calculation

    Re: FIFO calculation


    Pradeep


    The post is 11 years old now and you have been a member 12 years yourself. It is highly likely if Ramesh found the answer he was looking for that he is not still checking the board. If you want to fashion a new question someone will help you quite quickly.


    All the best


    Smallman