sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,431
- Office Version
- 2016
- Platform
- Windows
I'm trying to retrieve a specified number values from rows with criteria.
The number of rows to retrieve is determined by an option button on a userform, (for this example lets just say 10 records). I can't get this to work, (I apologise but I don't know enough about do until loops, hence I'm asking here).
Can someone please advise where the Do Until and Loop parts would fit within this?
I'm sure there will be a much more efficient way to achieve what I'm trying to do, this is my efforts to play around with it to see what happens!
The number of rows to retrieve is determined by an option button on a userform, (for this example lets just say 10 records). I can't get this to work, (I apologise but I don't know enough about do until loops, hence I'm asking here).
Can someone please advise where the Do Until and Loop parts would fit within this?
VBA Code:
If Option10.Value = True Then X = 10
'Open File
Set wb = Workbooks.Open(FileToOpen) '(defined elsewhere)
LGCRow = wb.Sheets("UNALLOCATED").Range("A65536").End(xlUp).Row
LCRow = ThisWorkbook.Sheets("CHECKS").Range("A65536").End(xlUp).Row
Do Until i = X
If OptionPBP.Value = True Then
For Each cell In wb.Sheets("UNALLOCATED").Range("A2:A" & LGCRow)
If cell.Value = "PBP" Then
ThisWorkbook.Sheets("CHECKS").Range("B" & LCRow).Value = cell.Offset(0, 1).Value
ThisWorkbook.Sheets("CHECKS").Range("C" & LCRow).Value = cell.Offset(0, 2).Value
ThisWorkbook.Sheets("CHECKS").Range("D" & LCRow).Value = cell.Offset(0, 3).Value
ThisWorkbook.Sheets("CHECKS").Range("E" & LCRow).Value = cell.Offset(0, 4).Value
ThisWorkbook.Sheets("CHECKS").Range("J" & LCRow).Value = cell.Offset(0, 5).Value
ThisWorkbook.Sheets("CHECKS").Range("N" & LCRow).Value = cell.Offset(0, 6).Value
ThisWorkbook.Sheets("CHECKS").Range("O" & LCRow).Value = cell.Offset(0, 7).Value
ThisWorkbook.Sheets("CHECKS").Range("P" & LCRow).Value = cell.Offset(0, 8).Value
ThisWorkbook.Sheets("CHECKS").Range("S" & LCRow).Value = cell.Offset(0, 9).Value
ThisWorkbook.Sheets("CHECKS").Range("T" & LCRow).Value = cell.Offset(0, 0).Value
ThisWorkbook.Sheets("CHECKS").Range("U" & LCRow).Value = cell.Offset(0, 11).Value
ThisWorkbook.Sheets("CHECKS").Range("X" & LCRow).Value = cell.Offset(0, 12).Value
ThisWorkbook.Sheets("CHECKS").Range("AC" & LCRow).Value = cell.Offset(0, 13).Value
ThisWorkbook.Sheets("CHECKS").Range("AQ" & LCRow).Value = cell.Offset(0, 14).Value
LCRow = LCRow + 1
i = i + 1
End If
Next cell
End If
Loop
I'm sure there will be a much more efficient way to achieve what I'm trying to do, this is my efforts to play around with it to see what happens!