Need code to repeat macro command

[johnwc]

Hello, This is my second post here. I haven't worked much with macros and the work I have had to do so far I have been able to figure out by searching forums such as this one. However, I need this community's help with what I am currently working on. Here's the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:E2")) Is Nothing Then
Exit Sub
Else
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D2"), Type:=xlFillDefault
Range("D1:D2").Select
End If
End Sub

I need this code/behavior to repeat in cells E3:E10, skip a couple rows, then repeat in E13:18 skip a couple more rows, repeat in E16:E21, etc.

Thanks!

Version: Excel 2000
OS: XP Pro

 

[johnwc]

Good question. If a value in column G is changed but is not deleted, then no change in column D. If a value in Column G is deleted (or if an entire row is deleted), then values in column D subsequent to the affected row should renumber accordingly.

This is the ideal behavior. If this is too hard to code, then I am ok with saying that once a value has been entered in column G and a value is generated in column D, then any future changes to G does not affect D.

Or, can you think of a better alternative? I hadn't thought this scenario through actually.

 

[Blade Hunter]

So it is almost like we need two event macros:

The first on is on a change, when a user enters something we need it to calculate the number and pop it in column D.

We don't need to test if the cell is a heading because we know the row that the user is entering on and that is the same row we are entering to. The user will not try and enter data on a heading row,

The second event is on a row deletion. When this occurs, we need a macro to run through column D and where there is Data in G it needs to recalc column D.

The problem here is what should it do when there is only 1 entry? It will have nothing to go on above or below the entry to work out what the prefix should be. We could do something with the intercept to dump the contents of the value of the cell in column D to a variable before we delete the row. Does this sound right?

 

[johnwc]

I agree that we are dealing with two separate events, and your assessments seem correct to me.

Regarding you last point stating the problem: You ask what it should do when there is only one entry. Are you referring to the case where there is only an entry in G8 (referring to the example I pasted in previously)? If so, that is when I want there to be some sort of popup window prompting the user to enter the prefix that will be used for all numbers in column D. The macro will automatically tack "-001" (i.e., dash 001) onto the user-defined prefix and voila there's the value for D8. Ok so a popup may be a bit too tricky. Another way to do it is allow the user to enter the entire value in D8, (e.g., ABC-001) and then have the macro take over on D9 and on using the AutoFill event.

 

[Blade Hunter]

Actually, ignore that question. If the user deletes a row and it is the only row then there is nothing to fix because it is the only row.

I will see what I can put together for you

 

[johnwc]

Oh I see - you were talking about the delete event - my bad. Yeah, what you say sounds right.

Thanks so much for your help.

 

[Blade Hunter]

OK, Try this for the change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 7 Then
    If Target.Row = 8 Then
        Range("D8").Formula = InputBox("Enter prefix") & "-001"
    End If
    If IsEmpty(Range("D" & Target.Row)) Then
        X = 0
        Do Until Left(Target.Offset(X, -3).Text, 4) = Left(Range("D8").Text, 4)
            X = X - 1
        Loop
        Range("D" & Target.Row).Formula = Left(Range("D8").Text, 4) & Left("000", 3 - Len(Right(Target.Offset(X, -3).Text, 3) + 1)) & Right(Target.Offset(X, -3).Text, 3) + 1
    End If
End If
Application.EnableEvents = True
End Sub

If this works ok for you I will work on the delete even next.

Cheers

Dan

 

[johnwc]

That is right on - PERFECT!! Thank you so much.

 

[johnwc]

Dan, I noticed something today I wonder if there is an easy workaround: If the user enters anything but a 3 digit prefix, the number of zeros increases proportionally in D9 and following. For example, if the user enters a 2 digit prefix, D8 = "AB-001" but D9 = "AB-0001." Likewise, a 4 digit prefix results in the dash being cut off. Example: "ABcD-001" in D8 becomes "ABCD001" in D9 and following. Is this too annoying to fix? If so, no worries I can just create a comment or something in the first cell to notify the user a 3 digit prefix is required.

Also, if the user does not make the first entry in G8 the thing blows up. Is there an easy fix for this? If not, no worries, again I can create a comment for this.

How's it going with the delete event?

I can't express enough how much gratitude I have for your help!!!

 

[Blade Hunter]

We need a starting point which in this case is D8, the user must make an initial entry in there.

If there is somewhere in the sheet that you know will never have anything entered into it we can use that to store a text string for what the user enters, if we can do this is will allow us to fix both your problems. We coul put it in cell A1 or where ever you can guarantee the user will not type. We can format it to be white so they will not even know it is there.

The delete I have not looked at, I got busy at work . Probably will not get it done today to be honest as I am not sure how to detect if the user has deleted something.

 

[Blade Hunter]

This fixes the first issue:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Target.Column = 7 Then
    If Target.Row = 8 Then
        Range("D8").Formula = InputBox("Enter prefix") & "-001"
    End If
    If IsEmpty(Range("D" & Target.Row)) Then
        X = 0
        Do Until Left(Target.Offset(X, -3).Text, Len(Target.Offset(X, -3).Text) - 3) = Left(Range("D8").Text, Len(Range("D8").Text) - 3)
            X = X - 1
        Loop
        Range("D" & Target.Row).Formula = Left(Range("D8").Text, Len(Range("D8").Text) - 3) & Left("000", 3 - Len(Right(Target.Offset(X, -3).Text, 3) + 1)) & Right(Target.Offset(X, -3).Text, 3) + 1
    End If
End If
Application.EnableEvents = True
End Sub

Cheers

Dan