Run Macro on Cell Change Error

[Guest]

I am getting a Type Mismatch error on this macro. The data in Column A is
text (names). When a name is changed, I want the macro to run. Help please.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
If Range("A1:A20") <> "" Then 'The error occurs here'
Sheets(2).Select
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A20"),
Type:=xlFillDefault
Range("A1:A20").Select
Range("A1").Select
End If
End If
End Sub

 

[Guest]

GOT IT!! Simplier is better!
This is what works..
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Call FillCells
End If
End Sub

I'm using this to automatically update an offset formula on sheet2 so when
new rows are entered or data changed in Column A (which is the offset data on
sheet two I want, it automatically updates everthing on sheet2 to match
sheet1.

 

[Dave Peterson]

The default property for a range is .value.

So this:

If Range("A1:A20") <> "" Then
is equivalent to:
If Range("A1:A20").value <> "" Then

And there are 20 values in that array of values.

Maybe:

if application.counta(range("a1:a20")) <> 0 then

if the cells are really empty.

======
And you're gonna have some trouble with the .select's on sheets(2). When
unqualified ranges are used in a general module, they refer to the activesheet.
But when they're used in a worksheet module, they refer to the worksheet that
owns the code.

That means you're selecting sheets(2), but then trying to select A1 on the sheet
with the code--that'll blow up because you can only select cells on the
activesheet.

You may want to try it without the .select's:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A20")) Is Nothing Then
If Application.CountA(Me.Range("A1:A20")) <> 0 Then
With Sheets(2)
.Range("a1").AutoFill _
Destination:=.Range("A1:A20"), Type:=xlFillDefault
End With
End If
End If
End Sub

But if you want...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
If Application.CountA(Range("A1:A20")) <> 0 Then
Sheets(2).Select
Sheets(2).Range("A1").Select
Selection.AutoFill Destination:=Sheets(2).Range("A1:A20"), _
Type:=xlFillDefault
Sheets(2).Range("A1:A20").Select
Sheets(2).Range("A1").Select
End If
End If
End Sub

 

[Guest]

And simplier still. Thanks much!

The default property for a range is .value.

So this:

If Range("A1:A20") <> "" Then
is equivalent to:
If Range("A1:A20").value <> "" Then

And there are 20 values in that array of values.

Maybe:

if application.counta(range("a1:a20")) <> 0 then

if the cells are really empty.

======
And you're gonna have some trouble with the .select's on sheets(2). When
unqualified ranges are used in a general module, they refer to the activesheet.
But when they're used in a worksheet module, they refer to the worksheet that
owns the code.

That means you're selecting sheets(2), but then trying to select A1 on the sheet
with the code--that'll blow up because you can only select cells on the
activesheet.

You may want to try it without the .select's:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1:A20")) Is Nothing Then
If Application.CountA(Me.Range("A1:A20")) <> 0 Then
With Sheets(2)
.Range("a1").AutoFill _
Destination:=.Range("A1:A20"), Type:=xlFillDefault
End With
End If
End If
End Sub

But if you want...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
If Application.CountA(Range("A1:A20")) <> 0 Then
Sheets(2).Select
Sheets(2).Range("A1").Select
Selection.AutoFill Destination:=Sheets(2).Range("A1:A20"), _
Type:=xlFillDefault
Sheets(2).Range("A1:A20").Select
Sheets(2).Range("A1").Select
End If
End If
End Sub

 

[Guest]

You were right about the call macro as well. I found that and changed it to
this (now looking at columns A & B on sheet1) It works, but I want to test
yours as well. This also brings me back to sheet1 so I can enter data in the
row inserted. The changes in the cells are updated by the OFFSET formula,
which is in A1 on sheet2,
=IF(Sheet1!A1="","",OFFSET(NameList,0,0))
Thanks again so much!


Sub FillCells()
'
' FillCells Macro
' Macro recorded 12/18/2005 by David L Perkins
'

'
Application.ScreenUpdating = False
Sheets(2).Select
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A5000"), Type:=xlFillDefault
Range("A1:A5000").Select
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B5000"), Type:=xlFillDefault
Range("B1:A5000").Select
Range("A1").Select
Sheets(1).Select
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

I'm not sure how the formula fits in, but you may want to try the same
autofilling without the .selecting:

Option Explicit
Sub FillCells2()
Application.ScreenUpdating = False
With Sheets(2)
.Range("A1").AutoFill _
Destination:=.Range("A1:A5000"), Type:=xlFillDefault
.Range("B1").AutoFill _
Destination:=.Range("B1:B5000"), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub