Excel VBA to move data from table to table based on column name

I created a procedure that loops in all header columns of table 1 and search for the same header column in table 2 to update relveant cells based on matched column name.

This is the code for this procedure:

Do While i <= SourceTableColumnCount
ColumnName = sourceTable.HeaderRowRange(i).Value
                    On Error Resume Next
                    DestColumnIndex = destTable.Range.Find(ColumnName, MatchCase:= True, SearchFormat:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookAt:=xlWhole).Count
                        If Err.Number <> 0 Then
                            'In case column name in source table is not found in destination table
                        Else
                            destTable.DataBodyRange(DestRowIndex + 1, DestColumnIndex).Value =  sourceTable.DataBodyRange(r + 1, i).Value
                        End If
                    i = i + 1
Loop

The problem is DestColumnIndex is always 1. Although column name in destination table is matching column name in source table. It should work like this:

1- Go to first column in table 1 and get it's value 2- Go to table 2 and search for value from step 1 3- For the found result, get the range.column (Index of this column) 4- Set the cell value where range (Row, DestColumnIndex)

my problem is in step 3 where range.column is always 1 so the output of step 4 is always updating cell where column index is 1.

I hope the problem is clear.

Answers


Do While i <= SourceTableColumnCount
ColumnName = sourceTable.HeaderRowRange(i).Value
                    On Error Resume Next
                    DestColumnIndex = destTable.Range.Find(ColumnName, MatchCase:=True, SearchFormat:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookAt:=xlWhole).Column - destTable.Range.Column + 1 'why would you use .Count?, it is a single cell, its count will always be 1
                        If Err.Number <> 0 Then
                            'In case column name in source table is not found in destination table
                        Else
                            destTable.DataBodyRange(DestRowIndex + 1, DestColumnIndex).Value = sourceTable.DataBodyRange(r + 1, i).Value
                        End If
                    i = i + 1
Loop

Need Your Help

List of classes in an assembly

.net vb.net reflection .net-assembly

I've a DLL assembly, in which there are various classes. Each class has around 50-100 members and 4-5 functions. How can I create a list of all the classes and their respective members using a VB.NET

Image resize and upload in grails

javascript html grails image-uploading image-resizing

I am trying to acheive the image crop/resize and upload functionality as done in the linkedin, twitter etc.The scenerio is as: