Update a specific table column, considering the row of the active cell

[Nicha]

I have a table with 3 columns, whose fields I show below:

Contas_DO_Abertura.xlsx
	A	B	C	D	E
1
2
3		Customer_ID	Process_Number	Folder_Name
4		1234	P000000001	1234 - 1
5		5678	P000000002	5678 - 2
6
7
Sheet4

Cell Formulas
Range	Formula
D4:D5	D4	=[@[Customer_ID]]&" - "&[@[Process_Number]]

The [Process_Number] Column, has the Format "P000000000", so the Values entered are numeric, see below:

• In Row 1 of the table - row 4 of the sheet - the Value in the cell is (1).
• In Row 2 of the table - row 5 of the sheet - the Value in the cell is (2).

The [Folder_Name] Column, has the Formula Shown Above on [D4]; so the Values are supposed to be the concatenation of Both [Customer_ID] and [Process_Number] columns.

The problem is that the formula doesn't show the visible values resulting from concatenation, as shown Below:

Customer_ID	Process_Number	Folder_Name
1234	P000000001	1234 - P000000001
5678	P000000002	5678 - P000000002

As it is not possible to obtain the desired values using the formula, I would like to update the [Folder_Name] column using VBA. For example:
Since the active cell is row 4 (of the sheet), how can I write the desired value "1234 - P000000001" in the [Folder_Name] column, of the table via VBA?

Many thanks

 

[Alex Blakenburg]

As it is not possible to obtain the desired values using the formula,

Why not:

=[@[Customer_ID]]&" - "&TEXT([@[Process_Number]],"P000000000")

 

[Nicha]

Why not:

=[@[Customer_ID]]&" - "&TEXT([@[Process_Number]],"P000000000")

Hi Alex, let me thank you for the solution using a Formula.
In fact, I didn't mention in the post that I prefer a solution via VBA, because I already have a macro that runs in the event "Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)", where 'Target' has the reference of the active cell.

I therefore need to know how to write in Column [Folder_Name], on the same line as the Active cell, the value that the Formula could give me.

My best regards

 

[Alex Blakenburg]

Since you are using a Table it doesn't really make sense to use VBA to do this unless the rules are going to change from Row to Row since the table will add the formula to every current row and every new row.
I am logging off for the night so perhaps someone else closer to your time zone will provide a VBA option.

 

[Nicha]

Since you are using a Table it doesn't really make sense to use VBA to do this unless the rules are going to change from Row to Row since the table will add the formula to every current row and every new row.
I am logging off for the night so perhaps someone else closer to your time zone will provide a VBA option.

I really apretiate your help Alex. But The need to use VBA because my Table is much more complex than the one I presented here.
My goal is to know how to update the table colum, in the same row as the active cell, using VBA.
Thank you. I'll wait for someone that can Help me to solve my problem.

 

[Alex Blakenburg]

I wouldn't be doing it as a Workbook Event. You can try this as a worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tbl As ListObject
    Dim rowTblTgt As Long, colTblTgt As Long
    Dim colFldr As Long, colCust As Long, colProc As Long
    
    If Target.CountLarge > 1 Then Exit Sub
    
    Set tbl = Range("Table1").ListObject                                    ' <--- Change to actual Table Name
    rowTblTgt = Target.Row - tbl.Range.Cells(1).Row
    If Intersect(Target, tbl.Range) Is Nothing Or rowTblTgt = 0 Then Exit Sub  ' Outside of Table or Table header
    
    colTblTgt = Target.Column - tbl.Range.Cells(1).Column + 1
    
    colFldr = tbl.ListColumns("Folder_Name").Index
    colCust = tbl.ListColumns("Customer_ID").Index
    colProc = tbl.ListColumns("Process_Number").Index
    
    If colTblTgt = colCust Or colTblTgt = colProc Then
            
       Application.EnableEvents = False
       
       With tbl.ListRows(rowTblTgt).Range
           .Cells(colFldr).Value = .Cells(colCust) & "-" & Format(.Cells(colProc), "P" & String$(9, "0"))
       End With
    
       Application.EnableEvents = True
    End If

End Sub

 

[Akuini]

The problem is that the formula doesn't show the visible values resulting from concatenation, as shown Below:

Try using text function:

Book1
	B	C	D
3	Customer_ID	Process_Number	Folder_Name
4	1234	P000000001	1234 - P000000001
5	5678	P000000002	5678 - P000000002
Sheet3

Cell Formulas
Range	Formula
D4:D5	D4	=[@[Customer_ID]]&" - "&TEXT([@[Process_Number]],"P000000000")

 

[Nicha]

I wouldn't be doing it as a Workbook Event. You can try this as a worksheet change event.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tbl As ListObject
    Dim rowTblTgt As Long, colTblTgt As Long
    Dim colFldr As Long, colCust As Long, colProc As Long
   
    If Target.CountLarge > 1 Then Exit Sub
   
    Set tbl = Range("Table1").ListObject                                    ' <--- Change to actual Table Name
    rowTblTgt = Target.Row - tbl.Range.Cells(1).Row
    If Intersect(Target, tbl.Range) Is Nothing Or rowTblTgt = 0 Then Exit Sub  ' Outside of Table or Table header
   
    colTblTgt = Target.Column - tbl.Range.Cells(1).Column + 1
   
    colFldr = tbl.ListColumns("Folder_Name").Index
    colCust = tbl.ListColumns("Customer_ID").Index
    colProc = tbl.ListColumns("Process_Number").Index
   
    If colTblTgt = colCust Or colTblTgt = colProc Then
           
       Application.EnableEvents = False
      
       With tbl.ListRows(rowTblTgt).Range
           .Cells(colFldr).Value = .Cells(colCust) & "-" & Format(.Cells(colProc), "P" & String$(9, "0"))
       End With
   
       Application.EnableEvents = True
    End If

End Sub

Fantastic @Alex Blakenburg; It's exactly what I was looking for.
Manny thank's to you for all the support.

I need to thank to @Akuini as well, for your sugestion on the Formula.

My best regards

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.