Automatically fill data from offset column

[stewart1]

Hi to all.

I have data that starts form row "4". Column A (row 4) starts with a number of X/1/1 column "B" has the word "client" and column "C" has the name of the person.

I am trying to get the a sequence going where from row 5 onwards, when the name of the client is entered the number X/1/2 will automatically enter and the name "client" will autofill in column "B". The numbers of course will follow on X/1/3 and so on respectively.

I need to try and do this through VBA as many rows will be used rather than enter column with a formula placed.

I hope someone can help.


Thanks for looking

 

[DauntingGecko]

You can just copy the X/1/1 down and it'll auto calculate the next number for you. E.G. x/1/2 then x/1/3 and so on?

If you want a formula then use: =IF(C5<>"","X/1/"&RIGHT(A4,MID(A4,5,10000))+1,"")

(change A4 to the cell that has X/1/1 and C5 is the cell for the client name).

 

[stewart1]

Hi Dauntinggecko,

Thanks for the formula.

I would prefer to go down the VBA route if possible as thousands of rows will be used. The formula you gave me would only go to the next row, then the following row gave me a VALUE error. It's my fault I imagine, but to solve both columns auto updating upon one input would be more beneficial.
(Unless it can't be done, then I'll abandon it!)

Thanks,


Stewart

 

[DauntingGecko]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err

Dim cell As Object

    If Target.Column = 3 Then
        For Each cell In Target
            If cell <> "" Then
                cell.Offset(0, -2) = "X/1/" & Mid(cell.Offset(-1, -2), 5, 100000) + 1
                cell.Offset(0, -1) = "Client"
            End If
        Next cell
    End If

Exit Sub

err:
    MsgBox err.Description, vbExclamation, "Error"

End Sub

 

[stewart1]

DauntingGecko, It works! (Of course it does!)

Just one more please. Can this be tweaked so that if a name is deleted the other two column (rows) "delete" as well. (Like a formula would)

Honestly though, thanks for what you have done, I've just sat here for three hours trying to make it work.

Thanks again

 

[stewart1]

Okay, I am having a go at doing this myself but I keep getting errors:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err

Dim cell As Object

    If Target.Column = 3 Then
        For Each cell In Target
            If cell < A - Z > "" Then
                cell.Offset(0, -2) = "X/1/" & Mid(cell.Offset(-1, -2), 5, 100000) + 1
                cell.Offset(0, -1) = "Client"
            End If
        Next cell
    End If
If Target.Column = 3 Then
            If cell <> "" Then
                cell.Offset(0, -2) = ""
                cell.Offset(0, -1) = ""
            End If
        Next cell
    End If
Exit Sub

err:
    MsgBox err.Description, vbExclamation, "Error"

End Sub

I've made the first part so that it picks up A-Z so it can differentiate between text and no text whereupon no text will reult in the data being deleted.

Its just not working though.

Please help!

 

[DauntingGecko]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err

Dim cell As Object

    If Target.Column = 3 Then
        For Each cell In Target
            If cell <> "" Then
                cell.Offset(0, -2) = "X/1/" & Mid(cell.Offset(-1, -2), 5, 100000) + 1
                cell.Offset(0, -1) = "Client"
            ElseIf cell = "" Then
                cell.Offset(0, -2).ClearContents
                cell.Offset(0, -1).ClearContents
            End If
        Next cell
    End If

Exit Sub

err:
    MsgBox err.Description, vbExclamation, "Error"

End Sub

 

[DauntingGecko]

If you want the actual row to be deleted rather than the data (so no empty rows appear) use this one:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo err

Dim cell As Object

    If Target.Column = 3 Then
        For Each cell In Target
            If cell <> "" Then
                cell.Offset(0, -2) = "X/1/" & Mid(cell.Offset(-1, -2), 5, 100000) + 1
                cell.Offset(0, -1) = "Client"
            ElseIf cell = "" Then
                Rows(cell.Row).Delete
            End If
        Next cell
    End If

Exit Sub

err:
    MsgBox err.Description, vbExclamation, "Error"

End Sub

 

[stewart1]

Hi DauntingGecko,

I just need the data to be deleted as there will be data in other columns further along,

Could you tell me how just those corresponding cells are deleted please.


Thanks

 

[stewart1]

Hi DauntingGecko,

So sorry, I did not see that you posted two! I am such a clutz! The delete cells worked perfectly!

At least you could see I was trying, albeit quite badly!


Thank you.