VBA-Last Question-Hopefully

[weefatbob]

I have been trying to resolve this by researching on Google, and on here but I cant seem to find the specifics I require....I have 2 worksheets, one called Last week and 1 called current week.

I have applied autofilters to 2 of the columns on current week sheet:-

Range("A1:X1").Autofilter 15, "=Value 1"
Range("A1:X1").Autofilter 5 , "<>Value2"

What I now need to do is do a Vlookup for the first cell in column, in columns 13,14,15,16 & 17 to return the values in columns 13,14,15,16,17 on Last weeks data and for it to be overwritten in to current week columns. This will only be for the Autofiltered rows as above.

If I was doing it manually my formula would like Vlookup(A2,'Last week'A:Q,13,false)

Hope this makes sense?

 

[Norie]

What are you looking up and where are you looking for it?

Are you looking for a value from the sheet for the current week sheet in the first column of the sheet for last week?

 

[weefatbob]

What are you looking up and where are you looking for it?

Are you looking for a value from the sheet for the current week sheet in the first column of the sheet for last week?

Hi Norie
Yes, A2 is the 1st cell in the 1st filtered row of current week, I am using that to look up against last week data to enable me to replace the values in the current week columns of 13,14,15,16,17 with the data in last week columns 13,14,15,16,17.

 

[weefatbob]

bump

 

[weefatbob]

[FONT=&quot]Dont know if it'll help but this is what I am trying to do:

2 worksheets on workbook called 'Last week' and the active one which is called 'Current Week'[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]On Active sheet 'Current week'[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Filter column 15 = "Value 1"[/FONT]
[FONT=&quot]Filter column 5 <> "Value 2"[/FONT]
[FONT=&quot]then:[/FONT]
[FONT=&quot]in first active cell in column 13 do a vlookup on first active cell in column 1 to previous week sheet and if found return value in column 13 on last week sheet[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]=VLOOKUP(A2,'Last Week'!A:X,13,FALSE)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]in first active cell in column 14 do a vlookup on first active cell in column 1 to previous week sheet and if found return value in column 14 on last week sheet[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]=VLOOKUP(A2,'Last Week'!A:X,14,FALSE)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]in first active cell in column 15 do a vlookup on first active cell in column 1 to previous week sheet and if found return value in column 15 on last week sheet[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]=VLOOKUP(A2,'Last Week'!A:X,15,FALSE)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]in first active cell in column 16 do a vlookup on first active cell in column 1 to previous week sheet and if found return value in column 16 on last week sheet[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]=VLOOKUP(A2,'Last Week'!A:X,16,FALSE)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]in first active cell in column 17 do a vlookup on first active cell in column 1 to previous week sheet and if found return value in column 17 on last week sheet[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]=VLOOKUP(A2,'Last Week'!A:X,17,FALSE)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]then do this for all active rows on the filter values[/FONT]

 

[weefatbob]

Anyone?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ba-vlookup-assistance-please.html#post4942361

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

How about

Intersect(ActiveSheet.AutoFilter.Range, Range("M:M")).FormulaR1C1 = "=VLOOKUP(rc1,'Last Week'!c1:c24,13,FALSE)"

 

[weefatbob]

Fluff...apaologies for the cross post. My Bad. No waste of time intended. Will try your suggestion when I get in the ofiice.

 

[weefatbob]

How about

Intersect(ActiveSheet.AutoFilter.Range, Range("M:M")).FormulaR1C1 = "=VLOOKUP(rc1,'Last Week'!c1:c24,13,FALSE)"

Fluff....Thanks for this ...works a treat. Appreciate all the help.