×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Length of current selection

Length of current selection

Length of current selection

(OP)
HI,

I'm a VBA newbie trying to write some code for excel macros.  I've gotten hung up on this one, though.  

My goal is to sequentially work down an entire column of IDs in a workbook called DMOE.xls, match each ID to a column of IDs in a workbook called OCC.xls, and take the necessary related information from the OCC workbook matched ID row to the DMOE workbook.  My first approach is to determine the length of the column of IDs and then run two nested FOR loops that many times to compare the values of each and every pair of IDs to find the matches.  Unfortunately, I'm stumped for a way to determine the length of the column of IDs.  Probably very simple, but Len and Count commands don't seem to be appropriate.

So, how can I count the number of rows with data in a given column?  And, for extra credit, any ideas for a more efficient matching routine? (remember, between two different  files).  

Thanks in advance,
YoungTurk

RE: Length of current selection

You can loop down the row testing for a value and exit the loop if the value is zero.

RE: Length of current selection

YoungTurk,

Here is some code to do what you requested, with notes below.  The key bit of code is the line LastUsedRowN = ...


CODE

Const COLUMN1 = 1
Const COLUMN2 = 1
Const STARTROW1 = 1
Const STARTROW2 = 1



Sub ConsolidateData()
Dim Wkb1 As Workbook
Dim Wkb2 As Workbook
Dim Wks1 As Worksheet
Dim Wks2 As Worksheet
Dim LastUsedRow1 As Long
Dim LastUsedRow2 As Long
Dim i As Long, j As Long


   Set Wkb1 = Workbooks.Open("DMOE.xls")
   Set Wkb2 = Workbooks.Open("OCC.xls")
   
   Set Wks1 = Wkb1.Worksheets("Sheet1")
   Set Wks2 = Wkb2.Worksheets("Sheet1")
   
   With Wks1
     LastUsedRow1 = .Cells(65536, COLUMN1).End(xlUp).Row
   End With
   With Wks1
     LastUsedRow2 = .Cells(65536, COLUMN2).End(xlUp).Row
   End With

   For i = STARTROW1 To LastUsedRow1
     For j = STARTROW2 To LastUsedRow2
       If UCase(Trim(Wks1.Cells(i, COLUMN1).Text)) = UCase(Trim(Wks2.Cells(j, COLUMN2).Text)) Then
         'Copy information from Wks2 to Wks1 (presumably on row i)
         Exit For '(If & only if a single match is expected/desired - will speed things up)
       End If
     Next j
   Next i
   
   Wkb1.Close SaveChanges:=False
   Wkb2.Close SaveChanges:=False
   
   Set Wks1 = Nothing
   Set Wks2 = Nothing
   Set Wkb1 = Nothing
   Set Wkb2 = Nothing
   
End Sub

Notes:
  • Change the value of the Const declarations to suit your particular situation.
  • Likewise with the individual worksheet names (case sensitive).
  • Assumes your code resides in a workbook other than DMOE.xls or OCC.xls.
  • I have not included code to do the actual copying.  Use the referenced object variables Wkb1, Wkb2, Wks1 and Wks2 along with a Range reference.
  • The UCase function ensures case insensitivity if the ID's are alphanumeric - If not, remove to speed things up.
  • The Trim function ensures a correct match even if extraneous spaces were included with the ID's.



  • HTH
    Mike

    RE: Length of current selection

    (OP)
    Hi IFRs and Mike,

    I have a code hacked together using IFRs reccomendation (VarName <> ""), but I wanted to know how many items there were for user interface reasons, so I plan to implement the

    LastUsedRow1 = .Cells(65536, COLUMN1).End(xlUp).Row

    line of code to do so.  I've also always preferred for-do loops to while-do loops from a logical standpoint (I like deterministic things).  I recognize the syntax from sections of code I've recorded, but wouldn't have gotten there on my own.  Mike clearly put some effort into this, many thanks!  I'll post my final code here in a few days if anyone is interested.


    YoungTurk

    Red Flag This Post

    Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

    Red Flag Submitted

    Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
    The Eng-Tips staff will check this out and take appropriate action.

    Reply To This Thread

    Posting in the Eng-Tips forums is a member-only feature.

    Click Here to join Eng-Tips and talk with other members!


    Resources