Excel Tables & VBA - Adding data into a table with two separate macros

[MrHydrant1857]

Hi everyone,

I am working on a program that has kind of a lot going on. I have a sheet that I manually input data into cells then use a macro to add that data into a "database" table in a separate sheet. that table has roughly 25 columns in it. from the initial macro only about half are used. Then i have another sheet that use a dropdown that has the data validation sourcing the first column of the database table. on the second sheet after i select from the dropdown I am manually entering data. i want to have my second macro add the new data into the table but in the appropriate column (column 14-25). I need the expert help on this as I am stuck. I know how to add rows and data into tables with VBA but I cannot for the life of me figure out how to input data into a specific table row. I may be too close to the program and might need a step back but any help would be appreciated

 

[stigcorneer]

I would fill an array with the contents of the database-table.
Then when you update the info on your second sheet you loop through the array and look for the validationkey.
When you find it you can change the values on that post in the array.
Finally you overwrite your database-table with the contents of your array.

Something like this.


dim tbl As ListObject
dim myArray()
dim key as string
dim data1 as string, data2 as string

set tbl = Sheet("sheet1").LlistObjects("databasetable")
myArray = tbl.DataBodyRange
key = Sheet("second sheet").Range("Validation Key").Value
data1 = Sheet("second sheet").Range("Data1").Value
data2 = Sheet("second sheet").Range("Data2").Value

For i = 1 To UBound(myArray)
If key = myArray(i, 1) then
myArray(i, 14) = data1
myArray(i, 15) = data2
Exit For
End if
Next i

tbl.DatabodyRange.value = myArray