If active cell found in another sheet

A

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
 
C

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
 
A

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
 
T

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
 
A

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
 
T

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.
 
Top