PDA

View Full Version : Range with Table VBA?



whatsapro
06-23-2016, 09:08 AM
Every time I insert columns into the table I have to rewrite the VBA code. I'm sure there is a way to make the VBA code dynamic. Any ideas?


NewSht.Range("A1").Value = cwkb.Sheets("F100 Supportability").Range("A" & Target.Row).Value

The Range("A" & Target.Row).Value is what I have to update when I insert columns in the table.

Table name is "AssetView"

Any suggestions?

Thanks again.

SamT
06-23-2016, 10:39 AM
There is nothing wrong with that line of code. "AssetView" is a legitimate Name.

That exhausts my knowledge of your workbook, therefore I have no other suggestions. Sorry.

whatsapro
06-23-2016, 02:11 PM
Yes but let's say I had Range("F" & Target.Row).Value, where Target.Row is the row clicked on. "F" is the column I want NOW.

However, I insert a new column say between "A" and "B". That pushes everything to the right one so then I would want "Range("G" & Target.Row).Value. Notice the "G" now instead of "F".

Is there a way to make this dynamic?

SamT
06-23-2016, 04:48 PM
Is there a way to make this dynamic?Probably. Maybe. Need to know a lot more details. A better question is why are you trying to force a square Excel into your round hole idea?

Since you keep inserting columns willy nilly, you don't know what Column headers you will have in the future so you can't use Find to retrieve the Column number.

Why do you have to keep inserting Columns into a table?

BTW, I am assuming that when you say "Table" that you mean a common rectangular range of cells and don't mean a Table Object with ListRows and ListColumns.

offthelip
06-25-2016, 06:50 AM
I have done very similar thing where you have lots of column and you want to identify which column to use. Assuming that you know what the header at the top of the colum is you can search for this to find the right column.
I have put headers in such a first header, second header etc
Try something like this ( I have assumed a maximum of 50 columns just for this example);

inarr = Range(Cells(1, 1), Cells(1, 50))
For i = 1 To 50
If inarr(1, i) = "Third Header" Then
colno = i
Exit For
End If
Next i
rowno = target.Row
NewSht.Range("A1").Value = cwkb.Sheets("F100 Supportability").Range(Cells(rowno, colno), Cells(rowno, colno)).Value

SamT
06-25-2016, 07:53 AM
If Offthelip's code will work for you, you can make it dynamic with respect to the number of columns by adding

c = Cells(1, Columns.Count).End(xlToLeft).Column

inarr = Range(Cells(1, 1), Cells(1, c))
For i = 1 To c
'''

snb
06-25-2016, 10:12 AM
Use a dynamic Table.

The button will always return the second value in Column 'address'.

Add a column before column 'address' and click the button again.
Remove a column before the column 'address' and click the button again.