I'm trying to get the address of a cell that I'm using in a "find".
This seems to work on the first pass but then the address doesn't seem
to change. I know the following code is a bit odd - but it is a work in
progress.

If anybody can help with this, it would be much appreciated.

Thanks in advance,
Anita


Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress As Range, myPrice
Dim myVendor As String, myProduct
Dim m
j = 1
k = 1
l = 2
Windows("trial.xls").Activate
Do Until Cells(k, j) = ""

If Cells(k, j).Value = "f" Then
myVendor = Cells(k, j).Offset(0, 6).Value
myProduct = Cells(k, j).Offset(0, 7).Value
Cells(k, 2).Value = myVendor
Cells(k, 3).Value = myProduct
Windows("Code.xls").Activate
Workbooks("Code.xls").Sheets(myVendor).Select
Columns("F:F").Select
Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
after:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set myAddress = ActiveCell
myAddress.Activate
myPrice = ActiveCell.Offset(0, 1).Value

If Not cell Is Nothing Then
Windows("trial.xls").Activate
Cells(k, 12).Value = myPrice
End If
Windows("trial.xls").Activate
Cells(k, 12).Value = myPrice

Windows("trial.xls").Activate



Else
Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
End If

k = k + 1

Loop
End Sub

A

*** Sent via Developersdex http://www.developersdex.com ***

This bit is redundant AFAICS

Set myAddress = ActiveCell
myAddress.Activate
myPrice = ActiveCell.Offset(0, 1).Value


you should use cell which refers to the found cell

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"aagray" wrote in message
...
I'm trying to get the address of a cell that I'm using in a "find".
This seems to work on the first pass but then the address doesn't seem
to change. I know the following code is a bit odd - but it is a work in
progress.

If anybody can help with this, it would be much appreciated.

Thanks in advance,
Anita


Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress As Range, myPrice
Dim myVendor As String, myProduct
Dim m
j = 1
k = 1
l = 2
Windows("trial.xls").Activate
Do Until Cells(k, j) = ""

If Cells(k, j).Value = "f" Then
myVendor = Cells(k, j).Offset(0, 6).Value
myProduct = Cells(k, j).Offset(0, 7).Value
Cells(k, 2).Value = myVendor
Cells(k, 3).Value = myProduct
Windows("Code.xls").Activate
Workbooks("Code.xls").Sheets(myVendor).Select
Columns("F:F").Select
Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
after:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Set myAddress = ActiveCell
myAddress.Activate
myPrice = ActiveCell.Offset(0, 1).Value

If Not cell Is Nothing Then
Windows("trial.xls").Activate
Cells(k, 12).Value = myPrice
End If
Windows("trial.xls").Activate
Cells(k, 12).Value = myPrice

Windows("trial.xls").Activate



Else
Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
End If

k = k + 1

Loop
End Sub

A

*** Sent via Developersdex http://www.developersdex.com ***