Another VBA Question

[weefatbob]

Morning all

I am looking for VBA to enable me to search my spreadsheet where the 'Grade' column is equal to 'APP'

I have the autofilter as Sub check3() Range("A:1:X1").Autofilter6,"=APP"

I know I may not need this, but want to leave it in, to let users see it has worked. What I am looking to do is

If Grade =APP then Supervisor ID = "Value 1", Supervisor Forename = "Value 2" Supervisor Surname = "Value 3" Supervisor mail - "Value 4" and Supervisor Dept = "Value 5"

Hope this make sense.

 

[vcoolio]

Hello Weefatbob,

Once the data has been filtered on Column F, are you transferring the filtered data to another sheet?

Cheerio,
vcoolio.

 

[weefatbob]

Hi Vcoolio, No, I am replacing the values that are in the columns following filter in the columns for the supervisor data with Values 1,2,3,4,5

So basically what I have tried is:

Range("A1:X1").Autofilter 6, "=APP"
Columns(6).Find("APP").Offset(7).Value="Value1"
Columns(6).Find("APP").Offset(8).Value="Value2"

Continuing moving along one column until I update Value 5

 

[Yongle]

Your code is currently offsetting 7 rows, do you mean 7 columns from the found cell?
If so, instead of

.[COLOR=#333333]Offset(7)[/COLOR]

try..

.[COLOR=#333333]Offset(,7)[/COLOR]

 

[vcoolio]

Hello Weefatbob,

If you are looking at populating the next five columns(G:K) with "Value1", "Value2" etc... once "App" is filtered in Column F then this code may work for you:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.Range("F1", Range("F" & Rows.Count).End(xlUp))
        .AutoFilter 1, "App"
        Range(.Offset(1, 1), .Offset(1, 5)) = Array("Value1", "Value2", "Value3", "Value4", "Value5")
        .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[weefatbob]

VCoolio, Thanks dude, but when I tried this I got a debug error, So I tried Yongle's suggestion which has worked a treat for one row, how do I get it to do for all the rows in the filter?

Thanks t you both so far.

 

[vcoolio]

Which error is coming up and which line is being high-lighted in the code?

Cheerio,
vcoolio.

 

[weefatbob]

Which error is coming up and which line is being high-lighted in the code?

Cheerio,
vcoolio.

Its working fine, was me Changed sheet 1 to ActiveSheet as I didn't tell you had 2 sheets...So thanks to you too....Same question as before tho, how do I run it for all rows in the filter not just the 1st one?

 

[vcoolio]

The code in post #5 will do just that.
For example, if, when filtered, there are only three rows showing "App" in Column F, then those three rows from Column G to Column K will show the values "Value1", "Value2" etc..

Cheerio.
vcoolio.

 

[weefatbob]

The code in post #5 will do just that.
For example, if, when filtered, there are only three rows showing "App" in Column F, then those three rows from Column G to Column K will show the values "Value1", "Value2" etc..

Cheerio.
vcoolio.

Dude, Thank you very much for your help with this, furthered my education.