I always have a problem with that. After reading your post again
I believe this will work fine:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
With Target(1, -2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
If Target = Range("H7") Then
If UCase(Range("H7")) = "YES" Then
Sheets("Old in").Range("F7").Copy
Sheets("Redeployment").Range("A4")
ElseIf UCase(Range("H7")) = "NO" Then
Sheets("Old in").Range("F7").Copy Sheets("Disposal").Range("A4")
End If
End If
End Sub
"jcurless" wrote:
> I tried it but here is the error it returned:
> Run-time error '91':
> Object variable or With block vriable not set
>
> I clicked Debug and this is the line it highlighted:
> If Target = Intersect(Target, Range("H3:H" & Cells(Rows.Count,
> 8).End(xlUp).Row)) Then
>
>
>
> "JLGWhiz" wrote:
>
> > This modifies your current worksheet change code:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Cells.Count > 1 Then Exit Sub
> > If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
> > With Target(1, -2)
> > .Value = Date
> > .EntireColumn.AutoFit
> > End With
> > End If
> > If Target = Intersect(Target, Range("H3:H" & Cells(Rows.Count,
> > 8).End(xlUp).Row) Then
> > If UCase(Range("H7")) = "YES" Then
> > Sheets("Old in").Range("F7").Copy
> > Sheets("Redeployment").Range("A4")
> > ElseIf UCase(Range("H7")) = "NO" Then
> > Sheets("Old in").Range("F7").Copy Sheets("Disposal").Range("A4")
> > End If
> > End If
> >
> > End Sub
> >
> > I did not test it, so give it a test run before permanently deleting the old
> > code. You cannot have two separate worksheet_change macros on the same
> > sheet, so you will neet to comment out (put apostrophes in front of code
> > lines) the old code while you test the new one.
> >
> >
> >
> >
> > "jcurless" wrote:
> >
> > > Okay, First I want to say I am new to this so you'll have to bare with me...
> > > I am using Excel 2003
> > >
> > > What I have:
> > > Inventory tracking Work book with 5 work sheets. (Old in, redeployment,
> > > disposal, device info, and location) the last 2 are tables used only for
> > > vlookups.
> > > The main worksheet is “old in” and has 6 column’s
> > > (b,c,e,f,g,h) (Others are hidden for later use if needed)
> > > In Column H the user types yes or no to answer a question:
> > > "is the device redeployable"
> > >
> > > What I need:
> > > If the text in work sheet “old in” cell H7 = YES
> > > I need to copy the text listed in work sheet “old in” cell F7 to work sheet
> > > “Redeployment” cell A4.
> > > If the text in work sheet “old in” cell H7 = NO
> > > I need to copy the text listed in work sheet “old in” cell F7 to work sheet
> > > “Disposal” cell A4.
> > > I want this to happen every time a cell in Column H is updated.
> > > I currently have code that will auto populate the date in column B when
> > > Column E is updated. I would need to ensure that the code doesnt effect that
> > > action. Here is the code I have for that...
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Cells.Count > 1 Then Exit Sub
> > > If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
> > > With Target(1, -2)
> > > .Value = Date
> > > .EntireColumn.AutoFit
> > > End With
> > > End If
> > > End Sub
> > >
> > > I have posted this on other sites but no one can figure out how to help!
> > > If anyone would like to try to tacle this with me please do so!!
> > > I can send a copy of the file if that would help!
|
Similar Threads
