Nested do while loop with vlookup

[falco008]

Hi all

first post here, just started with VBA, not very good at it

The goal :


i have data like this :



Week HomeTeam Awayteam Helpcolumnhometeam Helpcolumnawayteam HelpHT-1 HelpAT-1
1 arsenal manchester arsenal1 manchester1 arsenal0 manchester0
2 tottenham arsenal tottenham2 arsenal2 tottenham1 arsenal1
3 arsenal chelsea arsenal3 chelsea3 arsenal2 chelsea2

-- I made 4 helpercolumns because concatenating in VBA is too difficult for me

what i try to do is the following

take week 3 : arsenal is playing home , i want to lookup the old arsenal rating in a range ai , so i need to check if arsenal2 exists either in column helpht-1 ( if their previous match was a home match) or helptat-1 if their previous match is an away match-- so these two columns are basically concatenate Week-1&"hometeam" (note sometimes teams havent played the week before so we need to check -2, but this is complexity i cannot even grasp right now --
if im able to find the column i start a vlookup either with starting range Helpht-1 or starting range helpAT-1

my current code is not giving the right results

i replaced the actual lookups with test values am not sure what the problem is but the running the code gives either a columns with all values "5" or all values "1000"
which shouldnt happen , per row it should check if the value is found in column AI , then execute the first lookup (replaced by value 5) or not then execute the other lookup (replaced by value 1000)



help GREATLY appreciated

Please Login or Register  to view this content.

[6StringJazzer]

First, it would help if you describe what you are trying to do, rather than give a detailed step-by-step description of what your Excel is doing. For example, you say, "i want to lookup the old arsenal rating in a range ai". What information is in range AI? You appear to be looking up only the previous week, please explain why.

Also please attach your file so we can see your data and test updates to your code. To attach a file:

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

Here are step-by-step instructions with screen shots