Another VBA Question

weefatbob

New Member
Joined
Sep 3, 2013
Messages
18
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hello Weefatbob,

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

Cheerio,
vcoolio.
 
Upvote 0
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
 
Last edited:
Upvote 0
Your code is currently offsetting 7 rows, do you mean 7 columns from the found cell?
If so, instead of
Code:
.[COLOR=#333333]Offset(7)[/COLOR]
try..
Code:
.[COLOR=#333333]Offset(,7)[/COLOR]
 
Last edited:
Upvote 0
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:-

Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
Which error is coming up and which line is being high-lighted in the code?

Cheerio,
vcoolio.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top