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


Feel free to accept my answer if it solves your issue.

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

Get CurrentTime from Video Tag in Samsung Smart Tv

html5 html5-video samsung-smart-tv

I've installed Samsung Smart TV SDK and I'm trying to get the current frame/time the video is playing.

Is it possible to have two applications that use the same databases in a distributed transaction without having to implement a resource manager?

.net database web-services distributed msdtc

We have a .Net application that initiates a transaction using NHibernate and modify data in the database X. Amid this transaction we issue calls to a WebService (.Net too), that also modifies datab...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.