Mike
Got it to work by changing code to
Sub Test()
Dim wb1 As Workbook
Dim ws2 As Worksheet ' change
Dim x As Integer
Dim findrng As Range
Set wb1 = Workbooks("Transaction.xls")
Set ws2 = Workbooks("Statements.xls").Worksheets("Data") ' change
Set findrng = ws2.Range("E5:E65536")
' change
x = WorksheetFunction.Match("Salary", findrng, 0)
wb1.Worksheets("Summary").Range("C19").Value = _
ws2.Range(findrng.Item(x).Address).Offset(0, -4).Value ' change
End Sub
--
Thank you for putting me on the right track.
Bob C
It cost's little to carry knowledge with you.
"Mike Fogleman" wrote:
> You are right, I didn't test it either (shame on me). I didn't qualify wb2
> as the source:
>
> Sub Test()
>
> Dim wb1 As Workbook, wb2 As Workbook
> Dim x As Integer
> Dim findrng As Range
>
> Set wb1 = Workbooks("Transactions.xls")
> Set wb2 = Workbooks("Statements.xls")
> Set findrng = wb2.Worksheets("Data").Range("E1:E65536")
> x = WorksheetFunction.Match("Salary", findrng, 0)
>
> wb1.Worksheets("Summary").Range("C19").Value = _
> wb2.Range(findrng.Item(x).Address).Offset(0, -4).Value
>
> End Sub
>
> Mike F
>
> "Bob C" <(E-Mail Removed)> wrote in message
> news:37766A89-70C5-4C93-A344-(E-Mail Removed)...
> > Mike it's not working.
> >
> > It's not getting the offset value from Column A in wb2.
> > It just copies cell A5 in wb1 to cell c19 in the same wb1.
> > It's not referencing wb2 at all.
> >
> > Sorry for my second post it looked OK but I had not tested.
> >
> > Bob C
> > It cost's little to carry knowledge with you.
> >
> >
> > "Mike Fogleman" wrote:
> >
> >> Thanks for the update, glad it's working. As you can see, Copy/Paste is
> >> not
> >> necessary to duplicate data. Just assign the value to the range. Reserve
> >> Copy/Paste functions for transferring things like cell formatting or
> >> filtered lists, etc.
> >>
> >> Mike F
> >> "Bob C" <(E-Mail Removed)> wrote in message
> >> news:66667CAB-5D68-4D1E-8FCA-(E-Mail Removed)...
> >> > Mike
> >> > This is the code with correct W/book & sheet names inserted.
> >> > It is copying Cell A5 on the same sheet into C19.
> >> >
> >> > Sub Test()
> >> >
> >> > Dim wb1 As Workbook, wb2 As Workbook
> >> > Dim x As Integer
> >> > Dim findrng As Range
> >> >
> >> > Set wb1 = Workbooks("Transactions.xls")
> >> > Set wb2 = Workbooks("Statements.xls")
> >> > Set findrng = wb2.Worksheets("Data").Range("E1:E65536")
> >> > x = WorksheetFunction.Match("Salary", findrng, 0)
> >> >
> >> > wb1.Worksheets("Summary").Range("C19").Value = _
> >> > Range(findrng.Item(x).Address).Offset(0, -4).Value
> >> >
> >> > End Sub
> >> > --
> >> > Thank you
> >> >
> >> > Bob C
> >> > It cost''''''''s little to carry knowledge with you.
> >> >
> >> >
> >> > "Mike Fogleman" wrote:
> >> >
> >> >> Sub Test()
> >> >> Dim wb1 As Workbook, wb2 As Workbook
> >> >> Dim x As Integer
> >> >> Dim findrng As Range
> >> >>
> >> >> Set wb1 = Workbooks("Book1")
> >> >> Set wb2 = Workbooks("Book2")
> >> >> Set findrng = wb2.Range("E1:E65536")
> >> >> x = WorksheetFunction.Match("Sale", findrng, 0)
> >> >> wb1.Worksheets("Sheet8").Range("C19").Value =
> >> >> Range(findrng.Item(x).Address).Offset(, -4).Value
> >> >> End Sub
> >> >>
> >> >> Mike F
> >> >> "Bob C" <(E-Mail Removed)> wrote in message
> >> >> news:BDC98BEE-D131-4DD3-83DF-(E-Mail Removed)...
> >> >> > Hi
> >> >> >
> >> >> > Using Office 2003
> >> >> >
> >> >> > I'm trying to find in W/book2, Sheet1 Column E the first row
> >> >> > containing
> >> >> > the
> >> >> > word Sale.
> >> >> > Then in Column A same row, copy cells date value and paste value
> >> >> > into
> >> >> > W/book1 Sheet8 cell C19.
> >> >> >
> >> >> > TIA
> >> >> > --
> >> >> > Thank you
> >> >> >
> >> >> > Bob C
> >> >> > It cost''s little to carry knowledge with you.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
Similar Threads
