When to use Quotes

[SeniorTom]

When I use the following, I place the formula into the Spreadsheet and get the resulting row number.

Range(emailColumn & FirstRow & ":" & emailColumn & LastRow).Formula = "=MATCH(B2,'RosterFileName'!FullName,1)+3"

When I try this function in VBA I get different errors; syntax, compile error, etc. depending on where I place quotes.

For count2 = FirstRow To LastRow
MatchRow = Worksheet.Function.Match("B" & count2, 'RosterFileName'!FullName,1)+3

Any help on when and where to use an apostrophe (') and/or a quote (") would be appreciated.

Regards,
Tom

 

[Dryver14]

rather than worksheet.function try application.worksheetfunction

 

[SeniorTom]

MatchRow = Application.WorksheetFunction.Match("B" & count2, 'RosterFileName'!FullName,1)+3

I get a compile error - Expected expression ... It highlights the first apostrophe (')

 

[JoeMo]

Assuming FullName is a single column or single row named range with a workbook-level scope, try:

MatchRow = Application.Match("B" & count2, Range("FullName"), 1) + 3

BTW, the "1" argument after the range requires the named range to be in ascending order.

 

[RoryA]

You need to pass range objects to the function, not addresses:

MatchRow = Worksheet.Function.Match(Range("B" & count2), sheets("RosterFileName").Range("FullName"),1)+3

 

[SeniorTom]

oops

 

[SeniorTom]

The code is run in "workingResponse.xlsm" RosterFile is .xls and has "FullName" defined (FullName =Sheet1!$B$4:$B$96)

I'm now getting Subscript out of range. I have added some .activate to insure that the roster file is available.
I'm scratching my head ...

Public Sub insertEmail()
' This will insert the vlookup formula into a Formula column. Remote file is the roster woorkbook

ResponseFileName.Activate
Const FirstRow As Long = 2
Dim Count2 As Long

LastRow = Cells(Rows.count, "B").End(xlUp).Row                     ' last row of todays players

' Range(emailColumn & FirstRow & ":" & emailColumn &  LastRow).Formula = "=VLOOKUP(B2,'" & RosterFileName &  "'!roster,5,FALSE)"

Workbooks(RosterFileName).Sheets(1).Activate    ' roster file
For Count2 = FirstRow To LastRow
MatchRow = Worksheet.Function.Match(Range("B" & Count2), Sheets("RosterFileName").Range("Fullname"), 1) + 3
Next Count2

RosterWB.Close savechanges:=False
End Sub

 

[SeniorTom]

I found the problem or at least a problem. Because I get the Member from Response.xlsm and then want to match it to Roster.xls, I have to active the sheet I'm getting data from or putting the data to. "Subscript out of range" was because Response.xlsm was active when I was trying to address an inactive sheet & name. Sheets("RosterFileName").Range("Fullname") This is a start. Thanks for your guidance, it helped me see my logic error.
Regards,
Tom

... More questions will be forthcoming ...

 

[RoryA]

I have to active the sheet I'm getting data from or putting the data to.

You almost never have to activate anything in Excel to use it. Simply qualify the sheet with a workbook - for example:

Workbooks("Roster.xls").Sheets("RosterFileName").Range("Fullname")

 

[SeniorTom]

Rory, Thanks

I don't know why the activate worked, but when I used an Application.match & fully qualified reference, the activate was not needed.

Tom