Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > If and vlookup in macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 39
Default If and vlookup in macro
Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 39
Default If and vlookup in macro
I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
  
Posts: 2,062
Default If and vlookup in macro
two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,510
Default If and vlookup in macro
Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 39
Default If and vlookup in macro
Thanks JMB and Ossie<

I am trying to insert JMB formula in Ossie macro (I want a numeric 2 and a
relative row reference for the vlookup ). But the formula is placing #2 in
column M from 8 to 27 (Sheet2), instead of doing the vlookup. Any reason why?
Where should I call Sheet2.

Regards


Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M8:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult =
"IF(ISNA(VLOOKUP($A9,Sheet1!$A:$L,2,FALSE)),2,VLOO KUP($A9,Sheet1!$A:$L,2,FALSE))"
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub

"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,510
Default If and vlookup in macro
Hi again Faboboren,

If you want to insert the formula rather than the way I did it then you must
use relative addressing in the foumula. It will be like this:-

Note: The part of the formula between the double quotes is one line.

Sub Macro2()
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M8:M27")
End With

For Each c In rngResults

c.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(RC[-10],Sheet1!C1:C12,2,FALSE)),2,VLOOKUP(RC[-10],Sheet1!C1:C12,2,FALSE))"

Next c

End Sub



To get the correct syntax for for the forumla to insert in the macro, the
simplest way is:-

Insert the formula in the required cell in the interactive mode and get it
working.
Turn on the macro recorder.
Select the cell with the formula.
Make a dummy change to the formula like delete the last character and then
re-enter the character and then press Enter. (No change to the formula)
Turn off the recorder.
The recorded macro will show it for ActiveCell.FormulaR1C1 = .......
Replace ActiveCell with the required range which in this case is the range
variable c

--
Regards,

OssieMac


"Faboboren" wrote:

Thanks JMB and Ossie<

I am trying to insert JMB formula in Ossie macro (I want a numeric 2 and a
relative row reference for the vlookup ). But the formula is placing #2 in
column M from 8 to 27 (Sheet2), instead of doing the vlookup. Any reason why?
Where should I call Sheet2.

Regards


Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M8:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult =
"IF(ISNA(VLOOKUP($A9,Sheet1!$A:$L,2,FALSE)),2,VLOO KUP($A9,Sheet1!$A:$L,2,FALSE))"
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub

"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
  
Posts: 2,062
Default If and vlookup in macro
I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 39
Default If and vlookup in macro
JMB,

Thanks very much, the second option is working perfect.

How can I set up the range as variable (NO fixed Range("M2:M27")).
I can have different amount of rows

Thanks

With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!:C,2,FALSE))"
..Value = .Value
End With




"JMB" wrote:

I do have some observations:

it looks like the period is missing in the set statement, and I think the
sheet s/b sheet2 judging from the OP
With Sheets("Sheet2")
Set rngResults = .Range("M2:M27")
End With

and the range containing the table is not qualified in the lookup
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Sheets("Sheet1").Range("A:C"), 2, False)


But the same results could be achieved with
With Worksheets("Sheet2").Range("M2:M27")
.Formula =
"=IF(ISNA(VLOOKUP(C2,Sheet1!A:C,2,FALSE)),2,VLOOKU P(C2,Sheet1!A:C,2,FALSE))"
.Value = .Value
End With

which would give the same results as the macro (results hardcoded).




"OssieMac" wrote:

Hi Faboboren,

You can also use worksheetfunction with vlookup. However, it does not return
a formula in the cell; it returns the actual value. Thought you might be
interested a another option.

Sub Macro1()
Dim lupResult As Variant
Dim rngResults As Range
Dim c As Range

With Sheets("Sheet1")
Set rngResults = Range("M2:M27")
End With

For Each c In rngResults
lupResult = "" 'Last value retained if error

'Returns error if not found
On Error Resume Next
lupResult = WorksheetFunction.VLookup(c.Offset(0, -10), _
Range("A:C"), 2, False)
On Error GoTo 0

If lupResult < "" Then
c = lupResult
Else
c = "2"
End If

Next c

End Sub


Regards,

OssieMac



"JMB" wrote:

two questions:

1. did you intend the formula to return a text "2" or a numeric 2.

2. did you intend to fix the lookup value $C$2. if you put the formula in a
range in column M, they will all return the same value.

Assuming you want a numeric 2 and a relative row reference for the lookup
value, this would put the formula in M2:M4 of Sheet2:

Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),2,VLO OKUP($C2,Sheet1!$A:$C,2,FALSE))"

if you actually intended a text "2"
Worksheets("Sheet2").Range("M2:M4").Formula = _

"=IF(ISNA(VLOOKUP($C2,Sheet1!$A:$C,2,FALSE)),""2"" ,VLOOKUP($C2,Sheet1!$A:$C,2,FALSE))"

the IF statement should be on one line, so watch for word wrap.

"Faboboren" wrote:

I want to add some details:

I need the macro to be reapeated in a range at M column

"Faboboren" wrote:

Hi,

I am looking a code to create a macro, where I can insert the if vlookup
below (to avoid N/A)

Thanks

=IF(ISNA(VLOOKUP($C$2,Sheet1!$A:$C,2,FALSE)),"2",V LOOKUP($C$2,Sheet1!$A:$C,2,FALSE))
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro - Vlookup orquidea Excel Discussion (Misc queries) 6 May 14th 08 04:42 PM
vlookup macro MikeD1224 New Users to Excel 1 June 16th 07 04:37 AM
VBA Macro for VLOOKUP Myrna Rodriguez[_3_] Excel Programming 0 December 13th 05 09:22 PM
Please help.. VLookup Macro STEVE BELL Excel Programming 3 August 3rd 05 05:31 PM
VLOOKUP in a Macro Peter Hesselager Excel Programming 5 May 7th 05 01:28 AM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement