Posts by Bertie

    Re: Concatenation using For Next Loop


    Thanks people for replying. Ger Plante's code was what I was trying to achieve and it works so thank you.


    Thanks also, Norie. My inefficiency duly noted ;-). Whenever I try some VBA I always go for the hardest solution....need more practice.


    Thanks again.
    B

    Hello Everybody


    Was after a bit a help if possible....


    I'm getting my knickers in a twist over this and to make it more annoying I know that I'm doing something really stupid.


    As part of a script I'm tring to concatenate the contents of column C with column H - the results to appear in column R. It will be an unknown and ever changing amount of rows.


    I believe that it's the value for field 'NewField' that's causing most problems, but it could be other stuff.


    Any help appreciated.


    Thanks v much
    B


    Re: Application.Vlookup


    So, a little bit embarrassed then – not up on my short hand and don’t have a mobile phone, so text language is out.


    Apologies, Krishnakumar and thanks for the link.

    Re: Application.Vlookup


    Thanks HTH


    Do you mean use a VB macro to run the filtering process?? Yes, that would work and I will use your example....unless someone knows how do do the whole process with VB script - Also to explain, I'm interested in learning how to correctly write script so would be interested see how it would look/structure.


    Thanks everyone


    Bert

    Re: Application.Vlookup


    Hi


    Just as quick addition SUMPRODUCT will not work because I need to deal with each line separately. So if in ‘Order Data’ I have two lines next to each other ie


    Item1 Qty1
    A1 -------- 2
    A1 -------- 8


    Then Qty1 for each line needs to be divided by the look up value for A1 for example


    Item2 Qty2
    A1 -------- 2


    So finally the Results worksheet will show


    Item3 Qty3
    A1 -------- 1 --- ( 2/2)
    A1 -------- 4 --- ( 8/2)


    Sorry, really making a mess of explaining!!

    Re: Application.Vlookup


    Thank you both very much. You have both come up with a solution for the problem that I presented, but I don’t think that I explained all very well.


    The problem is that the ‘Results’ worksheet cannot contain data such as ‘N/A’. I need the results to ignore any rows of irrelevant data – so in Thomach spreadsheet the results will be just the 5 rows of data. This is needed because the results need to be loaded into a database in .CSV form. I was going to incorporate this in the end of the VB script. If either of you or anyone else knows how to do this is VB then please let me know. I know that the results are easily sorted and the required data extracted but this has to be done so often that it becomes labour intensive.
    Again, thanks Alistair and Thomach for what you’ve done.


    Bert

    Re: Application.Vlookup


    Hi Alastair


    Sorry, has just occurred to me that it is possible that sheet 'Order Data' could contain two identical valid items for different quantities ie


    Item1 Qty1
    A1 4
    C1 8
    A1 2


    Both would need to be included in the 'Results' worksheet, but both should have different calculated Qty results - . vlookup would always calculate on the first instant that it finds?? Does this mean that it cannot be done this way?


    Thanks
    Bert

    Re: Application.Vlookup


    Thanks Alastair


    Need it in VBA because the spreadsheet (Order Data) will be updated many times a day - and the user responsible will not have the time. My intention was to assign it to a button so that when new data is dropped into "Order Data" the process can be quickly achieved with a push of a button.
    Really appreciate your time on this.
    Thanks
    Bert


    PS Look forward to seeing if I was just a little bit right with my attempt!!

    Re: Application.Vlookup


    Thanks Alastair


    Now here's the thing, from what I've done so far you will see that I'm actually not very good at VB - For what it's worth I've attached it below - any help would be great though.
    Thanks again


    Bert


    Hello all


    Having difficulties using application.vlookup in a script and hoped that someone on her could help.


    A simplified version of my problem is as follows….


    Worksheet1
    Item1 Qty1
    A1 2
    A2 4
    A3 1
    A4 5
    B1 1
    B2 1
    B2 3
    C1 3
    C2 8
    C3 1…..


    Worksheet2
    Item2 Lookup Qty2
    A1 A1/R 1
    C2 C2/R 2
    F1 F1/R 2…..


    I need to perform a lookup from ‘Item1’ against ‘Item2’ and return the value‘Lookup’ AND the associated value ‘Qty1’ from Worksheet1 needs to be divided against ‘Qty2’ in worksheet2. The results to be shown in a third worksheet, so for the above it would be..


    Worksheet3
    Lookup Qty3
    A1/R 2
    C2/R 4….


    Thanks for your time reading – if this doesn’t make sense please let me know


    Bert

    Hi


    Does anybody know the best way to do about the following (or even if it can't be done).


    I receive a spreadsheet with 2 column 'Item' and 'Quantity'. Each is revised daily.


    i.e.
    ITEM QTY
    a 100
    c 25
    z 50


    I need to run a macro from a button that will divide the QTY for each ITEM by 25 and then create a second worksheet with however many lines of 25 to reach the original total - so the above would end up looking like hte below...


    ITEM QTY
    a 25
    a 25
    a 25
    a 25
    c 25
    z 25
    z 25


    Thanks for reading


    B

    Hi


    Was wondering if any of you guys know if the below is possible and how to go about it.


    The scenario is that files (CSV's) are sent (Email) to me from a number of different sources. Source A, B, C, & D. These file names are all formated differently, so for example from source A the file name will always be AB(+variable data), from source B it will always be BGHJ(+variable data) etc. I need then to recognize the fixed data e.g. AB or BGHJ and change the file name to something that I need - so AB(+variable data) could become ABNEW(+variable data) and BGHJ(+variable data) could become BGHJNEW(+variable data) - these are then saved to a specified destination.


    Does this make sense? Thanks for reading - any ideas / suggestions / questions gratefully received


    Cheers
    Bertie

    Re: Selecting variable rows to copy and paste


    Thanks Youtham - that's just what I was after except that it doesn't seem to work if the area to be copied is just one row ie. row 2. Do you know how to account for this scenario.


    Thanks v much


    Bert

    Re: Selecting variable rows to copy and paste


    Thanks Youtham


    Not sure that that would work quite how I need. The macro that I've recorded is attached to a button. When pressed it needs to look down Column A until it finds a blank, then copy all the rows (which consist of column A - M) above and save as a CSV file. The code is below and as you can see I'm just copying A2:L34 knowing that there will be no more than 34 rows of info. It all works until except that when the data is saved as a CSV file it carries through delimiters for the blank cells that I don't need. Sorry If I've mis-uinderstood your suggestion.


    Do you know if there is a bit of code that could resolve this???


    thanks again


    Bert


    Hi


    I've been working through a spreadsheet, with no small amount of help from you guys, and got a little stuck again. I need to find a way to look at a spreadsheet and copy only the rows with data in column A. There will be no instances of blank lines in between complete lines.


    Thanks for reading


    Bert