VBA If And Then Else Not Working

[Little_Ghost]

Hi Guys,

I have this VBA code which I thought was working, but it isn't. At least, not completely.
I've spend hours trying to figure out what is wrong but I can't figure it out.
Maybe someone here can find it?

This is the code:

Sub InsertRowAndCopyFormulas()
    Dim dest As Range
    Application.ScreenUpdating = False
    If ActiveCell.Column >= F And ActiveCell.Row <= 10 Then
           
    MsgBox "Please click OK and select" _
     & vbCrLf & "a row after row 11 and" _
     & vbCrLf & "before column E."
    Else


    ActiveCell.EntireRow.Insert
    Set dest = ActiveCell.Worksheet.Cells(ActiveCell.Row, 1)
    dest.Offset(1).Resize(, dest.Worksheet.UsedRange.Columns.Count).Copy dest


    End If
    Application.ScreenUpdating = True


End Sub

It seems to neglect the column value: when I'm in F11 I can add a row, which I shouldn't be able to.

I hope someone can help me.

Kind regards,

Little Ghost

 

[Special-K99]

Just a guess

If ActiveCell.Column >= F And ActiveCell.Row <= 10 Then

Should that be "F" instead of F ?

 

[Little_Ghost]

Just a guess

If ActiveCell.Column >= F And ActiveCell.Row <= 10 Then

Should that be "F" instead of F ?

... No. Tried it to be sure, but nope :S

 

[Jonmo1]

> operations work on NUMBERS not Text Strings.

Try
If ActiveCell.Column >= 6 And ActiveCell.Row <= 10 Then

(F is the 6th column)

 

[RoryA]

Column returns a number, so:

If ActiveCell.Column >= 6 And ActiveCell.Row <= 10 Then

 

[Little_Ghost]

Then Column works but now I can just add rows, even from A1 :S

 

[RoryA]

I think you want Or not And in that line.

 

[Little_Ghost]

I think you want Or not And in that line.

Thought about that and tested it but didn't work... then again... that was with F, not 6 ... let me try...

**edit**
That seems to do the trick!
OR instead of AND , and the "Number of column" instead of "letter of column"

THANKS!!!!!