ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Getting an address - help (https://www.excelbanter.com/excel-programming/368127-getting-address-help.html)

aagray
Getting an address - help
 

Hello,

I've been away from VBA for awhile and I have forgotten how to get the
address for a cell.

Following is a brief beginning to a procedure. I'm trying to go from
one file to another - getting the information from the first file and
then going to the next file to get information contained on the second
file. For instance, the vendor and product from the first and then
going to the next file to get the vendor tab and the product in that
tab. After that, I want to offset from the procut, get the price and
bring it back to the first file.

I think that I need to use address to do this, but I'm not positive.

Would anybody be able to help me out with this.

Thanks much in advance.

Anita





Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress
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
Columns("f:f").Find(What:=myProduct, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
myAddress = ActiveCell
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 ***

Bob Phillips
Getting an address - help
 
I wouldn't use Find like that, I would use it to set a range object
variable, you can then just refer to that variable at any time thereafter

Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If


--
HTH

Bob Phillips

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

"aagray" wrote in message
...

Hello,

I've been away from VBA for awhile and I have forgotten how to get the
address for a cell.

Following is a brief beginning to a procedure. I'm trying to go from
one file to another - getting the information from the first file and
then going to the next file to get information contained on the second
file. For instance, the vendor and product from the first and then
going to the next file to get the vendor tab and the product in that
tab. After that, I want to offset from the procut, get the price and
bring it back to the first file.

I think that I need to use address to do this, but I'm not positive.

Would anybody be able to help me out with this.

Thanks much in advance.

Anita





Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress
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
Columns("f:f").Find(What:=myProduct, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
myAddress = ActiveCell
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 ***

a
Getting an address - help
 
Hello Bob,

Thanks for the code. I tried it, however, and got "named arguement not
found". Would you know why that would be?

Following is how I incorporated your code.

Thanks in advance,
Anita

Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress
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, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If

myAddress = ActiveCell
Windows("trial.xls").Activate


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

k = k + 1

Loop
End Sub


Bob Phillips wrote:

I wouldn't use Find like that, I would use it to set a range object
variable, you can then just refer to that variable at any time thereafter

Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If

Bob Phillips
Getting an address - help
 
where do you get that error?

--
HTH

Bob Phillips

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

"a" wrote in message
nk.net...
Hello Bob,

Thanks for the code. I tried it, however, and got "named arguement not
found". Would you know why that would be?

Following is how I incorporated your code.

Thanks in advance,
Anita

Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress
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, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If

myAddress = ActiveCell
Windows("trial.xls").Activate


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

k = k + 1

Loop
End Sub


Bob Phillips wrote:

I wouldn't use Find like that, I would use it to set a range object
variable, you can then just refer to that variable at any time
thereafter

Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If

a
Getting an address - help
 
Oops, forgot to mention that part. The error came at this point:

If Not cell Is Nothing Then



ob Phillips (replace somewhere in email address with gmail if mailing
direct) "a" wrote in message
nk.net...

Hello Bob,

Thanks for the code. I tried it, however, and got "named arguement not
found". Would you know why that would be?

Following is how I incorporated your code.

Thanks in advance,
Anita


Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress
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, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If

myAddress = ActiveCell
Windows("trial.xls").Activate


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

k = k + 1

Loop
End Sub



Bob Phillips wrote:


I wouldn't use Find like that, I would use it to set a range object
variable, you can then just refer to that variable at any time

thereafter


Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
ater:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
Windows("trial.xls").Activate
End If






Bob Phillips wrote:

where do you get that error?


All times are GMT +1. The time now is 01:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com