PDA

View Full Version : Solved: Trouble with copying in a loop...



paulked
04-20-2013, 06:31 AM
I have two columns of data, Meter Number in Column A and Name in Column B.

I then use a combobox in a userform to select a name and want the meter numbers for that name to appear in the next combobox.

I am having trouble with this code:
Sub FindMeterNumbers(nm)
Dim lastRow, i, sh, r
Dim nm As String
Set sh = Sheets("Meters")
r = 1
sh.Select
lastRow = sh.UsedRange.Rows.Count
For i = 1 To lastRow
If Range("B" & i).Value = nm Then
Range("A" & i).Copy
Range("E" & r).PasteSpecial
r = r + 1
End If
Next
Columns("E:E").Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


If, for example, there are 4 meters belonging to Fred then the first 4 meter numbers in column A are copied to E1:E4 and not the meter numbers corresponding to Fred!

I can't see where I am going wrong, please :help

Many thanks

paulked
04-20-2013, 06:49 AM
Please ignore this post, it works!! :blush