locate a value from file1 in file2

[Gary]

I am trying to locate a value from file1 in file2 and
then copy a number to a cell in the same row. The copy
part works but it doesn't seem to locate the appropriate
line. See below. It ends up copying the value to column L
on the bottom of the data in file2. Help.


Sub sale2004()

Windows("file1.xlt").Activate
Dim rngCell As Excel.Range
Set rngCell = ActiveSheet.Range("F3")
rngCell.Select
Windows("file2.xls").Activate
Dim FndRng As Excel.Range
Set FndRng = ActiveSheet.Range("S:S").Find(rngCell)
ActiveCell.Offset(0, 11).Activate
........

 

[Dick Kusleika]

Gary

I don't see where you're copying anything at all.

Two things to note:
You generally don't need to active or select anything in order to work with
it. You might rewrite your fragment below as

Dim rngCell as Range
Dim FndRng as Range

Set rngCell = Workbooks("file1.xlt").ActiveSheet.Range("F3")
Set FndRng = ActiveSheet.Range("S:S").Find(rngCell.Value)

Second, when you don't specify all of the arguments for the Find method, it
assumes the arguments' values from the last time you did a Find, either from
VBA or the user interface. So if you specify to find a partial cell value,
then use Find without specifying the LookAt argument, it will still look at
partial values. You should always specify all the arguments in Find, unless
you know it doesn't matter.

 

[Gary]

Dick:
I didn't include the copy part as it seemed to work ok. I
revised it as you suggested and still get the same
result. The revised sub is as follows:
Sub sale2004()

Windows("File1.xlt").Activate
Dim rngCell As Range
Dim FndRng As Range
Set rngCell = Workbooks("File1.xlt").ActiveSheet.Range
("F3")
Windows("File2.xls").Activate
Set FndRng = ActiveSheet.Range("S:S").Find
(rngCell.Value)
ActiveCell.Offset(0, 11).Activate
Windows("File1.xlt").Activate
Dim rngCell1 As Excel.Range
Set rngCell1 = ActiveSheet.Range("d3")
rngCell1.Select
Selection.Copy
Windows("File2.xls").Activate
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

 

[Dick Kusleika]

Gary

Let me make sure I understand what you're doing, correct me if I'm wrong:
You want to find the value on from file1!F3 somewhere in Col S of File2. If
you find it, you want to copy that value (only the value) from file1!D3 to a
cell 11 columns over from where the file was found on the same row. If
that's right, here's the sub you need

Sub CopyValue()

Dim rngCell as Range
Dim FndRng as Range

'Set the cell that contains the value to find
Set rngCell = Workbooks("File1.xlt").ActiveSheet.Range("F3")

'Find the cell in S with that value. If found, FndRng will be a reference
to the cell. It
'does not change the ActiveCell. If not found, FndRng will = Nothing.
Set FndRng =
Workbooks("File2.xlt").ActiveSheet.Range("S:S").Find(rngCell.Value)

'If a cell with that value was found
If Not FndRng Is Nothing Then

'Make the value of the cell 11 columns over from the found cell equal
'to the value from D3. rngCell.Parent is a reference to the worksheet
'that contains the value in F3. It's a shortcut so you don't have to
'type the whole reference.
FndRng.Offset(0,11).Value = rngCell.Parent.Range("D3").Value
End If

'If a cell as not found, do nothing

End Sub

I use ActiveSheet when setting the Range variables, because I don't the
structure of your workbooks. If you know the sheet names and you can rely
on them not changing, you should use the literal sheet names. You would
change

Workbooks("File1.xlt").ActiveSheet.Range("F3")

to

Workbooks("File1.xlt").Sheets("Sheet1").Range("F3")

There's no problem using ActiveSheet, so long as you can rely on the correct
sheet being active. If there's only one sheet, then you have no worries.

The above sub is all you need. You don't need to Activate windows or Select
ranges - and it is preferable not to.

 

[Gary]

Dick:
Your understanding is correct. I tried your sub (the only
things I changed were the file names) and it still cannot
find the value of F3. I checked the locals window and it
appears to be looking for the correct value but cannot
find it.
Any suggestions?
Thanks

 

[Dick Kusleika]

Gary

It sounds like the value doesn't exist in column S. Are you sure that it
does? For instance, if your looking for "MyValue" and the cell contains
"MyValue ", you won't see those extra spaces, but they are there and it
won't "find" it because of them.

 

[Gary]

Dick:
I checked and found a duplicate record. It was copying
the value properly but not on the line that I was
checking.
Thanks,
Gary