PDA

View Full Version : Should This Code Work



drums4monty
08-06-2007, 04:44 AM
A few months ago someone kindly wrote this code for me to move data from one sheet to another:


Sub move_it()
Dim lrow As Long, lrow2 As Long
Dim cell As Range, result As Range, rngsource As Range
Dim rngtocopyfrom As Range
lrow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
lrow2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
Set rngsource = Sheets(1).Range("A1:A" & lrow)
Set rngtocopyfrom = Sheets(2).Range("A1:A" & lrow2)
For Each cell In rngsource
Set result = rngtocopyfrom.Find(cell.Value, LookIn:=xlValues)
If Not result Is Nothing Then
result.Offset(, 1).Copy Worksheets(1).Range("D" & cell.Row)
End If
Next cell
End Sub


The PC in question now has Excel 2003 on it and the above code does not seem to work. Is there a difference in the way the programmes work or should it still work ok?

anandbohra
08-06-2007, 05:00 AM
post your original data as there is not systax error in code as per excel 2003 format

rory
08-06-2007, 05:08 AM
Can you clarify what you mean by "does not seem to work"? Does it do something wrong; or do nothing at all; or crash your computer; or something else?
Regards,
Rory

drums4monty
08-06-2007, 05:20 AM
The codes fine (I think), I think there is a problem with the data I am trying to process as I have just had this working on another sheet.

Sorry to have troubled you :(

Alan

drums4monty
08-06-2007, 05:21 AM
By the way rory , it does nothing at all, but like I say, I think there must be a problem with the data.

Alan

rory
08-06-2007, 05:22 AM
The code assumes that column A is populated for all the rows you are interested in. If that's not the case, let us know and we can adapt it to find the real last row and column to use.
Regards,
Rory

Bob Phillips
08-06-2007, 05:25 AM
It worked fine for me in 2003 (once I was able to decipher what was rngSource and rngToCopyFrom - don't they mean the same thing). maybe it is just that the code is confusing and doesn't actually do what is wanted.

rory
08-06-2007, 05:26 AM
You could try this:
Sub move_it()
Dim lrow As Long, lrow2 As Long
Dim cell As Range, result As Range, rngsource As Range
Dim rngtocopyfrom As Range
lrow = LastCellInSheet(Sheets(1)).Row
lrow2 = LastCellInSheet(Sheets(2)).Row
Set rngsource = Sheets(1).Range("A1:A" & lrow)
Set rngtocopyfrom = Sheets(2).Range("A1:A" & lrow2)
For Each cell In rngsource
Set result = rngtocopyfrom.Find(cell.Value, LookIn:=xlValues)
If Not result Is Nothing Then
result.Offset(, 1).Copy Worksheets(1).Range("D" & cell.Row)
End If
Next cell
End Sub
Public Function LastCellInSheet(wks As Worksheet) As Range
' Returns the cell at the bottom right corner of the sheet's real used range
Dim lngLastCol As Long, lngLastRow As Long
lngLastCol = 1
lngLastRow = 1
On Error Resume Next
With wks.UsedRange
lngLastCol = .Cells.Find(what:="*", after:=.Cells(1), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
lngLastRow = .Cells.Find(what:="*", after:=.Cells(1), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End With
Set LastCellInSheet = wks.Cells(lngLastRow, lngLastCol)
End Function


Regards,
Rory

Charlize
08-06-2007, 07:09 AM
Originally this thread : http://vbaexpress.com/forum/showthread.php?t=11577

Charlize
08-06-2007, 03:55 PM
It worked fine for me in 2003 (once I was able to decipher what was rngSource and rngToCopyFrom - don't they mean the same thing). maybe it is just that the code is confusing and doesn't actually do what is wanted.The OP wanted a way to combine to sets of data. First one on sheet1 with articlenumber, description but no price. Second sheet with articlenumber and price.

The goal was to look for an articleno in sheet2 that matches the articleno in sheet1 and if a match was found, copy the price to column C.

Rngsource was on sheet1 - price to add
Rngtocopyfrom was on sheet2 - price to look for and copy from here.