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

MrHydrant1857

New Member
Joined
Mar 29, 2019
Messages
35
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top