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

MrHydrant1857

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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

stigcorneer

New Member
Joined
Nov 14, 2018
Messages
23
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,927
Messages
5,639,036
Members
417,065
Latest member
ALONSO_1150

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
Top