Free Microsoft Excel 2013 Quick Reference

how do you concatenate two variables in VBA

Need to concatenate the contents of two cells together, using VBA.
for example:

Cell A1 = HSBC
Cell A2 = LN equity

so that cell A3 would show exactly as follows: HSBC LN equity

I can do this on excel spread sheet as: Cell A3 = A1&A2
(indenting contents of A2 one space) however I need to do this in VBA

The beginning of my program is as follows but I get an error message
in line 2 of program.

If [C1] = "United Kingdom" Then
[A1] & [A2]
.. . . . .
ElseIf [C1] = "France" Then
.. . . . .
Else [C1] = ""

need to do this for 15 other countries.

Your help in this would be very much appreciated.


How do i call a variable in VBA from one form to another.

i know that it can be done if the VBA is all on the same module through the macro title

i.e. macro1(variableName)

but i need it in a form



[ This Message was edited by: philhowell on 2002-06-18 06:40 ]

Mark > wrote in message
> In Excel -
> How do you get information typed in one cell to appear in another
> pre-choosen cell location.

You need to use the formula bar. Select the cell that you want to copy the
data into (say, A1). Then click on the " = " button on the formula bar.
(This means that the selected cell (A1) will show the result of a formula).
Then select the source cell (say, B1) and click enter (or click on the tick)
button. Whatever info is entered into B1 will also be displayed in A1.
Point to note: if you cut and paste cell B1 to any other cell, then the
info in cell A1 will be linked to the new address of cell B1. Ordinarily,
this is a good thing. However, it might not be what you want.


how do you create custom buttons in office 2007?

How do you attach a macro in excel 2003?

how do you open SPSS files in excel? Can you? thanks in advance...

How do you prevent duplicate entries in an excel spreadsheet?

In Excel -
How do you get information typed in one cell to appear in another
pre-choosen cell location.

I know how to do it in C++, but im not sure how to do it in VBA.

How do you put quotation marks in a string?

Thanks guys,

I'm using a macro to do a simple copy and paste of a website page (whole page in Internet Explorer). I call up IE and navigate using the VBA macro. Part of the data shows expressions like '1 / 2' or ''2 / 3' which is saying 'one out of two' and 'two out of three', etc.

The problem is when they are pasted on a worksheet, excel converts them to dates such as '1-Feb' or '2-Mar' . Don't want this.

I tried to pre-format the cells to text before running the macro, but it didn't work. It gave me a 5 digit date # ?

How do I disable the date recognition using a macro to copy from a website?

I am not using 'web query' by the way, and I know how to disable the date recognition by this method.

I'm using the following code basically:

' grab the whole screen & paste into Excel

  'Wait for page to load!
    If ie.ReadyState = 4 Then
       ie.Visible = False
       Exit Do
    End If




thanks for your help.

Hello everyone. Can you apply a variable that is defined in a VBA module, sub macro, to a formula in an excel spreadsheet. If so, how do you go about doing this?

How do you put the above formula into a cell using vba? Below is what I've come up with.

However, the second " cuts off the first one. Does anyone know how to get around this?

Hi, I'm referencing a user inputted variable in the data labels for a graph.

To the best of my knowledge, the only way to do the data labels is to type something similar to:
where 'Chartname'! is the chart sheet where the x labels are located. I can't do this... because "Chartname" is a
user input and a changing variable everytime. 

I don't really know what this problem is called and thus haven't been able to search for it very well... but what is the syntax for putting a user inputted variable (via text box) into something like x labels or quotes " " ?

I've tried everything, "=""'Chartname'""! etc all fail miserably...

Thanks for any help.

*** In Excel how do you .....

- code a formula in cell B1 to do the following ....?

If cell B2= anything from 0.001 to 1.999
Then I need cell B1 to show 2.000

If cell B2 = 0.000
Then I need cell B1 to show 0.000

But If cell B2 is anything from 2 or higher
Then I need cell B1 to be whatever number is in cell B2

Thanks ahead a time for assistance with my problem.

Greg: I've seen in this forum that you put some thumbnails in your posts. How do you do that...?

En español: Greg, un saludo. He visto que en algunos de sus posts, incluye un vista en miniatura y al hacer click en ellas se ve en tamaño normal. ¿Como hace Usted eso...?

Thanxs in advance.... Gracias anticipadas...

How do you put a variable in a solver function in VBA for the target value?? Thanks

I have a script that sets font color. I think it uses something called "myColor" to define the color, and then pulls the color from a palette of 57 colors.

But how can use the "color" property instead, so I can have a broader range of colors? I can't tell how it would fit into my code. "MyColor" doesn't even show up in Excel's help file.

Here is the code. How can I adjust it so that I can use, like, "RGB 0, 0, 255" instead of color 32?

Or how would you code it so you could set color that way?

Private Sub WorkSheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column < 8 Then Exit Sub
    If Target.Column > 9 Then Exit Sub
    Dim myColor As Variant
    Select Case Target.Column
        Case Is = 9
        Select Case Target
            Case Is = "Util, Gas", "Util, Phone", "Util, Power", "Bills, FAP, Jeff", "Bills, FAP, Pam", "Bills, Other":
myColor = 18
            Case Else
                Select Case Target.Offset(0, -1)
                Case Is = "Deb EO", "Deb WF": myColor = 55
                Case Is = "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam": myColor = 47
                Case Is = "Work, Jeff": myColor = 5
                Case Is = "Work, Pam": myColor = 13
                Case Else: myColor = xlAutomatic
                End Select
        End Select
        Case Is = 8
        Select Case Target.Offset(0, 1)
            Case Is = "Util, Gas", "Util, Phone", "Util, Power", "Bills, FAP, Jeff", "Bills, FAP, Pam", "Bills, Other":
myColor = 18
            Case Else
                Select Case Target
                Case Is = "Deb EO", "Deb WF": myColor = 55
                Case Is = "Dep Fi-Aid, Jeff", "Dep Fi-Aid, Pam": myColor = 47
                Case Is = "Work, Jeff": myColor = 5
                Case Is = "Work, Pam": myColor = 13
                Case Else: myColor = xlAutomatic
                End Select
        End Select
    End Select
    Me.Cells(Target.Row, "C").Resize(, 3).Font.ColorIndex = myColor
    Me.Cells(Target.Row, "H").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "K").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "M").Font.ColorIndex = myColor
    Me.Cells(Target.Row, "N").Font.ColorIndex = myColor

End Sub
Thanks in advance for any and all suggestions!

How do you subtract and add in the same column going in the decending order?
I have a worksheet and we need to subtract some and add others. How does
this work?

I use the subtotal function quite frequently totals for each client. When the
result is returned the client name is in bold type (Fine) but how do you get
the calculated total to be in Bold as well. At the moment i have to go
through and do this process manually.

since you cant hit enter to make 2 lines in one cell how do you do it?

How do you printe all the data in a cell. Excel only visibily displays up to
1024 characters but you can have more data than that. How do make excell
print the entire cell data?


I am trying to program in VBA for excel. As a JAVA programmer often i do char++ if I want to move from 'A' to 'B' for example. However there dont seem to be a char data type in VBA, so how do you cycle thru letters in VBA?

Thanks for your help!

How do you name a tab in vba? I am attempting to transfer a template to a new file and then create about 20 tabs with a custom populated template for different groups. The problem I'm having is changing the name of the tab to the name of the group, which is listed in cell A1 of each worksheet. It's probably something simple like Sheetname = "Group 1" (or better yet, Sheetname = Range("A1"), but I can't figure it out.

Thanks for any assistance!!!


Split From

Thanks guys.

I'm trying to copy some of the results.

How do you combine two variables with text into one cell.

For example i want range("a1") = var1"."var2 so it would display var1.var2 if var1 = var1 and var2=var2


I have a Master Workbook, which has VBA to save as someone where else with a different name.

But I need to add something to the VBA code that clears out the data, based on the workbook's name.

Basically on start-up, IF the workbook's name is "Days to Invoice", then run CLEARALL Macro..

If the name is anything other than "Days to Invoice" I need it to be read-only.

Which leads me to another question..
How do I save a file in VBA to read-only?

2 questions in one post, woohoo