Length of current selection 1

[YoungTurk]

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

 

[IFRs]

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

 

[msmith]

YoungTurk,

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

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:
[li]Change the value of the Const declarations to suit your particular situation.[/li]
[li]Likewise with the individual worksheet names (case sensitive).[/li]
[li]Assumes your code resides in a workbook other than DMOE.xls or OCC.xls.[/li]
[li]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.[/li]
[li]The UCase function ensures case insensitivity if the ID's are alphanumeric - If not, remove to speed things up.[/li]
[li]The Trim function ensures a correct match even if extraneous spaces were included with the ID's.[/li]


HTH
Mike

 

[YoungTurk]

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