stuck with vba formula

[paulsolar]

Hi All

I'm having a problem with the code below trying to get the formula correct, I cant get the LastRow1 part correct.

I've tried everything, the first part INDIRECT(""E"" & ROW()) is OK, it's the (INDIRECT(A1:""A"" & LastRow1)) bit I'm having problems with.

Any help would be appreciated

Cheers

Paul

Sub match_sales_manager()


Dim r As Range
Dim LastRow As Long
Dim LastRow1 As Long


    With Sheet5
        LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    'MsgBox LastRow1


With Sheets("Sheet5")


   LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
    
   For Each r In .Range("E1:E" & LastRow)
        If r.Value <> "" Then
          r.Offset(0, 1).Value = "=VLOOKUP(INDIRECT(""E"" & ROW()),(INDIRECT(A1:""A"" & LastRow1)),3,0)"
      End If
    Next r
End With


End Sub

 

[sheetspread]

If you want quotes around the vlookup range in the indirect:

"=VLOOKUP(INDIRECT(""E"" & ROW()),INDIRECT(""A1:A" & LastRow1 & """),3,0)"

VBA will often not print erroneous formulas to the worksheet, so it's better to omit the = sign, see what actually appears, and debug.

 

[Rick Rothstein]

Since you are filling in each cell's formula manually, you do not need to make them copyable (that is, you do not need to use INDIRECT as you already know the addresses to use for each of them). I think this like of code should work...

[table="width: 500"]
[tr]
	[td]r.Offset(0, 1).Value = "=VLOOKUP(E" & r & ",A1:C" & LastRow1 & ",3,0)"[/td]
[/tr]
[/table]

Note: I changed your table array reference from A1:A# to A1:C# since you are referencing Column 3 of that table array.

 

[paulsolar]

Hi Sheetspread

Brilliant thanks, you nailed it. It would also help if i could copy my own formula (cant believe i was trying to lookup an offset when i only specified a single column) should have been "A1:C"......

Quick question for you, I have 4 out of 180 showing as #N/A's, but I have chosen exact matches using the 0 at the end of the vlookup, all the data is correct so i wasnt expecting any #N/A's as everything is exact. Any suggestions as to why this would happen?

cheers

Paul

 

[paulsolar]

Hi Rick

Many thanks for your reply, you spotted my deliberate mistake as well, I tried your code but I seem to have a proble with it. I can see what you are doing and it looks correct to me but i'm getting a 1004 error?

cheers

Paul

 

[Rick Rothstein]

Hi Rick

Many thanks for your reply, you spotted my deliberate mistake as well, I tried your code but I seem to have a proble with it. I can see what you are doing and it looks correct to me but i'm getting a 1004 error?

Maybe you need to concatenate the worksheet reference into the formula? Neither you nor sheetspread did that, but since LastRow1 is calculated from a different sheet than LastRow, perhaps one of the ranges is looking at the sheet where the formula in being installed but should be looking at the other sheet instead? Again, I repeat, though, neither you nor sheetspread referenced a different sheet in what has been posted so far, so I am not so sure of this.

 

[paulsolar]

Hi Rick

I'll have a look in the morning, but i think youre right

Cheers

Paul

 

[sheetspread]

4 out of 180 showing as #N/A's, but I have chosen exact matches using the 0 at the end of the vlookup, all the data is correct so i wasnt expecting any #N/A's as everything is exact.

(If you still get an error after including Rick's range/sheet corrections) has this data been typed/pasted to the sheet or downloaded from somewhere? You can check for possible text/value format differences that might jam the vlookup by copying and pasting from the lookup value cell to the lookup range cell (or vice versa) and seeing if the #N/A goes away. Check for extra spaces too.