Conditional Writing of Data by Comparing Two Tables Refering to Table Headers in Macro

[AnnaGT]

Hello,

I've been looking for similar threads online, but I couldn't find any solution that fits to my problem, so I would be very thankful for any help or advice.

I have 2 Tables in 2 separate Worksheets. I want to fill out a column in my Data Table according to the information in the Lookup Table with a Macro. I have some buttons on the WsLookup through which the Macros can be activated by klickiing on them.

Here an Example:

WsLookup
TblLookup
[TABLE="width: 500"]
<tbody>[TR]
[TD]Color
[/TD]
[TD]Prices
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]

WsData
TblData
[TABLE="width: 500"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Color
[/TD]
[TD]Prices
[/TD]
[/TR]
[TR]
[TD]Jacket
[/TD]
[TD]Blue
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trousers
[/TD]
[TD]Blue
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T-Shirt
[/TD]
[TD]Red
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


1. I wanted to add a new column to the Data table independently from number of columns and length. I've tried with this but it didn't work:

TblData.ListColumns.Add 1
TblData.HeaderRowRange(1) = "Price"

2. I want to fill the column with the Information from the lookup table by compraing the colors on both tables.

If Sheet("WsData").Range("TblData[Color]") = Sheet("WsLookup").Range("TblLookup[Color]") Then
Sheet("WsData").Range("TblData[Price]") = Options.Range("RETROFIT[Price]")
End If

3. In a further step I also need to change some information in certain columns depending on some conditions for example_:

If year <1990 and color = purple, write "old_purple" in column "status"

Important in all these, is that I would like to refer to the columns by their headers, and to the tables by their names, so that the the excel sheet and macro can be used also if the imported data change.

Im Using Excel 2010 and an already processed access data base.

Thank you very very much!!

Anna

 

[AnnaGT]

Hi!

Maybe this makes it more clear. But I get an error in the first For-loop.

Thank you very much in advance!



Sub Add_Retrofit_Column()
' Add_Retrofit_Column

Worksheets("EUROPE").Activate

Range("A1").End(xlToRight).Offset(0, 1).Select
ActiveCell.Value = "RETROFIT"


Dim i As Integer
Dim j As Integer

For i = 1 To Worksheets("EUROPE").Range("TECH").End(xlDown).Count
For j = 1 To Worksheets("Options").Range("TECH").End(xlDown).Count
If Worksheets("EUROPE").Cells(i, [@[TECH]]).Value = Worksheets("Options").Cells(j, [@[TECH]]).Value Then
Worksheets("EUROPE").Cells(i, [@[Retrofit]]).Value = Worksheets("Options").Range(j [@[Retrofit]]).Value
End If
Next j
Next i

End Sub