no need to write macro
sort any one column
and use lookup to other column
that will work fine
On Sat, Mar 12, 2011 at 6:06 AM, bpascal123 <bpascal...@googlemail.com>wrote:
> Hi cyberspace,
>
> I have spent quite some time trying to make this work but at this
> point from adding many msgbox checks, using the watch window for
> variables values everything seems coherent to me.
>
> I have 2 columns with sorted identical and not identical numercial
> values in both columns, see below :
>
> col.A col.B
> 251120 251130
> 251140 272505
> 251145 291101
> 272505 292100
> 272535
> 291130
> 292100
>
> I need to align identical value and to place single value alone on one
> row :
>
> 251120
> 251130
> 251140
> 251145
> 272505 272505
> 272535
> 291101
> 291130
> 292100 292100
>
> Now with the vba code, I get this :
>
> Option Explicit
> Option Base 1
>
>
> Public Sub RowMatching()
>
> Dim wkb As Workbook
> Dim wks As Worksheet
> Set wkb = Workbooks("code_row_v2.xls")
> Set wks = wkb.Worksheets("Sheet1")
>
> Dim trouve As Boolean
>
> Dim LigCol1 As Integer 'numéro de ligne pour la premiere colonne
> Dim LigCol2 As Integer 'numéro de ligne pour la seconde colonne
> Dim LastRow As Long
> Dim tmp
> Dim Numligne(256) As Long
> Dim marchehaute As Integer
> Dim marchebasse As Integer
> Dim marche As Integer
>
> wks.Cells(1, 1).Select
>
> LastRow = 0
> LigCol1 = 1
> While wks.Cells(LigCol1, 1) <> ""
> LastRow = LastRow + 1
> LigCol1 = LigCol1 + 1
> Wend
>
> LigCol1 = 1
> wks.Cells(LigCol1, 1).Select
> While LigCol1 <= LastRow '''MAIN LOOP
> Numligne(LigCol1) = wks.Cells(LigCol1, 1)
> 'MsgBox wks.Cells(LigCol1, 1)
> For LigCol2 = 1 To LastRow
>
> If Numligne(LigCol1) = wks.Cells(LigCol2, 2) Then '2a-IF7
>
> If LigCol2 < LigCol1 Then '3a-IF9
> Cells(LigCol2, 2).Select
> marchehaute = LigCol1 - LigCol2
> marche = 1
> While marche <= marchehaute
> Selection.Insert shift:=xlDown
> marche = marche + 1
> Wend
>
> ElseIf LigCol2 > LigCol1 Then
> Cells(LigCol1, 1).Select
> marchebasse = LigCol2 - LigCol1
> marche = 1
> While marche <= marchebasse
> Selection.Insert shift:=xlDown
> marche = marche + 1
> LastRow = LastRow + 1
> Wend
>
> End If '3a-IF9
>
> End If '2a-IF7
>
> Next LigCol2
>
> LigCol1 = LigCol1 + 1
>
> Wend
>
> LigCol1 = 1
> wks.Cells(LigCol1, 1).Select
> For LigCol1 = 1 To LastRow
> MsgBox wks.Cells(LigCol1, 1) & " - " & wks.Cells(LigCol1, 2)
>
> If Not IsEmpty(wks.Cells(LigCol1)) Then
>
> If wks.Cells(LigCol1, 1).Value <> wks.Cells(LigCol1, 2).Value
> Then
> Rows(LigCol1).Select
> Selection.Insert shift:=xlDown
> Cells(LigCol1 + 1, 1).Select
> Selection.Cut
> Cells(LigCol1, 1).Select
> ActiveSheet.Paste
> LastRow = LastRow + 1
> End If
>
> End If '2b-IF5
>
> Next LigCol1
>
> MsgBox LastRow
>
> End Sub
>
>
> Variable names are in french but it's easy : consider marche is
> floor : marchebasse = lowerfloor, marchehaute = upperfloor... in fact
> marche means step but steps has many meaning in english, here it would
> be stairway.
>
> Ok, this is what I get when i run the code from above :
>
>
> 251120
> 251130
> 251140
> 251145
> 272505 272505
> 272535 291101
> 291130
> 292100 292100
>
> Although, the switch is completed for values 251120 and 251130
> initialy on the same row, they are now on 2 distinct rows as stated
> in For LigCol1 = 1 To LastRow loop
>
> But when it comes to values 272535 and 291101, no new rows is added as
> it should for two different values on the same row. msgbox even show
> the loop is going though these values as with 251120 and 251130
>
> Could you point where I am missing something?
>
> Then if one can rearrange the whole thing, I feel recursion could make
> me spare a few lines here but I admit I don't have the skills to deal
> with algorithms and make thing simple when it is.
>
> Thanks,
> Cyberuser
>
>
>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Your Sincerely
Sanjoy Nath
Engineer
EOL Gurgaon
EOL/M/NZ/Engg.Dept.(Haryana Division)
We prepare custom tools (Softwares) to prepare Autocad Drawings
Of the Engineering Components.Some Of Our Clients Provide the
Design Calculation Steps And Sample Drawings (Templates for Eg the
Nuts , Bolts , Screws , Stair Cases , Patterns , Geometries , LandScapes,
Layouting, General Engineering Components , Mechanical Engineering
Components,
Civil/Structural Engineering Components,Architectural Components) to follow
and to meet their standards and specifications.The Tools we develop are
entirely customised and specific for clients own requirements and
they are not re distributed to others.
Let me be precise regarding our job profile and what we can do for you
Suppose you have to develop any design component repeatedly for different
projects
Only Some of its parameters are changing and you need the drawing for that
component.
you cannot prepare the block for that.Since even in the custom Block in
Autocad you
cannot controll all the parameters automatically and very few users in
Autocad can follow
the technicalities to handle the customisable blocks in Autocad.
On the Other hand , customisable blocks cannot decide the dimensions by
themselves nor can
compute the design calculations.But And it takes a long time to develop the
drawings.
Moreover if there is any revision in the General Arrangement drawings or
Facilities,
the entire things need to change and you have to start from the scratch
again and
draftsman forget to change some of the dimensions or the BOQ values in the
tables
attached to the drawings.These things are ridiculous to your clients.
We can understand these issues since we are in the design/Detailing and
drafting industry
for last 10 years and have worked for more than 6 companies(Directly as
employee or in parttime)
in 3 domains
(Piping,Architecture and Structural)
And now we have attained the expertise to develop the dxf files from
Excel/EXE Applications.
We can develop and provide the tools (EXE tools or Excel Files as you need
and as much you ask for)
to develop these drawings automatically.
Suppose you want to develop the drawings of doors/Panels with some given
features many/some of
which will change every time from task to task or from revision to revision
or from project to project.
You have to revise the drawings and your draftsman/detailer will take 30
minutes to 1 hour to revise each of
these several components.We can save this valuable time for you through the
tools.
Moreover many a time it happens that you have to prepare the scaled drawings
from the data in STAAD pro
and you have to type the utilisation ratios and reactions beside the members
including the sizes also.
Have you ever calculated how much time you spend on that and how much
errorprone are these ??????
We can help you in these matters also.
We can extract the data in tables/Dimensions in the Autocad Drawing to Excel
(With Automations)such that you can prepare the
BOQs easily.Suppose you have a drawing and the dimensions of some feature in
a certain layer.We can provide
tools to you which will read the data from your dimensions in specific layer
and will type them
in the excel format.And can prepare the drawings of some other feature from
the extracted dimensions.
We can develop the same kind of tools for Word ,STAAD Pro , Tekla , PDF ,
Excel Also which are applicable for
MIS purposes and the custom checking tools for word processing
industries/Quality control departments.
We have given you the examples with CAD and Excel Only.But we have expertise
in STAAD Pro ,TEKLA
PDF technologies also.We work in the digitisation works also.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
