VBA debugging.

[ProphetofProfit]

First day of creating VBA code, been a lot of fun at times and a lot of pain too. Fortunately I found similar code to what I was looking to create, now it's a case of adjusting it, here's what I've got. There is an error where I've put a ''

Code:

Sub AELOv1()

Worksheets("GAMES").Activate
Application.ScreenUpdating = False

Row = 2
Column = 4

Start:
Worksheets("GAMES").Cells(Row, Column).Select
If ActiveCell.Value = "" Then GoTo Finish

'select home team
ActiveCell.Select
'search for home team in "teams"
Worksheets("TEAMS").Range("A2").Select

Do
If ActiveCell.Value = Range("A2") Then GoTo GetHomeData Else:
'work down table checking for team
ActiveCell.Offset(rowOffset:=1, columnoffset:=0).Activate
If ActiveCell.Value = "" Then GoTo Finish2
Loop

-------
What I aimed to do was find the value of a field in "GAMES" that corresponds to a team name. Then I'd go over to "TEAMS" and look up this team name in a column of teams, which starts at "A2". The Do-Loop is not my creation, but it should search through the fields from A2 downwards and stop when the right team is selected.

The error is

"Run-time error '1004': Select method of Range class failed."

 

[VoG]

You can't do that in one line of code. Try

Worksheets("TEAMS").Select
Range("A2").Select

 

[mikerickson]

If sheet "TEAMS" is not active then that line would error. Replace that one line with one of the following.

Sheets("TEAMS").Activate
Sheets("TEAMS").Range("A2").Select

or

With Sheets("TEAMS")
    .Activate
    .Range("A2").Select
End With

or

With Sheets("TEAMS").Range("A2")
    .Parent.Activate
    .Select
End With

 

[ProphetofProfit]

Thanks! So I've corrected that problem and now F8'ed my way down the page but only made it 5 lines

Code:

GetHomeData:
Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3)).Copy
'Find last cell in column with data, move one down, paste.
Worksheets("GAMES").Select
Range("BZ1").End(xlDown).Offset(1, 0).Paste

So can I copy two cells, and paste them into one cell? I do this in Excel and it automatically pastes into two cells. Hmmm. I'm trying to copy, move to "GAMES", find column BZ, then move down to the next empty cell, and then paste the data in. Can't understand why it doesn't work.

 

[VoG]

Try

Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3)).Copy Destination:=Worksheets("GAMES").Range("BZ" & Rows.Count).End(xlUp).Offset(1)

 

[ProphetofProfit]

I think I corrected this with:

Worksheets("GAMES").Select
Range("BZ1").End(xlUp).Offset(1, 0).Select
ActiveCell.PasteSpecial

I didn't use your method VoG because I don't understand 'Range("BZ" & Rows.Count)'

 

[VoG]

I didn't use your method VoG because I don't understand 'Range("BZ" & Rows.Count)'

Try it - should be marginally faster than selecting, plus you don't end up with another sheet selected.

 

[ProphetofProfit]

Speed is an issue as this process will repeat about 5000 times per worksheet, so I've gone with your method.

 

[ProphetofProfit]

Is there any value is outsourcing this VBA work to someone else? It's not for work, just for a hobby of mine, and one that should be profitable, so the cost of paying someone to write up a few lines of cost should be recouped in the long run.

 

[VoG]

Try here http://www.mrexcel.com/consult.shtml