VLookup Syntax

ketbdnetbp · #1 04-08-2014, 08:00 AM

The following code is part of a loop but, it is not working. I believe its a syntax problem, any suggestions...
If Worksheets(iDSK2).Cells(intRow, 5).Text = "Associate" Then

Worksheets(iDSK2).Cells(intRow, 24).Select
ActiveCell.Formula = "=VLOOKUP(CONCATENATE(A" & intRow & ", "" "", B" & intRow & "),Sales!$A$6:$J200,4,FALSE)"
Else
Worksheets(iDSK2).Cells(intRow, 24).Select
ActiveCell.Value = "0.00"
End If
Dim valCheck As String
valCheck = Worksheets(iDSK2).Cells(intRow, 24).Text
If valCheck = "#N/A" Then
Worksheets(iDSK2).Cells(intRow, 24).Value = 0
End If

Pecoflyer · #2 04-08-2014, 08:38 AM

I'm not versed in VBA ( and actually this is a non VBA forum) but what is CONCATENATE(A" & intRow & ", "" "", B" & intRow & ") supposed to do ?

Non VBA syntax is =concatenate(string1,string2, etc..) or without the concantenate function = string1&string2&...

p45cal · #3 04-12-2014, 03:18 PM

try:

Code:

  If .Cells(intRow, 5).Text = "Associate" Then
    .Cells(intRow, 24).Formula = "=VLOOKUP(CONCATENATE(A" & intRow & ", "" "", B" & intRow & "),Sales!$A$6:$J$200,4,FALSE)"
    If .Cells(intRow, 24).Text = "#N/A" Then .Cells(intRow, 24).Value = 0
  Else
    .Cells(intRow, 24).Value = "0.00"
  End If
End With

but…
wouldn't, in cell X7 (just as an example row) the formula:

Code:

=IF(E7="Associate",IFERROR(VLOOKUP(CONCATENATE(A7, " ", B7),Sales!$A$6:$J$200,4,FALSE),0),0)

do the same thing without any vba at all?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using multiple formulas in one (IF, AND and SUM) gives syntax error	markdeniet	Word	1	02-05-2014 05:17 AM
Syntax to use OFFSET() in a cell formula?	dlowrey	Excel	3	07-09-2013 09:15 AM
Command line syntax for 2010	asearle	Word	1	08-07-2012 05:42 AM
Syntax Help Needed for Using AND Statement in NEXIF	robpgreer	Mail Merge	10	08-11-2011 04:58 PM
Syntax help needed	blazzercat	Excel	3	07-09-2009 02:15 PM