PDA

View Full Version : Elseif with values of formulas



nugol
03-15-2016, 12:42 AM
Hy guys,
I am trying to solve something and I am not entirely sure how to.
The goal is:
If the cells are empty then excel makes a vlookup. This part works so far.
In the second part it should make a vlookup if it is not empty and if vllokup has a certain value.
In the third part excel should just do nothing if the cell is not empty and vlookup does not have the desired value.


Sub Knowcells()
For i = 1 To 10
If IsEmpty(Cells(i, 1)) Then Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC[-2],RC[9]:R[1]C[10],2,0)"
ELSEIF ('Cell is not empty, Then vlookup, if vlookup has certain value (x,v or t))
ELSEIF ('Cell is not empty and vlookup does not has desired value Then just leave everything like it is.)

Next i
End Sub

I tried a few things like call or function but I really donn't get them.
Thanks in advance for your time

Bob Phillips
03-15-2016, 01:03 AM
Sub Knowcells()
Dim this As Variant, that As Variant

For i = 1 To 10

this = Cells(I, 1).Value
If IsEmpty(Cells(i, 1)) Then

Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC[-2],RC[9]:R[1]C[10],2,0)"
Else

this = Cells(i, 1).Value
Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC[-2],RC[9]:R[1]C[10],2,0)"
that = Cells(i, 1).Value
If that <> "x" And that <> "v" And that <> "t" Then

Cells(i, 1).Value = this
End If
End If
Next i
End Sub

nugol
03-15-2016, 02:07 AM
Hy xld, works wonderful.
Only thing that troubles me is that there is a 0 (zero) in former empty cells?

Bob Phillips
03-15-2016, 03:11 AM
I can't reproduce that scenario. Can you post the workbook?

nugol
03-15-2016, 03:41 AM
Can't post it right now but will post it tomorrow.

But it does not matter anymore, since I will just copy and paste the value somewhere else and will replace the zeros with empty lines.
My question now, can the same be done with "#NV". It works with the zeros below, but not with #NV, even if it is just written in myself.

Thanks so far.


For i = 1 To 10


If Cells(i, 2).Value = 0 Then Cells(i, 2) = ""


Next i