Posts by mikef0x

Re: Object variable or with block variable not set error - calculating distance sheet

Pike,
I think thats the best solution which looks like it will stop the error at source.

thanks.

mike...

Re: Object variable or with block variable not set error - calculating distance sheet

Thanks everyone for the suggestions, Im a newbie at this but its starting to make sense especially when its explained as above .

thanks again.

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...

Sub TestingDistance1()
Dim data As Collection
Dim from As String
Dim too As String
Dim cell As Range
Dim rRng As Range


On Error GoTo stopsub
 
Set rRng = Sheet9.Range("Q2:Q6")
    For Each cell In rRng
    
    from = cell.Value
    too = cell.Offset(0, 1).Value
    
        
    Set data = GetTimeAndDistance(from, too, True, True, vbFriday, 960)
        


     
    cell.Offset(0, 20).Value = data("Distance") * 0.000621371192
    With cell.Offset(0, 21)
        .Value = data("Time") / 86400
        .NumberFormat = "hh:mm:ss"
   End With
label12:
 
Next


stopsub:
cell.Offset(0, 20).Value = ""
cell.Offset(0, 21).Value = ""


Resume label12
End Sub

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

Jindon,

That works a treat , thanks so much, this will save me days of work.

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...

Quote from Kyle123;628180

This is a modified version of code I have used elsewhere, it returns drive time in seconds, and distance in meters between 2 given postcodes. I've only tested this in the UK, but I don't see why it wouldn't work elsewhere.

It uses the same services that the on-line TomTom route planner uses. I've read through the T&Cs of the service and can't find anything that using the service in this way would breach, but let me know if you think there are issues and I'll remove the code. (I used to use google APIs for doing this, but it breaches their T&Cs so this is an alternative - there is also a Bing Maps method that I've used, but find this more reliable).

The only required parameters are the A-End and B-End Postcodes, without setting the other parameters, the function will assume the following:

• It should not route to avoid traffic
• It should not include traffic delays in drive time duration
• Day of travel is Today
• Time of travel is Now

Code

'--------------------------------------------------------------------------
' Purpose:          Retrieves DriveTime and Distance between 2 postcodes  '
' Parameters:                                                             '
'       Required:                                                         '
'                   aEnd            - Postcode                            '
'                   bEnd            - Postcode                            '
'       Optional:                                                         '
'                   AvoidTraffic   - Whether a route that avoids          '
'                                    traffic should be returned           '
'                   IncludeTraffic - Include traffic in calculating       '
'                                    travel time                          '
'                   DayofWeek      - Day of Week of travel                '
'                   Time           - Time of departure in minutes         '
'                                    From Midnight                        '
' Returns:          Collection of                                         '
'                                    "Distance" in Meters                 '
'                                    "Duration" in Seconds                '
'--------------------------------------------------------------------------
Public Function GetTimeAndDistance(aEnd As String, _
                            bEnd As String, _
                            Optional avoidTraffic As Boolean, _
                            Optional includeTraffic As Boolean, _
                            Optional DayofWeek As VbDayOfWeek, _
                            Optional time As Long) As Collection
Dim aLong   As String
Dim aLat    As String
Dim bLong   As String
Dim bLat    As String
Dim url     As String
Dim ret     As Collection
Dim Days    As Variant




Set ret = New Collection




Days = Array("today", "sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday")




With CreateObject("MSXML2.XMLHTTP")
    'GeoCode aEnd
    .Open "GET", "http://routes.tomtom.com/lbs/services/geocode/1/query/" & aEnd & "/json/1e2099c7-eea9-476b-aac9-b20dc7100af1;language=en;map=basic", False
    .send
    aLong = Split(Split(.responsetext, "longitude"":")(1), ",")(0)
    aLat = Split(Split(.responsetext, "latitude"":")(1), ",")(0)
    'GeoCode bEnd
    .Open "GET", "http://routes.tomtom.com/lbs/services/geocode/1/query/" & bEnd & "/json/1e2099c7-eea9-476b-aac9-b20dc7100af1;language=en;map=basic", False
    .send
    bLong = Split(Split(.responsetext, "longitude"":")(1), ",")(0)
    bLat = Split(Split(.responsetext, "latitude"":")(1), ",")(0)
    'Get Route Info
    .Open "GET", "http://routes.tomtom.com/lbs/services/route/1/" _
                    & aLat & "," & aLong & ":" & bLat & "," & bLong & _
                    "/Quickest/json/1e2099c7-eea9-476b-aac9-b20dc7100af1;language=en;" _
                    & "avoidTraffic=" & LCase(avoidTraffic) _
                    & ";includeTraffic=" & LCase(includeTraffic) _
                    & ";day=" & Days(DayofWeek) _
                    & ";time=" & IIf(time = 0, "now", time) _
                    & ";iqRoutes=2;trafficModelId=-1;map=basic", False
    .send
    ret.Add Val(Split(.responsetext, "totalDistanceMeters"":")(1)), "Distance"
    ret.Add Val(Split(.responsetext, "totalTimeSeconds"":")(1)), "Time"
End With








Set GetTimeAndDistance = ret








End Function

Display More

A sample usage:

Code

Sub TestingDistance()Dim data As Collection




Set data = GetTimeAndDistance("SW1A 1AA", "LS15 0AD", True, True, vbFriday, 960)




Sheet1.Range("A1").Value = data("Distance") * 0.000621371192
With Sheet1.Range("A2")
    .Value = data("Time") / 86400
    .NumberFormat = "hh:mm:ss"
End With
End Sub

Display More

Display More

Re: Sumproduct or alternative idea?

Simple, yes i think they may well do it. thanks

Re: Sumproduct or alternative idea?

dont think this works on 2003 does it?

Quote from NBVC;649311

Try this one:

=SUMPRODUCT(COUNTIFS($F$6:$F$205,$F210,K$6:K$205,{"H","S","H.5","AA","L","M","T"}))

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"))

Re: VBA to Change order of columns

Thanks Smallman, I get it now....

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

Sub Movecolumns()
Dim ori As Variant
Dim dst As Variant
Dim i As Integer
ori = Array("A", "B", "C")
dst = Array("C", "B", "A")
For i = LBound(ori) To UBound(ori)
Columns(ori(i)).Cut
Columns(dst(i)).Insert Shift:=xlToRight
Next
Range("A6").Select


End Sub

Re: Userform Vlookup very slow

Thanks Chaps, I'll try the solutions you put up, I will attach a copy of the sheet when i can work it how to do it.

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 :

Private Sub Combobookingslot_Change()
Dim var2 As Date
Dim var3 As Date
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Me.Combodate.Value = Range("currentdate")
var1 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 2, False)
var2 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 3, False)
var3 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 4, False)
var4 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 5, False)
var5 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 6, False)
var6 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 7, False)
var7 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 8, False)
var8 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 9, False)
var9 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 10, False)
var10 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 11, False)
var11 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 12, False)
var12 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 13, False)
var13 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 14, False)
var14 = WorksheetFunction.VLookup(Combobookingslot.Value, Worksheets("data").Range("maintable"), 15, False)
 


Me.Textgoodsinout.Value = var1
Me.Combodate.Value = var2
Me.Combotime.Value = var3
Me.Combocust.Value = var4
Me.Combowarehouse.Value = var5
Me.ComboContainer.Value = var6
Me.Texthaulcont.Value = var7
Me.Textvehicle.Value = var8
Me.Textconsign.Value = var9
Me.Textorder.Value = var10
Me.Textlines.Value = var11
Me.Textcartons.Value = var12
Me.Textpallets.Value = var13
Me.Textcontact.Value = var14
 
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Call sortdata
Call ImageExport
Me.Combobookingslot.List = Range("bookingslots").Value
End Sub

mike...