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
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
RE: Length of current selection
Here is some code to do what you requested, with notes below. The key bit of code is the line LastUsedRowN = ...
CODE
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:
HTH
Mike
RE: Length of current selection
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