Free Microsoft Excel 2013 Quick Reference

Using A Variable In Concatenate Formula

Newbie having trouble with the following. I need to step the following code line down one row during a loop using a variable i. Col will remain the same.

Any help would be greatly appreciated.


Nevermind....I solved it. Used Range call outs and the & to concatenate and it worked fine....Thanks...

Post your answer or comment

comments powered by Disqus

I have different workbooks with data.
eg workbook "data_red". workbook "data_blue" and workbook "data_green". The sheetnames are the same in the different workbooks.

Now I have a workbook called "Overview". In this workbook I have a formula in cells A10..A110 that is linked to data in workbook "data_blue" (=[Data_blue.xlsx]Sheet1!$A$1 and so on.

If I want to use the data from the other workbook, eg "data_green.xlsx", I have to change al the formulas in the cells of workbook " Overview".

What I like to do is to put a variable in the formula =[Data_blue.xlsx]Sheet1!$A$1 on the position of the workbook [Data_blue.xlsx]. So that I have a cell B1 in the Overview workbook where I put the name of one of the other workbooks, eg workbook "data_blue". And that the formula now is changed to get the data from the other workbook.

Hopefully do you understand what I mean. And do you have a solution for it

I write quite a few macros that require a vlookup formula. The table array is often dynamic which means I must change my program. I am trying to find a way to create the formula to accomodate the growth of the table array. I can count the number of rows in the table by the counta function, but then when I try to use that variable in the formula it bombs.

Any suggestions would be appreciated.


I have tried to use a variable in this Range satement but I get a Select Method of Range Class Failed. I need to reformat this as I move data.
X is a changing row variable.
Any help would be greatly appreciated
Dim Temp as String
Temp = "O" & X

If TopInches < 10 Then
Selection.NumberFormat = "## ""' "" 0 ##/##"
Selection.NumberFormat = "## ""' "" #0 ##/##"
End If

Can you us a "variable" in a range name without having to INDIRECT() to
another cell?

A1 has a number in it
rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges

I want to use a vlookup to one of the above ranges. Something like:
VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0)
VLOOKUP("bongo", "rn"&A1&"mw", 0)

of course those formulas do not work.....

thanks in advance

Can you us a "variable" in a range name without having to INDIRECT() to
another cell?

A1 has a number in it
rn1mw, rn2mw, rn3mw, rn4mw, rn5mw are valid ranges

I want to use a vlookup to one of the above ranges. Something like:
VLOOKUP("bongo", CONCATENATE("rn",A1,"mw"), 0)
VLOOKUP("bongo", "rn"&A1&"mw", 0)

of course those formulas do not work.....

thanks in advance

How can I link one file to multiple directories using a varaible in the path name. e.g +c:data +[variable]file.xls

Hi, not sure if this is the right place to post this but...

I want to use a variable in the following piece of code

 XMLNode = XMLDOC.SelectSingleNode("Files/Infrastructure[@Name = 'variable name']/Description") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I declare one it has " " around it and doesn't work. Any ideas on what I can do?


Hello everyone,

I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
{My Code Here}
End If

If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
{My Code Here}
End If
Etc, Etc, Etc

What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
 If Intersect(Target, Range("D" & t)) Is Nothing Then
  Exit Sub
    Call Subtest
Application.ScreenUpdating = True
End If
Next t
End Sub
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?

Thanks in advance,

I have written an excel macro that makes a copy of the current tab, asks for the user to enter a variable (called "strFile" in this case), then labels the new tab with this variable, and uses the variable in cells.

However I have embedded scrolling charts that use named ranges in the original tab. When the tab gets copied, the charts continue to reference the original tab and not the new one.

Does anyone know the correct VB code so that a variable input string can be used as part of the 'values' and 'x-axis labels' calculation?

The macro I have so far is -
Sub CopySheet()
Dim Response As Integer

' Displays a message box with the yes and no options.
Response = MsgBox(prompt:="Base new summaries on similar 'Unit Operation' resources (ie; Granulator to Granulator, or Coater to Coater). Are you sure you want to create a New Resource Summary Sheet based on this Resource?", Buttons:=vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbYes Then
strFile = InputBox("Enter The New Resource Name", "Enter The New Resource Name", "")
' The no button was selected.
Exit Sub
End If
If SheetExists((strFile)) = True Then
MsgBox "A sheet with this resource name already exists!"
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Copy After:=Sheets("Resource Utilisation")
ActiveSheet.Name = strFile

ActiveCell.FormulaR1C1 = strFile
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(Sheets(SName).Name))
End Function

and the approach I was going to take was -
Sub add()
' add Macro
' Macro recorded 29/06/2007 by tjs20186

lbls = "=" & strFile & "!LABELS"
ActiveCell.FormulaR1C1 = strFile
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = lbls
ActiveChart.SeriesCollection(2).XValues = lbls
ActiveChart.SeriesCollection(3).XValues = lbls
ActiveChart.SeriesCollection(4).XValues = lbls
ActiveChart.SeriesCollection(5).XValues = lbls
ActiveChart.SeriesCollection(6).XValues = lbls
ActiveChart.SeriesCollection(7).XValues = lbls
ActiveChart.SeriesCollection(8).XValues = lbls
ActiveChart.SeriesCollection(9).XValues = lbls
ActiveChart.SeriesCollection(10).XValues = lbls
ActiveChart.SeriesCollection(11).XValues = lbls
ActiveChart.SeriesCollection(12).XValues = lbls
ActiveChart.SeriesCollection(1).Values = "=" & strFile & "!OFF1"
ActiveChart.SeriesCollection(2).Values = "=" & strFile & "!OFF2"
ActiveChart.SeriesCollection(3).Values = "=" & strFile & "!OFF3"
ActiveChart.SeriesCollection(4).Values = "=" & strFile & "!OFF4"
ActiveChart.SeriesCollection(5).Values = "=" & strFile & "!OFF5"
ActiveChart.SeriesCollection(6).Values = "=" & strFile & "!OFF6"
ActiveChart.SeriesCollection(7).Values = "=" & strFile & "!OFF7"
ActiveChart.SeriesCollection(8).Values = "=" & strFile & "!OFF8"
ActiveChart.SeriesCollection(9).Values = "=" & strFile & "!OFF9"
ActiveChart.SeriesCollection(10).Values = "=" & strFile & "!OFF10"
ActiveChart.SeriesCollection(11).Values = "=" & strFile & "!OFF11"
ActiveChart.SeriesCollection(12).Values = "=" & strFile & "!OFF12"

End Sub

I cannot seem to use a variable in a save as path name
unless it is the last entry (file name). What is the
syntax for giving a subdirectory in the path a variable
linked to a range?

I am working on a project where certian pieces of data are pulled from a
linked document.

The problem, not all users (at other sites) use the same mapped letter for
the drive. Therefore I can't link via a static path. I have decided to
place this linked document on each users local drive, which is defined as
"D:USERS(username)documents. I have this defined in the macro as a

The question, how do I use this defined path from the macro when linking
within the workbook (ie cell a1 ='DataPath[workbook.xls]Sheet1'!$A$1). When
I enter this into the cell, it asks me to update values and define the path.
How do I link to the file using a variable path?

Code used to determine variable path:

Option Explicit
Global SharedDrive
Public Username
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
fOSUserName = ""
End If

Exit Function

MsgBox Error$
Resume fOSUserName_Exit

End Function

And in the ThisWorkbook object in the Worksheet_Open() event it would look
like this:

Private Sub Workbook_Open()
Username = fOSUserName
DataPath = "D:Users" & Username & "Documents"
End Sub

Thank you

Cell M8 in Sheet "Sched" is a variable from 1 to 12 (whole numbers only, representing months).

When a form control button is pressed, a selection from "Sched" (L8:Z41) is copied and its content is pasted (Value only) into another sheet in the same workbook. There are 12 additional sheets labeled 1 - 12

I would like the Macro to past the content into the correct sheet as stated in cell M8.

So if M8=3, I would like the content pasted into sheet "3".

Every other part of the Marco works, I just don't know how to select a sheet using a variable. I would like to avoid referencing the sheet by it's Index Value in case other sheets are added or re-arranged in the future.

My code is below with "???" where the variable should go.

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Hi all,

I have the following code which determines the amount of rows:
Dim lastrow As Long
lastrow = range("A6").End(xlDown).Row

I now need to use the "lastrow" in the formula from Bob

For Each cell In ActiveSheet.range("BD6:BD10000")
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
In other words all the spreadsheets start at BD6 but the lenght is
variable. I have tried to piece it together but cannot get it to work.



*** Sent via Developersdex ***


I am trying to do a countif statement that has a variable number in it. The formula works when it looks like

   ActiveCell.FormulaR1C1 = _
        "=COUNTIF(RC[-24]:RC[-1],""> 10"")/COUNT(RC[-24]:RC[-1])"
I am trying to make it so that the '> 10' part can be changed. I tried the below, but both come back as application-defined or object defined error 1004.

 " + CStr(price1)
   ActiveCell.FormulaR1C1 = _
        "=COUNTIF(RC[-24]:RC[-1],[" & mprice1 & "])/COUNT(RC[-24]:RC[-1])"

 " + CStr(price1)
   ActiveCell.FormulaR1C1 = _
        "=COUNTIF(RC[-24]:RC[-1]," & mprice1 & ")/COUNT(RC[-24]:RC[-1])"
Any helpful suggestions on how to get a variable in the formula? Thanks

I have had great success connecting to a access file through vba! However I am at a point where the column that I and querying could change. I would like some help altering me code. Thank you!

Dim cnn As New ADODB.connection
    Dim rst As New ADODB.Recordset
    Dim companyid As String
    companyid = Application.Transpose(Range("myid").Value) & "P"
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" mydatasource"

'This part works fine
    rst.Open "SELECT [" & companyid & "],[UM] FROM tblLineItems WHERE Item ='" &
ListBox1.Value & "' ;", _
             cnn, adOpenStatic

    With Me
    .itemlbl.Text = ListBox1.Value
'This part doesn't
    .pricelbl.Text = Format(rst! ["'" &companyid & "'"], "$0.00")
    End With
I have also tried
but it didn't work either

See attached for the error message

Just so you know if I don't use a variable in
It works fine.

So I really need to know how to put a variable in there!

I am using a user form text box to set a variable representing a column reference. I then want to use this variable name in a formula that pulls in the data from the correct column of a different sheet. After several days of research I think I am close:

User inputs G in the user form then

col = G

I then want to have a formula that uses col combined with row number to select proper cell. I believe the right way to do this is the INDIRECT function


I have tried many different variations and can get it to work when I use a cell reference i.e. A5 instead of a variable and when it is on the same sheet, but I have not found the right syntax that will produce the right cell reference when I use a variable to represent the column

I'm stumped.

I have a macro which defines a workbook selected by the user as the name ServicingInfoFile.
The macro then proceeds to write vlookup formulas in another workbook to pull data from the ServicingInfoFile workbook.
Only problem is that the code errors out at the vlookup formulas every time.
Can I not use a Variable in a formula?
Is it just my syntax?
Hep me!
Hep me!

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'" & ServicingInfoBook & "'!C1:C2,2,FALSE)"

I am trying to use a sheet name as a variable in a formula. I need to be able to reference data in cell a1 as part of the sheet name (sheet names include spaces) in a formula. This will enable me to manually change the sheet reference in cell one and have the formula reference the sheet I am specifying. It looks something like this:

----A -------- B
1 Cats
2 Dogs
3 Birds
4 Fish

I need cell b1 to utilize in a formula the data on sheet ‘my favorite pet [reference cell a1]’! so that I can manually change the type of pet in the future that is listed in cell a1 and have the formula reference this new sheet automatically. I DO NOT want to use a Function. I just simply need to know the nomenclature to make this happen. Thank you for your help in advance.

hello XLers

I think I'm missing something obvious here, but I could use some help.

I have code that, among other things, pastes formulas into cells. Part of
one of the formulas is:

FormulaStr9 = "=IF(OR .... more code here ... & INDEX(Sheet23!E:E,
MATCH(A8, Sheet23!A: A .....etc

All works well, but on other sheets I keep having to Replace Sheet23 with
Sheet24, Sheet 25, etc.

Now I have a variable called SheetName, but I can't get the syntax of the
formula to work when using it. How do I use SheetName in the formula? When
SheetName = Sheet23, the formula should give the same result as the one
shown above.

Much thanks for any help

Is there a way to use a variable for the table_array in a VLOOKUP, specifically, where the table_array is in another workbook? I'm not afraid to use a VBA function if necessary but it'd be nice to have the function all 'self-contained' using standard formula commands.

This is an example of what I'm looking to do:
Since a string is returned, this function fails.  I'm guessing I need a Range returned?

The "CONCATENATE("..." part is the piece causing me the issue. Depending upon what Form (radio) button the user clicks (that part is done), the table_array range will change.


Am trying to serialize an Excel form template I have created. While reading every post I could find regarding this function in Excel, I came across two promising solutions.

One uses vb code and a .txt file stored on a network (this is the preferable method since all employees would access the form from this location) I have sample code, but lack of Excel vb programming leaves me scratching my head. I have accomplished this with Word form templates and the serialization is working great, but the code and methodology used with Excel is unclear.

The second used a very short Concatenation formula. Very sweet but am curious whether there is a way to stop the formula from updating in subsequent 'open' dialogues. Also - the manner in which the company forms are accessed (from a network) is thru the softwares 'File New - On my computer..." dialogue. The first instance of 'opening' is File New, then Save or Save as.... anything beyond this point would be a direct 'Open'.

Would appreciate if anyone could shed some light on these two methods...

Regards - Lenny2


I am fairly new to VBA, but understand enough to get by. However, I am having difficulties using a variable with a formula I am trying to insert into my spreadsheet. Here is the code:

Dim i As Integer 
For i = 1 To Worksheets.Count 
    ActiveCell.Formula = "='" & Worksheets(i) & "'!K10" 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is with the "Activecell.Formula..." portion. I receive an error of '438' "Object doesn't support this porperty or method". I am attempting to have the macro populate each cell in my spreadsheet with this formula so the value in "K10" for each corresponding worksheet will be populated.

Any suggestions/Help? Thanks!

I want to use a variable within a reference being used in a fuction.

Index = 1 
rowvar = 6 
    ActiveCell.Formula = "=Info!R[6]C1" 
    ActiveCell.Offset(0, 1).Select 
    Index = Index + 1 
    rowvar = rowvar + 1 
Loop Until Index >= Range("Info!D9") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My intentions are to replace the number 6 with the variable rowvar so that the row will be incremented each time this loop is run.

I get an error if I try this, so there must be something wrong with my code, or at least an easier way to accomplish what I want.

I have a variable in a loop and want to concatenate a loop counter to it. For example instead of doing this:

value(1) = Cycle1.Value 'Cycle1.Value is the value of a textbox control
value(2) = Cycle2.Value 'there are four on this worksheeet
value(3) = Cycle3.Value 
Value(4) = Cycle4.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need something like this:

    value(j)  =  ? ' I don't know how to do this part
Next j 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It would be no problem if I could use and array for textbox controls, like
Cycle(j).Value, but this can't be done can it?


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