Excel VBA - Set variable to text and cell value

Question

Still working on my first VBA script, but I'm stuck. I want to set a variable to a cell value AND text but it only lets me set a variable to one or the other, not both.

Example:

Dim Week As Range
Dim WeekCurrent As String

Set Week = .Range("F4")

If Week.Value = "Initial Week" Then
    WeekCurrent = "Initial Week"
Else
    WeekCurrent = "Week #" Week.Value
End If

Desired Result: "Initial Week" or "Week #x" to call on later.

I can either set it to the "Week #" text or to Week.Value but not both. I'm sure I'm using the wrong declaration or something.

As of right now, my workaround involves changing any instances I want to use this variable into an IF THEN ELSE and doubles the amount of coding because I have to duplicate the code. Thanks for the feedback.

what are you trying to achieve with WeekCurrent = "Week #" Week.Value ??? Also, try to avoid declaring a variable with the name of Week which is an Excel saved word — Shai Rado
– Shai Rado, Commented Feb 21, 2017 at 3:32
As I mentioned. I want to call on that string where week.value is a number and it outputs "Week #n" where n = week.value. — BetaOp9
– BetaOp9, Commented Feb 21, 2017 at 3:35
I knew it was something stupid I was missing. This was the fix. Thanks. — BetaOp9
– BetaOp9, Commented Feb 21, 2017 at 3:39

Jeff Grove · Accepted Answer · 2017-02-21 04:53:05Z

1

Another way of doing this may be:

If IsNumeric(Week) Then WeekCurrent = "Week #" & Week Else WeekCurrent = Week

answered Feb 21, 2017 at 4:53

Jeff Grove

262 bronze badges

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

3 Comments

BetaOp9 Over a year ago

Nice! Like this, more efficient and flexible

Jeff Grove Over a year ago

I'm sure you may be aware you can refer to excel cells using Cells(row#,column#).

Jeff Grove Over a year ago

Then by using variables for the row and/or column numbers you can be a bit more dynamic on which cell you reference or easily construct code to loop through and process a range of cells.

Collectives™ on Stack Overflow

Excel VBA - Set variable to text and cell value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related