| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Macro on Cell Change Error
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro on Cell Change Error
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. "David" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro on Cell Change Error
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 David wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro on Cell Change Error
And simplier still. Thanks much!
"Dave Peterson" wrote: 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 David wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro on Cell Change Error
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" wrote: 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 David wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Run Macro on Cell Change Error
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 David wrote: 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" wrote: 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 David wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
