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
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