When to use Quotes

SeniorTom

Board Regular
Joined
Jun 5, 2017
Messages
89
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

SeniorTom

Board Regular
Joined
Jun 5, 2017
Messages
89
MatchRow = Application.WorksheetFunction.Match("B" & count2, 'RosterFileName'!FullName,1)+3

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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,705
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming FullName is a single column or single row named range with a workbook-level scope, try:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,824
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

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

SeniorTom

Board Regular
Joined
Jun 5, 2017
Messages
89

ADVERTISEMENT

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 ...

Code:
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

Board Regular
Joined
Jun 5, 2017
Messages
89
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,824
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

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

SeniorTom

Board Regular
Joined
Jun 5, 2017
Messages
89
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
 

Forum statistics

Threads
1,148,291
Messages
5,745,897
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top