Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I avoid excel change absolute address to relative address | Excel Discussion (Misc queries) | |||
long address list, name-address-city, listed vertically, how do y. | Excel Discussion (Misc queries) | |||
How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) | |||
How to insert an address from Outlook 2003 address book ? | Excel Programming | |||
LINKING Address cells from an EXCEL spreadsheet to fill MapQuest Address Info | Excel Programming |