msmith
05-16-2005, 10:59 AM
Hello-
I need a search/find structure. I have an import sheet that has numerical values for certain samples. I also have a 'results' sheet. These are in different workbooks. I would like the search to take the samples names from the 'results' page (which are in a single column) and search for the corresponding numerical values from the import sheet. I have some code for the search but cannot get the loop to work. This code is pretty dirty since I am relatively new to vb so I'm sure it should be improved. I have included the base code.
Thanks in advance,
Mark
Dim qsearch As String
Dim Cel As Range
Dim temp2 As Variant
Dim temp1 As Variant
Dim FirstAddress As String
Dim n As Integer
For n = 69 To 110
With Range("B:B")
Windows("CForm.xls").Activate
qsearch = Sheets("Q").Range("L" & n).Value
Set Cel = .Find(What:=qsearch, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
temp1 = Cel.Address
Windows(wbtemp).Activate 'import sheet
Range(temp1).Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=1).Activate
temp2 = ActiveCell.Value
Windows("CForm.xls").Activate
Sheets("Q").Select
Range("M" & n).Value = temp2
Set Cel = .FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
End If
End With
Next n
I need a search/find structure. I have an import sheet that has numerical values for certain samples. I also have a 'results' sheet. These are in different workbooks. I would like the search to take the samples names from the 'results' page (which are in a single column) and search for the corresponding numerical values from the import sheet. I have some code for the search but cannot get the loop to work. This code is pretty dirty since I am relatively new to vb so I'm sure it should be improved. I have included the base code.
Thanks in advance,
Mark
Dim qsearch As String
Dim Cel As Range
Dim temp2 As Variant
Dim temp1 As Variant
Dim FirstAddress As String
Dim n As Integer
For n = 69 To 110
With Range("B:B")
Windows("CForm.xls").Activate
qsearch = Sheets("Q").Range("L" & n).Value
Set Cel = .Find(What:=qsearch, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
temp1 = Cel.Address
Windows(wbtemp).Activate 'import sheet
Range(temp1).Activate
ActiveCell.Offset(rowOffset:=1, columnOffset:=1).Activate
temp2 = ActiveCell.Value
Windows("CForm.xls").Activate
Sheets("Q").Select
Range("M" & n).Value = temp2
Set Cel = .FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
End If
End With
Next n