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
– David Zemens, Commented Jun 13, 2014 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
– David Zemens, Commented Jun 13, 2014 at 17:39
Very true. I did not phrase that good at all. I apologize for that. Found a site that answered my question : dummies.com/how-to/content/vba-for-dummies-cheat-sheet.html — Adam Walker
– Adam Walker, Commented Jun 13, 2014 at 18:13

Community · Accepted Answer · 2017-05-23 10:25:05Z

2

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.

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Jun 13, 2014 at 17:43

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set String value to Variable based on cell in Visual Basic

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related