for each - tables - vba

[pit84]

Hi

is there a way to do such a trick:

Dim cell As Range

For Each cell In Range("Table1[col3]")
cell.Value = Range("Table1[@col1]")
Next cell

or is using standard for look a better idea?

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Value _
= ActiveSheet.ListObjects("Table1").ListColumns(1).Range.Value
End Sub

 

[pit84]

thanks for the answer.

I might not have expressed myself very clearly, sorry for that.
The example was very simplified, just to show what i want to know.

More detailed example:

For Each cell In Range("Table1[col10]")
if Range("Table1[@col1]") < Range("Table1[@col2]") + Range("Table1[@col3]") then
cell.Value = "TEXT1"
elseif Range("Table1[@col1]") < Range("Table1[@col3]") + Range("Table1[@co4]") then
cell.Value = "TEXT2"
...
else
cell.Value = "TEXT DEF"
End if
Next cell

Also, in your way, I need listColumn - index, what I wanted to avoid, as using table headers simplifies the process. I don't know if that is possible, that's why I am asking

 

[My Aswer Is This]

It would be easier for me to understand if you were to tell me in words what you want and not in your formulas

And what is : TEXT2
And what is: TEXT DEF

And these values that will be found in Activex Textboxes

And you said:
as using table headers simplifies the process.

Simplifies the process for who?
I'm the one writing the code

 

[pit84]

I make an app, where I give lots of freedom to end user int terms of organizing data (order of columns), so they can organize it to their needs.

Now, when I need to work on the data, I can loop through them using standard 'for i = start to end' with cells(rowNr, colNr). This way, I need to determine colNr in front. It is simple, but the more columns I have to operate on, the more variables I have to declare.

If I put a formula into cell of table, excel keeps track of row by putting '@' symbol, eg '=@col1 - @col2. My question is, if I can do something like it via VBA to use for each loop. In tables, I can refer to specific column by its header: Table1[ColName]

In other words, I would like to replace:

for i = 1 to X
cells(i, Colnr1) = cells(i, Colnr2) [operation] cells(i, Colnr3)
next i

with:

for each cell in Table1[Colnr1]
cell.value = Range("Table1[Colnr2]") [operation] Range("Table1[Colnr3]")
next cell

 

[pit84]

In case I did not make myself understood, a live example:

I have two tables:
Table1 - main table, with lots of data about products we make. Two columns are of interest: Tool and Notes
TableToolNote - table I keep on SetUp sheet, with two columns only: Tool and Notes

The idea is, I populate second table with tools and corresponding notes. I have a macro, that would populate Notes in first table with Notes from second table if Tool from both tables matches.

Sub FillCustomNotes(wsJ As Worksheet, wsW As Worksheet)
    Application.ScreenUpdating = False
    Dim cell As Range
    Dim rng As Range
    Dim tbl As ListObject, tblWtL As ListObject
    Dim notesCol As Integer
    Dim toolCol As Integer
    Dim fillColor As Long
    Dim note As String
    Dim indexer As Integer
    
    Set tbl = wsJ.ListObjects("TableToolNote")
    Set tblWtL = wsW.ListObjects("Table1")
    notesCol = wsW.Range("Table1[Notes]").Column
    toolCol = wsW.Range("Table1[Tool]").Column
    
    If tbl.DataBodyRange.item(1, 1) <> "" Then
        indexer = 0
        For Each cell In tbl.ListColumns(1).DataBodyRange
            indexer = indexer + 1
            Set rng = Nothing
            note = tbl.DataBodyRange.item(indexer, 2)
            fillColor = tbl.DataBodyRange.item(indexer, 2).Interior.Color
            
            tblWtL.Range.AutoFilter field:=toolCol, Criteria1:=cell.Value, Operator:=xlFilterValues

            On Error Resume Next
                Set rng = tblWtL.Range.SpecialCells(xlCellTypeVisible)
            On Error GoTo 0

            If Not rng Is Nothing Then
                tblWtL.ListColumns(notesCol).DataBodyRange.SpecialCells(xlCellTypeVisible).Value = note
                tblWtL.ListColumns(notesCol).DataBodyRange.SpecialCells(xlCellTypeVisible).Interior.Color = fillColor
            End If
        Next cell
          
        If tblWtL.AutoFilter.FilterMode Then
            tblWtL.AutoFilter.ShowAllData
        End If
    End If
End Sub

Now, I use foreach to go through every element in second table, filter Table1 and populate Table1[Notes] with variable 'note'. But to assign a value to note, I use indexer while looping through second table and retrieve it with line:

note = tbl.DataBodyRange.item(indexer, 2)

My question is, if I can retrieve it, without using indexer and col number. Is there a way of writing a code, that will tell VBA to retrieve value from TableToolNote at the same row level from column name 'Note', something like: TableToolNote[@Note]


By the way, at the beginning, I do a check:

If tbl.DataBodyRange.item(1, 1) <> ""

I also have a macro, that uses a listbox on userform to list all items in TableToolNote, lets user chose and delete item. However, if a user removes last item, the table still has one row, but it is empty. Can I check if table is empty (has that row, but no data) with Is Empty, or other function? So far, all I've tried failed and that wast the only workaround I came up with

Thanks

 

[pit84]

Regarding
If tbl.DataBodyRange.item(1, 1) <> ""

I must have done something wrong earlier, as this line generates error if table is empty, yet check with Is Empty works fine