Set String value to Variable based on cell in Visual Basic

Question

I have a simple question that should be simple to answer. I am trying to make a string reflect the value of a cell. The naming scheme of the spreadsheet that it will import is the value of "(CSV) & ("[1].csv") with no space. CSV is the value of B5. If the variable CSV = WASHINGTON I need the line to read

Windows(WASHINGTON[1].csv).Activate

Below is what I have. I am having an error with the CSV Variable.

Sub Import_CSV()

Import_CSV Macro

    Dim CSV As Long
    Application.DisplayAlerts = False
    Windows("Records.xlsm").Activate
    Worksheets("Data").Activate
    CSV = Cells(5, "B").Value
    Windows(CSV & "[1].csv").Activate
    Rows("1:2").Select
    Range("A2").Activate
    Selection.Cut ... etc

Any Help is greatly appreciated.

*What is my error?* WHy don't you tell us what the error is, and then perhaps we can tell you how to fix it. — David Zemens, Jun 13 '14 at 17:39
You have declared `CSV As Long` but you are expecting a `String` data type. That will almost certainly raise a mismatch error for any non-numeric value. — David Zemens, Jun 13 '14 at 17:39
Very true. I did not phrase that good at all. I apologize for that. Found a site that answered my question : http://www.dummies.com/how-to/content/vba-for-dummies-cheat-sheet.html — Adam Walker, Jun 13 '14 at 18:13

score 2 · Accepted Answer · edited May 23 '17 at 10:25

You have declared CSV As Long but you are expecting a String data type. That will almost certainly raise a mismatch error for any non-numeric value.

Declare Dim CSV as String and that should resolve it.

Note: This assumes that the specified file is already open. If not, you will get an error on the .Activate statement.

You should also avoid using Activate and Select methods. There are some resources on here that explain why:

How to avoid using Select in Excel VBA macros

Please, in the future do a better job describing what your actual problem is -- if you get an eerror message, indicate what that message is, and on which line it raises. This is very helpful for others who are trying to assist you.

Set String value to Variable based on cell in Visual Basic

1 Answers1