PDA

View Full Version : Do not know how to use "Findnext" command



q1q2q3
11-01-2007, 12:44 AM
I have two sheets in a document. Each sheet has the identical number for updating the status. However, I don't know how to use findnext command for finding & updating the model number between master and client sheet.



Dim num2, firstfound As String

Dim r2, r3, c1, c2, c3, c4, c5, c6 As Range

' update information from client sheet to master sheet
Sheets("gr").Select

' check the last record no. in the sheet
finalrow2 = Range("A" & Rows.Count).End(xlUp).Row
constr = Trim(Str(finalrow2))

' For loop
For allknow = 2 To constr

'master record sheet
Sheets("sheet1").Select

' extract the item no. like " model 3/25 class 3" from master sheet
g1 = Cells(allknow, 82) ' extract model no. 3/25
g2 = Cells(allknow, 83) ' extract class 3

' match the item no from client sheet
Sheets("gr").Select
num2 = Trim(g1)
num3 = Trim(g2)

' find out all model number in specific column 8 from client sheet
Set r2 = Sheets("gr").Columns(8).Find(What:=num2, MatchCase:=True)


' match the model number and extract the row and column no
If Not r2 Is Nothing Then

address1 = r2.Row
knowrow = r2.Row

address3 = r2.Column

checknex = address3 + 1

Post = Cells(knowrow, 19) ' extract the cell content from client sheet

dd = Cells(knowrow, 20) ' extract the cell content from client sheet

xp = Trim(Cells(knowrow, address3)) ' extract the model no from client sheet

winx = Trim(Cells(knowrow, checknex)) ' extract the class no from client sheet


' match the model number between master sheet and client sheet
' match the class no between master sheet and client sheet
If num2 = xp Then If num3 = winx Then colknow = 1

Sheets("sheet1").Select


' update information from master sheet, check the cell is empty or not
While Cells(allknow, colknow) <> BLANK
colknow = colknow + 1

Wend

' update the client information into master sheet

countx = colknow + 1
county = countx - 1
Cells(allknow, county) = Post

Cells(allknow, countx) = dd

howmany = howmany + 1

End If
End If

' model number in client sheet has many transaction record, use the findnext command to dig out all the relevant model number until the end of the sheet

Do

Set r2 = Sheets("gr").Columns(8).FindNext(r2)
If Not r2 Is Nothing Then

address1 = r2.Row
knowrow = r2.Row

address3 = r2.Column

checknex = address3 + 1

Post = Cells(knowrow, 19)

dd = Cells(knowrow, 20)

xp = Trim(Cells(knowrow, address3))

winx = Trim(Cells(knowrow, checknex))

If num2 = xp Then
If num3 = winx Then
colknow = 1
Sheets("sheet1").Select
While Cells(allknow, colknow) <> BLANK
colknow = colknow + 1

Wend
countx = colknow + 1

county = countx - 1
Cells(allknow, county) = Post

Cells(allknow, countx) = dd
End If
End If
End If


' do not know how to loop the command for finding & updating all the model number in client sheet
Loop Until Not r2 Is Nothing And knowrow <= constr
End If


Next


Thank you all