tpoynton
05-21-2007, 07:32 AM
Greetings,
I'm probably doing something silly, ignorant, inefficiently, or perhaps all of them! Feel free to make suggestions of any sort, even completely different solutions that have the same end result.
I'm trying to merge two worksheets based on a 'key' value. If that value does not exist in the 'School-level-ALL', put 'not moved' in the NCES sheet and move on to the next row; otherwise, copy the row from NCES to School-level-ALL and write 'moved' in the NCES sheet.
The data are sorted, if that helps.
The attached example has 11 or so rows; the original data have nearly 2000 rows.
I run into a problem in the first case where the key variable (iOrgCode in the sample) does not equal what is in the NCES sheet.
Here's the VBA (which is also in the attached workbook)
Public Sub moveNCESdata()
Application.ScreenUpdating = False
Dim iOrgCode As Integer
Dim i As Integer
Dim cell As Range
Dim NCESrange As Range
Dim shtName As String
shtName = "NCES"
With Sheets(shtName)
Set NCESrange = Range(Cells(2, 1), Cells(11, 1))
End With
For i = 2 To 11
iOrgCode = Sheets("School-level-ALL").Cells(i, 4).Value
For Each cell In NCESrange
If iOrgCode = cell.Value Then
Sheets("NCES").Range(Cells(cell.Row, 1), Cells(cell.Row, 12)).Copy
Sheets("NCES").Cells(cell.Row, 13).Value = "MOVED"
Sheets("School-level-ALL").Cells(i, 39).PasteSpecial (xlPasteValues)
ElseIf iOrgCode <> cell.Value Then
Sheets("NCES").Cells(cell.Row, 13).Value = "NOT MOVED"
End If
Next cell
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
EDIT - also, the routine didnt even work consistently in terms of copying data when the key did match...sometimes it did, sometimes it didnt...
I'm probably doing something silly, ignorant, inefficiently, or perhaps all of them! Feel free to make suggestions of any sort, even completely different solutions that have the same end result.
I'm trying to merge two worksheets based on a 'key' value. If that value does not exist in the 'School-level-ALL', put 'not moved' in the NCES sheet and move on to the next row; otherwise, copy the row from NCES to School-level-ALL and write 'moved' in the NCES sheet.
The data are sorted, if that helps.
The attached example has 11 or so rows; the original data have nearly 2000 rows.
I run into a problem in the first case where the key variable (iOrgCode in the sample) does not equal what is in the NCES sheet.
Here's the VBA (which is also in the attached workbook)
Public Sub moveNCESdata()
Application.ScreenUpdating = False
Dim iOrgCode As Integer
Dim i As Integer
Dim cell As Range
Dim NCESrange As Range
Dim shtName As String
shtName = "NCES"
With Sheets(shtName)
Set NCESrange = Range(Cells(2, 1), Cells(11, 1))
End With
For i = 2 To 11
iOrgCode = Sheets("School-level-ALL").Cells(i, 4).Value
For Each cell In NCESrange
If iOrgCode = cell.Value Then
Sheets("NCES").Range(Cells(cell.Row, 1), Cells(cell.Row, 12)).Copy
Sheets("NCES").Cells(cell.Row, 13).Value = "MOVED"
Sheets("School-level-ALL").Cells(i, 39).PasteSpecial (xlPasteValues)
ElseIf iOrgCode <> cell.Value Then
Sheets("NCES").Cells(cell.Row, 13).Value = "NOT MOVED"
End If
Next cell
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
EDIT - also, the routine didnt even work consistently in terms of copying data when the key did match...sometimes it did, sometimes it didnt...