I'm trying to search one file for a staff number and then copy the
information in the cells to the right into another file.

The problem is with "Cells.Find(What:=Windows("users.xls").ActiveC ell,
......."

I want to search for the contents of the currently selected cell of the
other sheet.
Another way to do this would be to copy and then search for the
contents of the clipboard but I couldn't find any examples of that
either.
I also tried to copy the number to a variable and search for the
variable name but that didn't work for me. (2nd piece of code)


Thanks for any help. I should hopefully soon have my first piece of VBA
code written!




' Switch to sheet to fill
Windows("users.xls").Activate
Range("c104").Select

' Switch to sheet to search
Windows("list.xls").Activate

' Search the sheet for staff number as selected in other sheet
Cells.Find(What:=Windows("users.xls").ActiveCell, After:="A1",
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate

' Select the cells to the right of what is found and copy
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy

' Switch to sheet to fill to paste results
Windows("users.xls").Activate

' The correct cell will be active, paste.
ActiveSheet.Paste

' Repeat

ActiveCell.Offset(1, 0).Select





' Switch to sheet to fill
Windows("users.xls").Activate
Range("c104").Select
staffnumber = ActiveCell.Value


' Switch to sheet to search
Windows("list.xls").Activate

' Search the sheet for staff number as selected in other sheet
Cells.Find(What:=staffnumber, After:="A1", LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

' Search the sheet for staff number as selected in other sheet
Cells.Find(What:=staffnumber, After:="A1", LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate


I figured it out, it was the After:="A1" part.

I'm now using

Cells.Find(Windows("users.xls").ActiveCell,
After:=ActiveSheet.Rows(1).Cells(1, 1)).Activate

though I don't really understand the (1, 1) after Cells. Why are there
2 numbers?

The code is also giving an error whenever the value is not found. I
have "On Error GoTo CatchError" in my code but I still get the error:

Run time error '91':

Object variable or With block variable not set


Again, not much from google that I can apply to my own problem. Any
ideas??

It might be better to use some variables and eliminate your selects and
activates.

Option Explicit
Sub testme()

Dim UserWks As Worksheet
Dim ListWks As Worksheet

Dim CellToFind As Range
Dim FoundCell As Range

'use the correct sheet names here.
Set UserWks = Workbooks("Users.xls").Worksheets("sheet1")
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

Set CellToFind = UserWks.Range("C104")

With ListWks
Set FoundCell = .Cells.Find(What:=CellToFind.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

If FoundCell Is Nothing Then
MsgBox "Not Found!"
Else
.Range(FoundCell.Offset(0, 1), FoundCell.End(xlToRight)).Copy _
Destination:=CellToFind.Offset(0, 1)
End If
End With

End Sub

I actually copied from one cell over from the found cell, but then I pasted to
one cell over.

alanthecat wrote:

I'm trying to search one file for a staff number and then copy the
information in the cells to the right into another file.

The problem is with "Cells.Find(What:=Windows("users.xls").ActiveC ell,
......"

I want to search for the contents of the currently selected cell of the
other sheet.
Another way to do this would be to copy and then search for the
contents of the clipboard but I couldn't find any examples of that
either.
I also tried to copy the number to a variable and search for the
variable name but that didn't work for me. (2nd piece of code)

Thanks for any help. I should hopefully soon have my first piece of VBA
code written!

' Switch to sheet to fill
Windows("users.xls").Activate
Range("c104").Select

' Switch to sheet to search
Windows("list.xls").Activate

' Search the sheet for staff number as selected in other sheet
Cells.Find(What:=Windows("users.xls").ActiveCell, After:="A1",
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate

' Select the cells to the right of what is found and copy
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy

' Switch to sheet to fill to paste results
Windows("users.xls").Activate

' The correct cell will be active, paste.
ActiveSheet.Paste

' Repeat

ActiveCell.Offset(1, 0).Select

' Switch to sheet to fill
Windows("users.xls").Activate
Range("c104").Select
staffnumber = ActiveCell.Value

' Switch to sheet to search
Windows("list.xls").Activate

' Search the sheet for staff number as selected in other sheet
Cells.Find(What:=staffnumber, After:="A1", LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

--

Dave Peterson

Thanks. I've tried to change that so it will loop for as long as there
are staff numbers in the document I'm trying to fill but I'm now
getting a error

"Subscript out of range."

and it's returning to
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

Is Option Explicit necessary?

TIA



Sub testme()

Dim UserWks As Worksheet
Dim ListWks As Worksheet

Dim notfound As Integer
Dim CurrentRow As Integer

Dim CellToFind As Range
Dim FoundCell As Range


Set UserWks = Workbooks("Users.xls").Worksheets("sheet1")
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

CurrentRow = 104

Set CellToFind = UserWks.Cells(CurrentRow, 3)

Do While IsNumeric(CellToFind.Value) = True

With ListWks
Set FoundCell = .Cells.Find(What:=CellToFind,
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If FoundCell Is Nothing Then
notfound = notfound + 1
Else
.Range(FoundCell.Offset(0, 1),
FoundCell.End(xlToRight)).Copy Destination:=CellToFind.Offset(0, 1)

End If
End With

CurrentRow = CurrentRow + 1

Loop

MsgBox ("Not Found " & notfound)

End Sub

Option explicit
isn't necessary for your code to run. But it's very useful. It tells excel
that you're going to declare all your variables. Then when you make a mistake:

When this doesn't work like it should
myCtr1 = myCtrl + 1
(one of those has an ELL and one has a ONE)
It really makes debugging easier.

As for the subscript out of range...

Do you have a workbook named List.xls open when you run this macro?

If no, you have to open it.

If yes, does that List.xls workbook have a worksheet named Sheet1?

(Change Sheet1 to what it should be in List.xls.)

Don't forget to do the same with the Users.xls line.



alanthecat wrote:

Thanks. I've tried to change that so it will loop for as long as there
are staff numbers in the document I'm trying to fill but I'm now
getting a error

"Subscript out of range."

and it's returning to
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

Is Option Explicit necessary?

TIA

Sub testme()

Dim UserWks As Worksheet
Dim ListWks As Worksheet

Dim notfound As Integer
Dim CurrentRow As Integer

Dim CellToFind As Range
Dim FoundCell As Range

Set UserWks = Workbooks("Users.xls").Worksheets("sheet1")
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

CurrentRow = 104

Set CellToFind = UserWks.Cells(CurrentRow, 3)

Do While IsNumeric(CellToFind.Value) = True

With ListWks
Set FoundCell = .Cells.Find(What:=CellToFind,
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If FoundCell Is Nothing Then
notfound = notfound + 1
Else
.Range(FoundCell.Offset(0, 1),
FoundCell.End(xlToRight)).Copy Destination:=CellToFind.Offset(0, 1)

End If
End With

CurrentRow = CurrentRow + 1

Loop

MsgBox ("Not Found " & notfound)

End Sub

--

Dave Peterson

Ps, I think you want to copy this line, too:

Set CellToFind = UserWks.Cells(CurrentRow, 3)

=====

Do While IsNumeric(CellToFind.Value) = True

With ListWks
Set FoundCell = .Cells.Find(What:=CellToFind,
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If FoundCell Is Nothing Then
notfound = notfound + 1
Else
.Range(FoundCell.Offset(0, 1), FoundCell.End(xlToRight)).Copy _
Destination:=CellToFind.Offset(0, 1)
End If
End With

CurrentRow = CurrentRow + 1
set celltofind = userwks.cells(currentrow,3)

Loop

=======
And change these to long's:

Dim notfound As Integer
Dim CurrentRow As Integer

Dim notfound As Long
Dim CurrentRow As Long

It makes less work for the computer and you won't have to worry about going past
32767 (and blowing up).



alanthecat wrote:

Thanks. I've tried to change that so it will loop for as long as there
are staff numbers in the document I'm trying to fill but I'm now
getting a error

"Subscript out of range."

and it's returning to
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

Is Option Explicit necessary?

TIA

Sub testme()

Dim UserWks As Worksheet
Dim ListWks As Worksheet

Dim notfound As Integer
Dim CurrentRow As Integer

Dim CellToFind As Range
Dim FoundCell As Range

Set UserWks = Workbooks("Users.xls").Worksheets("sheet1")
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

CurrentRow = 104

Set CellToFind = UserWks.Cells(CurrentRow, 3)

Do While IsNumeric(CellToFind.Value) = True

With ListWks
Set FoundCell = .Cells.Find(What:=CellToFind,
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If FoundCell Is Nothing Then
notfound = notfound + 1
Else
.Range(FoundCell.Offset(0, 1),
FoundCell.End(xlToRight)).Copy Destination:=CellToFind.Offset(0, 1)

End If
End With

CurrentRow = CurrentRow + 1

Loop

MsgBox ("Not Found " & notfound)

End Sub

--

Dave Peterson

Dim notfound As Long
Dim CurrentRow As Long


Option explicit
isn't necessary for your code to run. But it's very useful. It
tells excel that you're going to declare all your variables.


As for the subscript out of range...

Do you have a workbook named List.xls open when you run this macro?

If no, you have to open it.

If yes, does that List.xls workbook have a worksheet named Sheet1?



Ok, I changed Integer to Long. I see why though this spreadsheet is
only a few hundred lines long. Good to know anyway.

I tried to put Option Explicit in. When I put it above the Sub name()
it placed VBA made it seem as though it was in the macro above and when
I put it below the Sub name VBA gave me an error. I just cut it out
again.

The subscript out of range error was being caused by the sheets name
having been changed. Fixed it but now.....





When the script reaches the first cell that doesn't have a match in the
second document it seems to just keep on trying to perform the function
on that cell and when i press escape I get:

Run-time error '1004':

Unable to get the Find property of the Range class.

And on debug I'm brought to:

Set FoundCell = .Cells.Find(What:=CellToFind.Cells)

I cut out everything after what:= to see if it was them causing the
problem.

I tried to surround the find function with
On Error Resume Next
On Error GoTo 0

But that just gave me a new error.

This stuff is harder that I expected to get working. But way more fun
than doing this job manually (which is what they expected me to do!)


Thanks again.

Option Explicit
is a module level directive.

It should go at the top of the module -- before any Sub. And it only appears
once in that module.

Maybe you're having trouble with empty cells.

Does this version work?

Option Explicit
Sub testme()

Dim UserWks As Worksheet
Dim ListWks As Worksheet

Dim notfound As Long
Dim CurrentRow As Long

Dim CellToFind As Range
Dim FoundCell As Range

Set UserWks = Workbooks("Users.xls").Worksheets("sheet1")
Set ListWks = Workbooks("list.xls").Worksheets("sheet1")

CurrentRow = 104

Set CellToFind = UserWks.Cells(CurrentRow, 3)

Do

If IsEmpty(CellToFind) _
Or IsNumeric(CellToFind.Value) = False Then
Exit Do
End If

With ListWks
Set FoundCell = .Cells.Find(What:=CellToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

If FoundCell Is Nothing Then
notfound = notfound + 1
Else
.Range(FoundCell.Offset(0, 1), FoundCell.End(xlToRight)).Copy _
Destination:=CellToFind.Offset(0, 1)

End If
End With

CurrentRow = CurrentRow + 1

Set CellToFind = UserWks.Cells(CurrentRow, 3)

Loop

MsgBox ("Not Found " & notfound)

End Sub

If it doesn't help, post the code you're currently using.

alanthecat wrote:

Dim notfound As Long
Dim CurrentRow As Long

Option explicit
isn't necessary for your code to run. But it's very useful. It
tells excel that you're going to declare all your variables.

As for the subscript out of range...

Do you have a workbook named List.xls open when you run this macro?

If no, you have to open it.

If yes, does that List.xls workbook have a worksheet named Sheet1?

Ok, I changed Integer to Long. I see why though this spreadsheet is
only a few hundred lines long. Good to know anyway.

I tried to put Option Explicit in. When I put it above the Sub name()
it placed VBA made it seem as though it was in the macro above and when
I put it below the Sub name VBA gave me an error. I just cut it out
again.

The subscript out of range error was being caused by the sheets name
having been changed. Fixed it but now.....

When the script reaches the first cell that doesn't have a match in the
second document it seems to just keep on trying to perform the function
on that cell and when i press escape I get:

Run-time error '1004':

Unable to get the Find property of the Range class.

And on debug I'm brought to:

Set FoundCell = .Cells.Find(What:=CellToFind.Cells)

I cut out everything after what:= to see if it was them causing the
problem.

I tried to surround the find function with
On Error Resume Next
On Error GoTo 0

But that just gave me a new error.

This stuff is harder that I expected to get working. But way more fun
than doing this job manually (which is what they expected me to do!)

Thanks again.

--

Dave Peterson