When to use Quotes

SeniorTom

Board Regular
Joined
Jun 5, 2017
Messages
96
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 is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
MatchRow = Application.WorksheetFunction.Match("B" & count2, 'RosterFileName'!FullName,1)+3

I get a compile error - Expected expression ... It highlights the first apostrophe (')
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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 ...
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top