Forum home » Delegate support and help forum » Microsoft VBA Training and help » vba courses london - Loop
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
vba courses london - Loop
Resolved · Low Priority · Version Standard
Reply Sat 18 Nov 2006, 21:42Delegate Liz said...
Liz has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Loop
I have adapted some code to look in a cell to see if the name matches that which has been input in a drop down list on a combo box on an input form and then copy the value of column 10in the same row - similar to what we did on the VBA course. I set the public variable for that name as txtTenant.
What I wanted to do was to then write the code that if the first cell did not match the name selected that it would then drop down one cell and compare it and keep going down until it finds a match, right down to the end of the column of data if necessary.
My coding is not working - it stays at the first person it finds in the first cell.
This is the code:
Sub FillData()
Application.ScreenUpdating = False
RentRow = 2 'sets the first row as row 2 in the data sheet to copy from
NewRentRow = 7 'seta the row to copy to in txtTenant
Sheets("data").Select
Range("A2").Select
NoOfRows = ActiveCell.CurrentRegion.Rows.Count
For Counter = 1 To NoOfRows
If Cells(RentRow, 1) = txtTenant Then
Range(Cells(RentRow, 10), Cells(RentRow, 10)).Copy
'go to the other sheet and select the correct row
Sheets(txtTenant).Select
'selects the rent and paste to tenant sheet
Cells(NewRentRow, 2).Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
' this copies the rent down all the whilst there is data to the left
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown
Sheets("data").Select
Range("A2").Select
End If
Next Counter
End Sub
For upcoming training course dates see: Pricing & availability
Row Increment, Copy Paste Loop
You need to increment your RentRow and NewRentRow variables, by 1, to move from the first person in the "data" and also move to the next cell in the "txtTenant" sheet
1. Immediately before the End If code line add the code:
NewRentRow = NewRentRow + 1
2. Immediately before the Next Counter code line add the code:
RentRow = RentRow + 1
These code lines keep increasing the Row variables until the Counter loop ends.
|