How can I write an if, or, lookup formula in VBA

[morsoe]

I have the following formula in column M2. I don't know in which row the formula should end, because it depends on how many product lines I import from another sheet, so I thought the range should be something like range("M:M").Formula =.

My question is, how can I write this in VBA?

=IF(OR(L2="update";L2="inactivate");VLOOKUP(A2;catalogue_open!A1:R1;13;FALS K);TODAY())

Thanks in advance.

[PhilC]

Hi Morsoe,

From what I can gather if the value of cell $L$2 is "Inactive" or "Active" then look up the value stored in cell A2 in the catalogue. If the value of $L$2 is not "Inactive" or "Active" then enter today's date in the cell.

I am not sure what the result is that is being placed in the cell where the formula or even what the intent is. I'll need more informtion about how it is being used and perhaps a bit of data to show what your trying to do.

I also wanted to ask you if used semicolins instead of the commas? If so that may be the problem.

Thanks
Phil

[Blade Hunter]

[vba]
Sub morsoe()
Dim X As Long
For X = 2 To Range("L" & Rows.Count).End(xlUp).Row
If UCase(Range("L" & X).Text) = "UPDATE" Or UCase(Range("L" & X).Text) = "INACTIVE" Then
Range("M" & X).Formula = "VLOOKUP(A" & X & ",catalogue_open!A1:R1,13,FALSE)"
Else
Range("M" & X).Formula = Date
End If
Next
End Sub
[/vba]

There is a quicker way to do this and put the formula in there all in one go but I can't remember off the top of my head how so I looped it.

Hope that helps

Dan

[morsoe]

Hi Morsoe,

From what I can gather if the value of cell $L$2 is "Inactive" or "Active" then look up the value stored in cell A2 in the catalogue. If the value of $L$2 is not "Inactive" or "Active" then enter today's date in the cell.

I am not sure what the result is that is being placed in the cell where the formula or even what the intent is. I'll need more informtion about how it is being used and perhaps a bit of data to show what your trying to do.

I also wanted to ask you if used semicolins instead of the commas? If so that may be the problem.

Thanks
Phil

I have attached my workbook and tried to explain what i want to do.

And thanks for your help.

Morsoe

[morsoe]

[vba]
Sub morsoe()
Dim X As Long
For X = 2 To Range("L" & Rows.Count).End(xlUp).Row
If UCase(Range("L" & X).Text) = "UPDATE" Or UCase(Range("L" & X).Text) = "INACTIVE" Then
Range("M" & X).Formula = "VLOOKUP(A" & X & ",catalogue_open!A1:R1,13,FALSE)"
Else
Range("M" & X).Formula = Date
End If
Next
End Sub
[/vba]

There is a quicker way to do this and put the formula in there all in one go but I can't remember off the top of my head how so I looped it.

Hope that helps

Dan

Hi Dan,

I appreciate that you will help me. I will try your solution and have uploaded my workbook.

Morsoe

[morsoe]

Hi Dan,

The seems to be a problem with your code, when I use the two catalogue commands "UNCHANGED" and "UPDATED" it returns the following two cell values in product_start_date:

UNCHANGED = VLOOKUP(A2,catalogue_open!A2:Q30,13,false)
UPDATED = VLOOKUP(A4,catalogue_open!A2:Q30,13,false)

Am I doing something wrong. Take a look at my attachment.

Morsoe

[macropod]

Hi morsoe,

Take a look at: http://www.vbaexpress.com/forum/showthread.php?t=34327

Also, FWIW, if you name the range you're using from the 'catalogue_open' sheet (currently A1:R1), then a simple change to that name's range will update the range used by your current lookup formula.

[morsoe]

Hi morsoe,

Take a look at: http://www.vbaexpress.com/forum/showthread.php?t=34327

Also, FWIW, if you name the range you're using from the 'catalogue_open' sheet (currently A1:R1), then a simple change to that name's range will update the range used by your current lookup formula.

thanks for your reply - I'll take a look at it.

morsoe