Hi,
I checked both the sheets your code is working as per required
but when I run my code it deletes the numbers and the output
are the same numbers without the formula please check.
Can you merge your code with my code just the part to check and get parked
area
data. because both the codes are working fine separately but some things
are missing
in either codes.
my code adds the column I (Final KM) through code but in your code it has
to be there
for the code to work. your code merges the cells but my code does not merge.
It is nearly done but need only one final code if possible with the sorting
also.
thanks for bearing with me.
Regards
On Fri, Jun 6, 2014 at 9:36 PM, Vaibhav Joshi <v...@vabs.in> wrote:
> Check this file..
>
> Your code is also now working...
>
> Check if it is giving wrong output..
>
> I didnt tested..
>
> Cheers!!
>
>
> On Fri, Jun 6, 2014 at 9:14 PM, Vaibhav Joshi <v...@vabs.in> wrote:
>
>> Hi
>>
>>
>> check this file which works as per your need with my code, only issue is
>> the blank point which i stated in mine earlier point.
>>
>> When DP is followed by PU but parking is not in same are then what ?
>>
>> Check Test(2) sheet..
>>
>>
>> On Fri, Jun 6, 2014 at 11:12 AM, Jack Tribhuvan <jacktribhu...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> It seems to be working fine with a first few entries but
>>> some places it merges and shows '0'
>>>
>>> Let me add right now I am having this :
>>>
>>> For sorting I use:
>>>
>>> {Sub SortMIS()
>>> '
>>> ' SortMIS Macro
>>> ' Sorts the MIS
>>> '
>>>
>>> '
>>> Cells.Select
>>> ActiveWorkbook.Worksheets("Routes").Sort.SortFields.Clear
>>> ActiveWorkbook.Worksheets("Routes").Sort.SortFields.Add
>>> Key:=Range("C:C" _
>>> ), SortOn:=xlSortOnValues, Order:=xlAscending,
>>> DataOption:=xlSortNormal
>>> ActiveWorkbook.Worksheets("Routes").Sort.SortFields.Add
>>> Key:=Range("A:A" _
>>> ), SortOn:=xlSortOnValues, Order:=xlAscending,
>>> DataOption:=xlSortNormal
>>> ActiveWorkbook.Worksheets("Routes").Sort.SortFields.Add
>>> Key:=Range("E:E" _
>>> ), SortOn:=xlSortOnValues, Order:=xlAscending,
>>> DataOption:=xlSortNormal
>>> ActiveWorkbook.Worksheets("Routes").Sort.SortFields.Add
>>> Key:=Range("D:D" _
>>> ), SortOn:=xlSortOnValues, Order:=xlAscending,
>>> DataOption:=xlSortNormal
>>> With ActiveWorkbook.Worksheets("Routes").Sort
>>> .SetRange Range("A:I")
>>> .Header = xlYes
>>> .MatchCase = False
>>> .Orientation = xlTopToBottom
>>> .SortMethod = xlPinYin
>>> .Apply
>>> End With
>>> End Sub}
>>>
>>> for this I have used Record Macro procedure.
>>>
>>> Then a friend of mine gave me this code which I am using for my work:
>>>
>>> {Sub find_on_the_way()
>>> Dim match As Boolean
>>>
>>> Range("I2").Select
>>> While ActiveCell.Offset(0, -1).Value <> ""
>>> match = check_on_route(ActiveCell.Offset(0, -3).Value,
>>> ActiveCell.Offset(1, -3).Value)
>>> If ActiveCell.Offset(0, -5).Value = "DP" And
>>> ActiveCell.Offset(1, -5).Value <> "PU" Then match = False
>>> If ActiveCell.Offset(0, -5).Value = "PU" Then match = False
>>> If ActiveCell.Offset(0, -6).Value <> ActiveCell.Offset(1,
>>> -6).Value Then match = False
>>> If ActiveCell.Offset(0, -8).Value <> ActiveCell.Offset(1,
>>> -8).Value Then match = False
>>>
>>> If match = True Then
>>> Dim a As Integer, b As Integer
>>> a = ActiveCell.Offset(0, -1).Value
>>> b = ActiveCell.Offset(1, -1).Value
>>> ActiveCell.Value = WorksheetFunction.Max(a, b) +
>>> (WorksheetFunction.Min(a, b) / 2)
>>> Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row + 1,
>>> ActiveCell.Column)).Interior.Color = 65535
>>> ActiveCell.Offset(1, 0).Select
>>> Else
>>> ActiveCell.Value = ActiveCell.Offset(0, -1).Value
>>> End If
>>>
>>> ActiveCell.Offset(1, 0).Select
>>> Wend
>>>
>>> End Sub
>>>
>>> Function check_on_route(source As String, destination As String) As
>>> Boolean
>>> Dim FoundCell As Range, LastCell As Range, FirstAddr As String
>>> With Range("DESTINATIONS")
>>> Set LastCell = .Cells(.Cells.Count)
>>> End With
>>> Set FoundCell = Range("DESTINATIONS").Find(what:=source,
>>> after:=LastCell)
>>> If Not FoundCell Is Nothing Then FirstAddr = FoundCell.Address
>>>
>>> Do Until FoundCell Is Nothing
>>> If UCase(destination) = UCase(FoundCell.Offset(0, 1).Value) Then
>>> check_on_route = True
>>> Exit Function
>>> End If
>>>
>>> Set FoundCell = Range("DESTINATIONS").FindNext(after:=FoundCell)
>>> If FoundCell.Address = FirstAddr Then Exit Do
>>> Loop
>>>
>>> check_on_route = False
>>> End Function}
>>>
>>> Now in this code I wanted to add the cab parked area code as the above
>>> code does not look for
>>> cab Parked area.
>>>
>>> If you could make out what the code means you can know what to add
>>> My mistake I should have given his code in the first place Sorry.
>>>
>>> I have attached more data in the file you can use these codes to see the
>>> result and add
>>> the cab parked area code so as to finish the data.
>>>
>>> Thanks for helping me.
>>>
>>>
>>> On Thu, Jun 5, 2014 at 11:26 PM, Vaibhav Joshi <v...@vabs.in> wrote:
>>>
>>>> Also there is on blank point..
>>>>
>>>> what if .. cab parked area (as in 'area' sheet) is *NOT *matching
>>>> with the areas or on route areas??
>>>>
>>>>
>>>> On Thu, Jun 5, 2014 at 10:18 PM, Vaibhav Joshi <v...@vabs.in> wrote:
>>>>
>>>>> hey
>>>>>
>>>>> check this..
>>>>>
>>>>> you can run macro on test sheet by pressing A;t + F8 then enter..
>>>>>
>>>>>
>>>>> PS. You will be required to unmerge cell in col I before running macro.
>>>>>
>>>>>
>>>>> Cheers!!
>>>>>
>>>>>
>>>>> On Thu, Jun 5, 2014 at 11:54 AM, Jack Tribhuvan <
>>>>> jacktribhu...@gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Yes I sort the data before running the macro.
>>>>>> Now the DP stand for drop and PU stands for pick up
>>>>>> so only cab going for drop (DP) will bring pick up (PU)
>>>>>> therefore only those two consecutive rows DP followed by PU
>>>>>> are coloured yellow.
>>>>>> and the full lines are not merged but only the 'final km' column two
>>>>>> cells are merged and formula
>>>>>> used to define the total.
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>>
>>>>>> On Thu, Jun 5, 2014 at 11:09 AM, Vaibhav Joshi <v...@vabs.in> wrote:
>>>>>>
>>>>>>> Hey,
>>>>>>>
>>>>>>> Questions for you!!
>>>>>>>
>>>>>>> Whether data will be sorted or you want it to be sort before running
>>>>>>> Macro?
>>>>>>>
>>>>>>> Explain this line ..
>>>>>>>
>>>>>>> (always PU has to be followed by DP no two PU or DP or PU then DP
>>>>>>> is taken)
>>>>>>>
>>>>>>> pl explain in steps specially that sentences between or..
>>>>>>>
>>>>>>> Why do you want to merge lines & is it ok to get it done using
>>>>>>> formula & conditional formatting istead of Macro.
>>>>>>>
>>>>>>> Cheers!!
>>>>>>>
>>>>>>>
>>>>>>> On Wed, Jun 4, 2014 at 2:58 PM, Jack Tribhuvan <
>>>>>>> jacktribhu...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> I have basic knowledge excel and have 2010 office
>>>>>>>> if anyone could help as I have lot of data.
>>>>>>>> I have attached sample of working needed.
>>>>>>>>
>>>>>>>> The procedure I do manually is as below:
>>>>>>>>
>>>>>>>> I have two sheets 'data' and 'area'
>>>>>>>> in 'data' sheet monthly entries are done (which are new each month)
>>>>>>>> in 'area' sheet fixed areas and on route locations are given.
>>>>>>>>
>>>>>>>> In data look for two consecutive row having the below
>>>>>>>> First DP then PU (always PU has to be followed by DP no two PU or
>>>>>>>> DP or PU then DP is taken)
>>>>>>>> then I look for Cab No. (if they are same no.)
>>>>>>>> Then I look for area if they same or on route (as given in 'area'
>>>>>>>> sheet)
>>>>>>>> I check if cab parked area (as in 'area' sheet) is matching with
>>>>>>>> the areas or on route areas
>>>>>>>> on the two rows
>>>>>>>> then colour the two rows yellow
>>>>>>>> merge the last two cells of the rows
>>>>>>>> and use the formula as in the merged cell
>>>>>>>>
>>>>>>>> If this can happen with code I can save days of work.
>>>>>>>>
>>>>>>>> Thanks.
>>>>>>>>
>>>>>>>> --
>>>>>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna
>>>>>>>> be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this
>>>>>>>> forum @
>>>>>>>> https://www.facebook.com/discussexcel
>>>>>>>>
>>>>>>>> FORUM RULES
>>>>>>>>
>>>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>>>> Advice will not get quick attention or may not be answered.
>>>>>>>> 2) Don't post a question in the thread of another member.
>>>>>>>> 3) Don't post questions regarding breaking or bypassing any
>>>>>>>> security measure.
>>>>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>>>> 5) Jobs posting is not allowed.
>>>>>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>>>>>
>>>>>>>> NOTE : Don't ever post confidential data in a workbook. Forum
>>>>>>>> owners and members are not responsible for any loss.
>>>>>>>> ---
>>>>>>>> You received this message because you are subscribed to the Google
>>>>>>>> Groups "MS EXCEL AND VBA MACROS" group.
>>>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>>>> send an email to excel-macros+unsubscr...@googlegroups.com.
>>>>>>>>
>>>>>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>>>>>> Visit this group at http://groups.google.com/group/excel-macros.
>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>>>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>>>>> https://www.facebook.com/discussexcel
>>>>>>>
>>>>>>> FORUM RULES
>>>>>>>
>>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>>> Advice will not get quick attention or may not be answered.
>>>>>>> 2) Don't post a question in the thread of another member.
>>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>>> measure.
>>>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>>> 5) Jobs posting is not allowed.
>>>>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>>>>
>>>>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners
>>>>>>> and members are not responsible for any loss.
>>>>>>> ---
>>>>>>> You received this message because you are subscribed to a topic in
>>>>>>> the Google Groups "MS EXCEL AND VBA MACROS" group.
>>>>>>> To unsubscribe from this topic, visit
>>>>>>> https://groups.google.com/d/topic/excel-macros/cb4ff453jUA/unsubscribe
>>>>>>> .
>>>>>>> To unsubscribe from this group and all its topics, send an email to
>>>>>>> excel-macros+unsubscr...@googlegroups.com.
>>>>>>>
>>>>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>>>>> Visit this group at http://groups.google.com/group/excel-macros.
>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>
>>>>>>
>>>>>> --
>>>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>>>> https://www.facebook.com/discussexcel
>>>>>>
>>>>>> FORUM RULES
>>>>>>
>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.
>>>>>> 2) Don't post a question in the thread of another member.
>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.
>>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>> 5) Jobs posting is not allowed.
>>>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>>>
>>>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners
>>>>>> and members are not responsible for any loss.
>>>>>> ---
>>>>>> You received this message because you are subscribed to the Google
>>>>>> Groups "MS EXCEL AND VBA MACROS" group.
>>>>>> To unsubscribe from this group and stop receiving emails from it,
>>>>>> send an email to excel-macros+unsubscr...@googlegroups.com.
>>>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>>>> Visit this group at http://groups.google.com/group/excel-macros.
>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>
>>>>>
>>>>>
>>>> --
>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>> https://www.facebook.com/discussexcel
>>>>
>>>> FORUM RULES
>>>>
>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>> will not get quick attention or may not be answered.
>>>> 2) Don't post a question in the thread of another member.
>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>> measure.
>>>> 4) Acknowledge the responses you receive, good or bad.
>>>> 5) Jobs posting is not allowed.
>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>
>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners
>>>> and members are not responsible for any loss.
>>>> ---
>>>> You received this message because you are subscribed to a topic in the
>>>> Google Groups "MS EXCEL AND VBA MACROS" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/excel-macros/cb4ff453jUA/unsubscribe.
>>>> To unsubscribe from this group and all its topics, send an email to
>>>> excel-macros+unsubscr...@googlegroups.com.
>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>> Visit this group at http://groups.google.com/group/excel-macros.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to excel-macros+unsubscr...@googlegroups.com.
>>> To post to this group, send email to excel-macros@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/excel-macros.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/excel-macros/cb4ff453jUA/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
