look up formula

M

Miree

I have the following code which enters the formula
=IF(F21="","",IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Densities!O:p,MATCH(F21,Densities!O:O,0),2)))
into the cells, I need the code to perform the the calculation insted of
putting it in to the cells.

Range("H21").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
 
B

Bob Phillips

With Range("H21")

FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
.Value = .Value
End With
 
M

Miree

Sorry this doesnt work

Bob Phillips said:
With Range("H21")

FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"), Type:=xlFillDefault
.Value = .Value
End With

--
__________________________________
HTH

Bob

Miree said:
I have the following code which enters the formula
=IF(F21="","",IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Densities!O:p,MATCH(F21,Densities!O:O,0),2)))
into the cells, I need the code to perform the the calculation insted of
putting it in to the cells.

Range("H21").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"),
Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
Click to expand...
Click to expand...
 
B

Bob Phillips

Sorry I missed a bit

With Range("H21")

.FormulaR1C1 = "=IF(RC[-2]="""",""""," & _
"IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),""""," & _
"INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
.AutoFill Destination:=.Resize(19), Type:=xlFillDefault
.Resize(19).Value = .Resize(19).Value
End With


--
__________________________________
HTH

Bob

Miree said:
Sorry this doesnt work

Bob Phillips said:
With Range("H21")

FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"),
Type:=xlFillDefault
.Value = .Value
End With

--
__________________________________
HTH

Bob

Miree said:
I have the following code which enters the formula
=IF(F21="","",IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Densities!O:p,MATCH(F21,Densities!O:O,0),2)))
into the cells, I need the code to perform the the calculation insted
of
putting it in to the cells.

Range("H21").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"),
Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
Click to expand...
Click to expand...
Click to expand...
 
M

Miree

thanks worked perfectly

Bob Phillips said:
Sorry I missed a bit

With Range("H21")

.FormulaR1C1 = "=IF(RC[-2]="""",""""," & _
"IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),""""," & _
"INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
.AutoFill Destination:=.Resize(19), Type:=xlFillDefault
.Resize(19).Value = .Resize(19).Value
End With


--
__________________________________
HTH

Bob

Miree said:
Sorry this doesnt work

Bob Phillips said:
With Range("H21")

FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"),
Type:=xlFillDefault
.Value = .Value
End With

--
__________________________________
HTH

Bob

I have the following code which enters the formula
=IF(F21="","",IF(ISNA(MATCH(F21,Densities!O:O,0)),"",INDEX(Densities!O:p,MATCH(F21,Densities!O:O,0),2)))
into the cells, I need the code to perform the the calculation insted
of
putting it in to the cells.

Range("H21").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]="""","""",IF(ISNA(MATCH(RC[-2],Densities!C[7],0)),"""",INDEX(Densities!C[7]:C[8],MATCH(RC[-2],Densities!C[7],0),2)))"
Selection.AutoFill Destination:=Range("H21:H39"),
Type:=xlFillDefault
Range("H21:H39").Select
Range("H21").Select
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup returns zero or the wrong number 1
lookup vba 2
Lookup not working 2
lookup returning #N/A 5
Need Formula to Calculate Time/Overtime (2) 2
Checkbox linked to calcs 1
Checkbox linked to calcs 0
Vlookup in vba - how to use absolute rows not relative 1

Top