Solved: index, match

[white_flag]

Hello
I have the following:
2 sheets -In one I have the range "name_range" (from where the data will be collected) and the second data were will be put.
On the second one I have almost the same first.row and first.column. I like to know, how can I make an correct syntax , that will take appropriated data from the range and putted in the second table.
how can you tell to VBA to select just the first row from an range and the first column from the range.
I suppose that Index and match will do the rest:

index(name_range, match(r" & rTopLeft.Row & "c, name_range.first.row, 1), match (rc" & rTopLeft.Column, name_range.first.column,1))

index(name_range, match(A2-from the second table, name_range.first.row, 1), match(B3-from the second table, name_range.first.column,1))

so this approach is an correct one?

thank you

[austenr]

Did you try that? What happened?

[Sean.DiSanti]

i've not played with index/match before, but when i've had similar situations, i usually just do a vlookup and then a quick paste special/values

[white_flag]

ok, I make it to work, but I have now an problem with the match. ex

I have the next values 15,20,25,30 ..etc 100 (like an headers) those are been taken from the second table. in the table with the given values in header I have (30, 35, 40 ..etc..100). if I use the match_type 1, for the values 15, 20 I have #N/A, I like that the values to take the first value from the row/column. for match_type -1 is worst then 1

how, can I trick the match function to take the next biggest value if the value is missing?

[Sean.DiSanti]

depends on how you did it. if you used a vlookup, you can have it look at ranges; if you did it in code using a select case, then you would just set a case else... or you could do a pre-check to see which ones are not going to be an exact match, and change them to the next biggest value before you do the actual matching... if you show code, i can offer more specific help

[white_flag]

this is the part of the code:

[VBA]
Set rTopLeft = Sheet1.Range("B11")
j = 1
Set range_i = Sheet4.Range(Me.Controls("ComboBox" & j).Text)
Set range_m = Sheet4.Range("m_" & Me.Controls("ComboBox" & j).Text)

With rTopLeft
.Offset(0, 0).Resize(1, UBound(tk)).Value = tk_values
.Offset(0, 0).Resize(UBound(dia), 1).Value = dia_values
For n = 1 To range_i.Rows.Count
For m = 1 To range_i.Columns.Count
With rTopLeft.Offset(n, m)

If CheckBox15 = True Then
formula_c = "match(r" & rTopLeft.Row & "c,ca" & ",1)" & ""
Else
formula_c = 0
End If
If range_i.Cells(n + 1, m + 1).Value = 0 Then
.FormulaR1C1 = Null
.Interior.ColorIndex = 56
.Font.Bold = False
Else
.FormulaR1C1 = "=" & formula_c
.Interior.ColorIndex = 0
.Font.Bold = False
.Font.Name = "Trebuchet MS"
.Font.Size = 10
.NumberFormat = "0.00"
End If
End With
Next m
Next n

End With
[/VBA]
I need Index and match functions. vlookup it is not feting with my needs. I need an formula that give an value from table on intersection on row and column.

[Bob Phillips]

[vba]

Set rTopLeft = Sheet1.Range("B11")
j = 1
Set range_i = Sheet4.Range(Me.Controls("ComboBox" & j).Text)
Set range_m = Sheet4.Range("m_" & Me.Controls("ComboBox" & j).Text)

With rTopLeft
.Offset(0, 0).Resize(1, UBound(tk)).Value = tk_values
.Offset(0, 0).Resize(UBound(dia), 1).Value = dia_values
For n = 1 To range_i.Rows.Count
For m = 1 To range_i.Columns.Count
With rTopLeft.Offset(n, m)

If CheckBox15 = True Then
formula_c = "INDEX(" & ca.Address & ",4-MATCH(I1,TRANSPOSE(N(OFFSET(" & ca.Cells(1, 1).Address & _
",0,3-ROW(1:" & ca.Columns.Count & ")))),-1))"
Else
formula_c = 0
End If
If range_i.Cells(n + 1, m + 1).Value = 0 Then
.FormulaR1C1 = Null
.Interior.ColorIndex = 56
.Font.Bold = False
Else
.FormulaArray = "=" & formula_c
.Interior.ColorIndex = 0
.Font.Bold = False
.Font.Name = "Trebuchet MS"
.Font.Size = 10
.NumberFormat = "0.00"
End If
End With
Next m
Next n
[/vba]

[Sean.DiSanti]

do you have an example of data to be worked on?
***edit***
scratch that xld got it already

[white_flag]

morning Bob

thx for replay, I hope that you bring winter tires :P (yesterday was a traffic jam day) and you enjoy a bit of Belgium (a bit cold now).

back to your solution. please look in attachment. I do not have necessary skills to figure out, what it is wrong.

thx

[white_flag]

[VBA]
If CheckBox15 = True Then
formula_mo_cla = "INDEX(m_cla" & ",4-MATCH(I1,TRANSPOSE(N(OFFSET(m_cla" & .Cells(1, 1).Address & _
",0,3-ROW(1:m_cla" & .Columns.Count & ")))),-1))"
Else
formula_mo_cla = 0
End If
[/VBA]

I get 0.00 values every ware

[white_flag]

that was an ugly formula.

[Sean.DiSanti]

i think you want to be checking Checkbox15.Value, not just checkbox15

[white_flag]

yes, this is a correct remark. thx Sean. I will do the changes

[Bob Phillips]

Whilst I am not arguing for not including the Value property, as it is the default property for Checkbox, you can get away with it.