If active cell found in another sheet

[axg275]

Hello,

Is there a way to write an if statement which would see if the
activecell is found in another sheet then perform the action?

Thanks

 

[Calligra]

I'm sorry the the ActiveCell function only applies to the ActiveSheet.
If you are attempting to see what cells are activated on what sheet
then you need to do

application.screenupdating = false

for isheet = 1 to 20
with sheets(isheet)
.activate
if activecells = sheets(isheet).cells(blah, blah) then
do whatever
end if
end if
next ishee

 

[axg275]

Actually I want to see if a certain column exist in a master sheet an
if it does not I would paste it into master and if it does then I woul
move on to d different column

any suggestions


Thank yo

 

[Tom Ogilvy]

If you identify the column's presence by the header in the first row then:

Dim ans as variant

ans = Application.Match("Header5",worksheets("Master").Range("A1:IV1"),0)
if not iserror(ans) then
set rng = worksheets("Master").Range("A1:IV1")(1,ans)
else
' do something else
End If

 

[axg275]

Tom,

Thank you very much it worked great!

just one more question

what does this string mean so I know

Set rng = Worksheets("Master").Range("A1:IV1")(1, ans)

??


Thank yo

 

[Tom Ogilvy]

It returns the location of where header is on the master sheet

it is a short hand method of saying

Set rng = Worksheets("Master").Range("A1:IV1").Item(1, ans)

so Item(1,ans) would be first row and nth column where nth is value of ans
for the Range starting in A1.


Worksheets("Master").Range("A1:IV1").Item(1, 1) is A1
Worksheets("Master").Range("A1:IV1").Item(1, 2) is B1
Worksheets("Master").Range("A1:IV1").Item(2, 1) is A2

for example.