Free Microsoft Excel 2013 Quick Reference

Using quotation marks with quoted string in vba

I have a need to used the formula:

.Range("B13").Formula = "=VLOOKUP(MAX($B$3+Cap, VALUE(TEXT(CurrentSum+A13, "0.000"))), Month11Values, 2, FALSE)"

within a VBA module.

I need to be able to do this at runtime, as I will be running different numbers through B13 as part of the macro, and only the first one can be found with this formula.

There are a few problems here:

First, note the "VALUE(TEXT(..., "0.000"))" Call. For some reason, I was having all kinds of VLookup problems stemming from fills that changed cells from .02 to .019999999999 for instance. I found this Value(Text()) call fixed that - Don't ask me why...But if you can give me an easier solution, that would be great.

Secondly, my main problem stems from the fact that I have a second set of quotes that must be within the formula around the 0.000. They need to be there as this is not actually the number 0, but a numerical format. Without those quotes, it reads it as 0 and causes an error. In VBA, it recognizes the first quote of "0.000" as the closing of the first quote in my above call. Thus, I get an error because there's more stuff after that closed quote, which VBA doesn't like.

I've tried:


	VB:
	
.Range("B13").Formula = "=VLOOKUP(MAX($B$3+Cap, VALUE(TEXT(CurrentSum+A13, " & "'0.000'" & "))), Month11Values, 2, FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and about every other combination of single and double-quotes imaginable, but can't seem to make any of them work.

Thanks in advance for any help you can give me!

P.S. As you probably guessed, CurrentSum and Month11Values are named ranges in my WB. I am certain that the formula works when put into the worksheet manually.


Post your answer or comment

comments powered by Disqus
Hello friends,

I need to concatenate the " (Quotation Mark) to a string. How can I do this ? (In VBA)

Example:

A string with John and I want the end result to be "john", concatenate the Quotation Mark in the begining and in the end.

Does anyone know how to do this ?

Thank you,
Bleeding_me

Hello:

Is it possible to extract words/numbers in quotation marks from a string (see eg) using formula?

eg This is “Tom12” brown.

Sean

Hello,

I need to search an Excel file in my computer which has a specific VBA
subroutines I wrote. I tried using Windows file search feature to search the
text string. It does not work. And it is almost impossible to open every
Excel file to seach for the text string in the VBE.

Any suggestions on how to conduct a file search by looking for a text string
in VBA code?

Thank you in advance.

I am trying to use the R1C1 reference style in my code but intead of numbers I want to use variables. Right now I have the line:

ActiveCell.FormulaR1C1 = "=MIN(R" & intRightR & "C" & intRightC & ":R" & intBottomR & "C" & intBottomC & ")"

and depending on an evaluation of two numbers, the variables will change and the loop will start over. The only problem is that this line of code is not working. I saw Derk's post of trying something similar to:

Range("A1").formulaR1C1="=SUM(R" & row1 & "C" & col1 & ":R" & row2 & "C" & col2 & ")"

Could anyone help me out with this. Thanks in advance!

I have been shown, by members of this forum, how to build up SQL strings in VBA.

The following code is successful (Line nos only to help explain problem)

Code
Function MakeSQL()
 Dim strSQL As String
1 strSQL = " SELECT  WMORDER.WM_STATUS, WMORDER.WM_ACCOUNT, WMORDER.WM_INVOICE_DATE, "
2  strSQL = strSQL & "WMORDER.WM_ORDER_NO, WMLABEL.WL_NOMCC, WMLABEL.WL_QUANTITY, WMLABEL.WL_MANUF_OR_MERCH,
WMLABEL.WL_HEIGHT, WMLABEL.WL_MADE_QTY "
 
3  'strSQL = strSQL & "WMLABEL.WL_WIDTH "
4  'strSQL = strSQL & "WMLABEL.WL_MATERIAL, WMLABEL.WL_ADHESIVE, WM_LABEL_ADHESIVE.ADHESIVE_DESCRIPTION "
5 strSQL = strSQL & "FROM "
6 'strSQL = strSQL & "WINDMILL.WM_LABEL_ADHESIVE WM_LABEL_ADHESIVE, WINDMILL.WM_LABEL_MATERIAL WM_LABEL_MATERIAL
"
7 strSQL = strSQL & "WINDMILL.WMLABEL WMLABEL,WINDMILL.WMORDER WMORDER "
8 strSQL = strSQL & "WHERE "
9 strSQL = strSQL & "WMLABEL.PARENT_RECORD = WMORDER.THIS_RECORD "
10 'strSQL = strSQL & "AND "
11 'strSQL = strSQL & "WM_LABEL_ADHESIVE.ADHESIVE_KEY = WMLABEL.ADHESIVE "
12 'strSQL = strSQL & "AND "
13 'strSQL = strSQL & "WM_LABEL_MATERIAL.MATERIAL_KEY = WMLABEL.WL_MATERIAL "
 
 
 MakeSQL = strSQL

End Function
Several of the lines have been REM'd out in my attempts to narrow down the problem. In particular line 3.
If I remove the REM and use this line the code does not work and I get an error message "SQL Syntax error"
If I put the field WMLABEL.WL_WIDTH as an item in line 2 the code does work and the query returns results.
I can't see where I have gone wrong and would be very greatful for some help.
John Southern

Hi fellows,

I have a question, can I fill a cell with a color in VBA? If is that possible, how can I do it?

Kind Rewards.

Pedro

How do I convert a date to a string in VBA?

	VB:
	
 
 
 'a is being read from a cell in excel that is a date
a = "2/3/2006" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So how do I get the variable a to act like a string. When I attempt to add to another string in excel I receive an error.

Thanks in advance.

I am trying to use the following line in a macro and it's not working:

Range("O2").Value = "=IF(AND(B2=30,$K2=" ",$L2=" "),$H2,0)"

I get a "Compile error: Expected: end of statement" for this syntax, and I believe this is because Excel is interpreting the 2nd quote as the end of the statement and it doesn't know what to do with the rest of the formula.

I have seen several posts on similar issues, but am not having any luck using double or triple quotes and the Chr(34) that I have also seen suggested isn't working either. I am probably just using the incorrect syntax but am not sure what to do.

Any help would be greatly appreciated!
Thanks

I have this function that takes in a string of words separated by commas. In this case it is:

"Production, Production Goals, Daily Cost, Total Downtime"

Forgoing the encasing quotes, the commas, by my count are at characters' 11, 29, and 41.

The code below should get each word by itself with no spaces or commas showing, but when I run the code. I get for each iteration (both the starting and ending character count and the extracted string:

1st: 1, 11, sExtract = "Production"

I am trying to use the Vlookup function in VBA and am having some difficulty.

All of the variables in the formula are correct:
Cusip = "38142B872" - its in quotes when I hover over it
CusipColun6 = 5 as expected
Finalrow6 = 3109 as expected
RTColumn6 = 6 as expected

I am convinced this problem has to do with Cusip being a string and having "" around it.

Any ideas on how to fix this would be most helpful.

Thanks

Code:


Hi All,

I am currently coding up a VBA macro that will counts up the number of occurrences of a particular event for a particular date. The problem is that it keeps returning zero as the result. I thought it might have been a data type mismatch issue as the cell format is a date in the mm/dd/yyyy format but the data is stored as a string so that I can easily manipulate it with the built in VBA functions. I tried changing the data type but still received zero as the result. 'a' and 'c' refer to cell ranges, criteria is a string of characters and criteria2 is a string that pulls in a date in the format of mm/dd/yyyy. I know this code works when I don't use dates but I can't figure out why it doesn't work with dates. Any help is greatly appreciated.

Thanks,

Sally

Code:
criteria = Application.Workbooks(FileName).Worksheets("Day_Counts").Range("B1").offset(0, column).Value
            
criteria2 =  Application.Workbooks(FileName).Worksheets("Day_Counts").Range("A2").offset(row, 0).Value
criteria2 = Format(criteria2, "mm/dd/yyyy")

a = Application.Workbooks(FileName).Worksheets(FileNameNE).Range("G2:G" & EOF).Address

c = Application.Workbooks(FileName).Worksheets(FileNameNE).Range("D2:D" & EOF).Address

result = Evaluate("SUMPRODUCT((" & a & "=" & Chr(34) & criteria & Chr(34) & ")*(" & c & "=" & Chr(34) & criteria2 & Chr(34) &
"))")


Hello

I like to call the worksheetfunction MATCH in VBA. I am using the
formula
=MATCH(2;1/(2:2<>"")) for getting the last used column. How do I
programm that with VBA?

Application.WorksheetFunction.Match( ... ???? )

Thanks for any hints and examples

regards
Mark

Hi,

I'm trying to pass this function to a cell within VB:

=IF(ISERR(VLOOKUP(U2,'Anderson List'!$A$3:$F$1945,1))=TRUE," MISSING
",IF(VLOOKUP(U2,'Anderson List'!$A$3:$F$1945,1)=U2," OK
",VLOOKUP(U2,'Anderson List'!$A$3:$F$1945,1)))

but with the quotation marks foul things up. How do I pass the quotes?

Thanks!
--
Thanks!
Max

Here's my string:

"Column 16: Part Number"

and i want to extract 16. the number and text after the : changes.

in a cell, i'd use this formula (with my string in A1):

=MID(A1,8,FIND(":",A1)-8)

but in VBA, i can't use the FIND(":",A1) bit, or is there a way to?

Can someone lend a hand?

"Column " is constant, ":" after the number with no space between is constant. the number and text at the end varies.

I'm trying to use the following code to return an empty string in a
range of cells if any value in the range is FALSE:

Application.EnableEvents = False
For Each R In Range("A1:A7")
If R.Value = False Then R.Value = ""
Next
Application.EnableEvents = True

The problem is this code returns the actual quote marks "" in the
cells, not an empty string. R is set as a variant.

What am I doing wrong?

Thanks.

Dave

Hello all,

I have a problem with a sql statement in vba here is my code and I do
not know why because the same query in sql server works file and the
params strRoom is well get through my function

The sub that call this function i do like this:
strRoom = GetroomNumber(cndb, strRoomCode)

Option Explicit

' Description : Get Currency from Index
' Params : the strAssetCode as argument (index InternalCode)
' Returns : an array as String
' Author : RI
' Last Update : 11.05.06

Public Function GetRoomNumber(ByVal cndb As ADODB.Connection,
strRoomCode As String) As String
On Error GoTo GetRoomNumber_Err

Dim strSQL As String 'String for SQL statement
Dim rsroom As ADODB.Recordset 'Recorset

'Set a new recorset
Set rsRoom = New ADODB.Recordset

'SQL statement
strSQL = "SELECT CURRENCY FROM INDEXCURRENCY WHERE INTERNALCODE= '" &
strRoomCode & " '"

Debug.Print strSQL 'I can see the query and if I use the SQL it works

'Recorset and Connection
rsRoom.ActiveConnection = cndb
rsRoom.Open strSQL

GetRoomNumber = rsRoom.Fields(1).Value 'GetRoom = room number

'close the recorset
rsRoom.Close
Set rsRoom = Nothing
Exit Function

GetRoomNumber_Err:
GetRoomNumber = CVErr(xlErrNA)
Exit Function

End Function

Hi,
i'm trying to build a parameter in VBA as a list stings to pass it to
MSAccess procedure such as
"select ...from ...WHERE Strategy in ([@Strategy_List]);"

but I'm having problem with quotation marks around each string in a
list.. if only one name is in the list then query works just find but
additing second string to the list breaks it...

Set ParamStrategy = .CreateParameter("@Strategy_List", adVariant,
adParamInput)
'this one will work as only one string in the list
ParamStrategy.Value = "ALL_STRT"
param with two strings fails
'ParamStrategy.Value = "" & """ALL_G""" & "," & """ALL_O""" & ""

would appreciate any help on it
cheers
D.

How do I convert from A2:A6 to:
R2C1:R6C1

The goal is to populate a chart, and so far I cant get it to work unless I use the above format (of which I am drawing a total blank for its named type).

When manually selected via macro recorder:
I am trying: (and it does not work)
I thought an address is an address as long as its the same cells?
Where:
or in this case "A2:A6" as a string with quotes. (tested in my code via msgbox)

Any hints, tips, examples are appreciated.

Hi,
I have a file name which part of the name is "Querry SPVS_01_01_2011" or "Watch 01_01_2011".
The date will always be different so I am trying to put a wildcard so vba will detect SPVS in the name and do something.
The code does work for the "Watch" file coz there is an space in between the name and the date, but it doesn't work for the SPVS file.

I've been trying the following:

Sub test1

Dim Filename As String

Filename = ActiveWorkbook.name

If InStr(1, Filename, "SPVS_*") Then

wbt = "C:SPVS.xls"

ElseIf InStr(1, Filename, "WATCH") Then


wbt = "C:Watch.xls"

End If

Set wb2 = Application.Workbooks.Open(wbt)

End Sub
Where am I making a mistake?

Thanks,

I'm writing a macro, creating a formula for an active cell using "SUMIF". I
need to use cell references that I can control via variables (aka 'cells'
with rownum and colnum notations, or named cell ranges derived from same).
Syntactically VBasic doesn't understand nested quotation marks for named cell
ranges. Additionally using 'Range(Cells..' gives compile errors. Is it
possible to accomplish this? Sample code?? Most grateful for help!!

Hello

I'm a newbe, so please bear with me.

I'm having problems doing the following in VBA for Excel

A first piece of VBA script in an active Excel worksheet accesses an external DB and fills columns E from row 10 onwards with social security numbers. So far so good

A second piece of VBA script is supposed to get the first entry in the social security column (>E10) and use that value in a VBA macro that contains a sql command. The aim is for SQL to get to a second external DB and extract name and address for the person belonging to the social security number.
Name and number are to be put in column C and D of the same row the social security number was taken from.

Then the VBA script is to proceed to the next row in column E and do it all over again for the second social security number. And so on until there are no more numbers in column E.

If I put the social security number in the SQL string as an exact numerical value (like 1234567) it works. I don't now how to refer to sucessive rows in column E however.

Some stuff I'm using

Dim client As String
Dim Name As String

Const column_Client As String = "C"
Const Column_Name As String = "D"

' Column E is define as follows
Dim RgSocialSec As Range

Set RgSocialSec = Range("E10:E3000").Select

Really stuck with this
Many thanks for any help

Hi guys,

I'm a total newbie and am trying to use mmult in VBA..
All i want to do is multiply 2 matrices (3x3 and 3x1), so that I can use that matrix for more complicated calculations.

Need help on a UDF that returns the final result as a matrix (i.e. 3x1 matrix)
Below is my code but its returning error (#VALUE!)

	VB:
	
 
Function matt() 
    Dim P(1 To 3, 3) 
    P(1, 1) = 1 
    P(1, 2) = 2 
    P(1, 3) = 3 
    P(2, 1) = 4 
    P(2, 2) = 5 
    P(2, 3) = 6 
    P(3, 1) = 7 
    P(3, 2) = 8 
    P(3, 3) = 9 
     
     
    Dim Q(1 To 3, 1) 
    Q(1, 1) = -1 
    Q(2, 1) = -2 
    Q(3, 1) = -3 
     
    Dim result(1 To 3, 1) 
    result = Application.WorksheetFunction.MMult(P, Q) 
    matt = result 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance!!!

Matt

I'm trying to copy data from some worksheet to the active sheet. I've looked at several threads where examples were given with certain syntax, but none are working. For example, I have 2 worksheets named: 'Dec' & 'Templates'. I simply want to copy cell A1 of worksheet 'Template' to cell a1 of worksheet 'Dec' (using VBA). I've tried:

Range(A1).Value = Templates.Range(A1).Value
Range(A1).Value = Range(Templates!A1).Value
SomeString = Templates.Range(A1).Value
SomeString = Range(Templates!A1).Value

and 100's of variations of these with quotes & parenthesis in various posistions. I know that the statement:

Range(A1).Value = Range(B1).Value

works, so the problem is with specifying the worksheet override reference. Any ideas?

I need to filter a column that contains one of 3 values:
"" or "A" or "B"

The only way I can come up with to isolate the zero-length strings is to type


No luck finding an answer? You could always try Google.