Posts by mikef0x

    I have some code below which which uses a function to collect the mileages from the internet, this works fine but i have problems when the postcode can't be found ect.( they are all in correct format but some are typed incorrecly and do not exist in the database)
    To get around this i have added an on error goto , this sort of works and if the postcode cant be found it inputs a "" in the field, however at the very end of the range it always ends with the object variable error.
    Can anyone tell me what i'm doing wrong ?




    cell value field is a postcode
    cell value offset 0,1 is a postcode


    gettimeanddistance is a function which returns the distance and time from the tomtom website


    any help much appreciated


    mike...




    Re: vba to summarise data in multiple rows into a singe row with extra columns


    Jindon,


    Just looked at you code and it works a treat , and as you thought, it appears i am nowehere near being able to do this !!


    This is what i am trying to get to :


    Summarise all data into a sheet by trip no in columns as -


    a = trip no (BO)
    b = From (C) Should be the same for each htrip
    c = Vehicle (BQ) should be the same for each trip
    d = Customer (K) could be different ( added to with a ", " between ?)
    e = weight (AB) sum of trip value
    f = pallets (AA) sum of trip value
    g = revenue (AP) sum of trip value
    h onwards (F) postcode(drop) as per your code is excellent





    mike...

    Re: vba to summarise data in multiple rows into a singe row with extra columns


    thanks everyone for the replies, i will see if i can use some of the examples,


    Ive added a sample of the actual file , the columns are not in the order i had on the example so it could be a little more difficult than i thought !
    I would like to also pull some other fields accross but i think i could work that out if i could do the below.


    Trip Ref - col BO
    Postcode(drop) - col F
    Weight - col AB



    thanks again everyone


    mike...

    I have a large spreadsheet with seperate delivery drop data on each row ( this is 71 columns of various data including load no, destination and weight ect...). I want to create a new sheet sumarising the load no,weight and also each delivery drop location but in extra columns ( drop 1, drop 2 ect ). I currently do this with a long complicated array formula but this slows the sheet to a stop when i try doing 5000+ lines ect.


    i've seen vba which would do this with the drops in the same column seperated by "," but could anyone help me with adding extra columns as below example ?


    original data


    load no weight destination
    11235 2000 london
    11235 4000 colchester
    11235 200 ipswich
    11235 500 norwich


    new sheet


    loadno weight drop1 drop2 drop3 drop4

    11235 6700 london colchester ipswich norwich



    mike.

    Re: Distances and DriveTimes


    Hi Kyle,


    Great piece of code , im a bit of a novice at VBA , how would i get this funcion to return the distance or time by inputing =getdistanceandtime() into a cell?
    I have a sheet with lots of rows of delivery data with columns for to and from postcodes ect. i would like to have the distance in one cell and the time in another, I cant work out how to do this from your code.



    mike...







    Re: Sumproduct or alternative idea?


    Thanks Smallman, That helps a little but it still takes 45 Seconds to update, I will try to upload a copy of the file later , i cant seem to get it to work at the minute.

    I'm trying to complete a hoiday sheet which has 6 months on a sheet (each day in a seperate column ) 150+ rows of employees , the user inputs "H","H.5","S","L","AA","M","T" as the absence type into each cell , so we end up with a lot of cells . This all works , however i need to total the types for each day against the department types for each employee ( these are in each row against each employee , I have done this with the formula below , but its very , very slow, and also recalculates all of the time , to stop this i have the formula copied into the cells and calculated before the sheet closes and then the values pasted over the calcs.


    Has anyone any ideas at all of an alternative way to sum these up?



    =SUMPRODUCT(($F$7:$F$206=$F210)*(K$7:K$206="H"))+SUMPRODUCT(($F$7:$F$206=$F210)*(K$7:K$206="H.5"))/2+SUMPRODUCT(($F$7:$F$206=$F210)*(K$7:K$206="S"))+SUMPRODUCT(($F$7:$F$206=$F210)*(K$7:K$206="AA"))

    I am trying to change the order of columns in a spreadsheet ,I've stumbled across a piece of code that looks like it will do what i want but i get an error (application-defined or object-defined error) everytime i run it, any help would be appreciated.


    mike


    Hi I have a spreadsheet with a couple of userforms one of which is used to retrieve data from a table via a combobox dropdown selection and then populate lots of textboxes using vlookups , this works but is very very slow. Does anyone know a way of speeding this up? I am very new to VBA and would appreciate any help at all.


    This is the code that i think is slow :





    mike...