Datatable Excel 2003

[elduderino65]

Hi everyone, not sure if this is going to be laughed at or not, I spent a while today trying to get this correct. I have a data table, the rows are fixed. The columns are constantly changing, and I would like my rows to get to that point as well soon enough.

I am currently using If then else statements which pick which cell to use for the column input. When I move to 10 or so variables on either side that seems like too much work to code all 100 if statements. I want to have 2 lookups, that will have the cell reference ie "J6" based upon which 2 variables I want to use for this table displayed in the cell. That vlookup is in cell AG32 for the columns. How do I put this as the column. I tried and all I got was error messages.

Table.Selection. RowInput:= range.(AJ9) ColumnInput:= range.("AG32") does not return what I want normally...any suggestions, obviously this forum really helps people and I greatly appreicate the effort people make.

[royUK]

I can't understand why the columns should not be fixed in number. A bit better explanation is required & an example workbook would help

[elduderino65]

Ok so in the example below, where it says column input, I just want to be able to reference a cell, or just use the text from another cell as my column (and possibly row)input. Doesn't seem to want to let me.

 If Range("AJ30").Value = "Sally" Then
                Range("AK27:AQ35").Select
                Selection.Table RowInput:=Range("AJ8"), ColumnInput:=Range("AJ9")
                Else
                If Range("AJ30").Value = "Jane" Then
                Range("AK27:AQ35").Select
                Selection.Table RowInput:=Range("AJ8"), ColumnInput:=Range("AJ10")
                Else
                If Range("AJ30").Value = "Betty" Then
                Range("AK27:AQ35").Select
                Selection.Table RowInput:=Range("AJ8"), ColumnInput:=Range("AJ11")
                Else
                If Range("AJ30").Value = "Suzie"Then
                Range("AK27:AQ35").Select
                Selection.Table RowInput:=Range("AJ8"), ColumnInput:=Range("AJ12")
                Else
                If Range("AJ30").Value = "Tammy"Then
                Range("AK27:AQ35").Select
                Selection.Table RowInput:=Range("AJ8"), ColumnInput:=Range("AJ13")
                End If
                End If
                End If
                End If
                End If

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[elduderino65]

I will upload a sample sheet later tonight.

[elduderino65]

Ok so here is the sheet, essentially I do not want a million different if statements, I figured the people on here would know rather than me have 100 or so combinations to code. There are notes in the Macro, but L4 and M4 would be the idea place for the column and row references to be pulled from.

Thanks a million

[shg]

If each row is associated with a name, and each column is associated with a name, why not just list the names across the top of the table and down the left side, and use a pair of simple MATCH formulas?

[shg]

Or ...

Sub Test()
    Dim iCol As Long

    Range("O4:V11").ClearContents
    iCol = Application.Match(Range("L3").Value, Array("Betty", "Sally", "Jane", "Tammy", "Suzie"), 0)
    Range("N3:V11").Table RowInput:=Range("C4"), _
                          ColumnInput:=Range("C1").Offset(iCol)
    Calculate
End Sub

BTW, you have a trailing space in Sally's name that you should fix.

[elduderino65]

If each row is associated with a name, and each column is associated with a name, why not just list the names across the top of the table and down the left side, and use a pair of simple MATCH formulas?

I was refering to this, thanks

[elduderino65]

alright, thank you for all of your help.

[elduderino65]

Any idea how to bring the table to another sheet and still have it work, using the previous code?
If not no problem
thanks

Scott

[shg]

I don't understand the question -- just change the references for the table location and the row and column references.

[elduderino65]

I just realized what I was asking was impossible, my mistake.