Array error in VBA

Question

Dim scene(1 To 2) As String
Dim currentsheet As String
scene(1) = "Good"
scene(2) = "Better"
currentsheet = scene(Worksheets("Input").Cells(j, 1))

j variable keeps on changing but the first column of Input sheet only has vales of 1 or 2 so at each row of currentsheet there should be either be a value of "Good" if Worksheets("Input").Cells(j, 1)= 1 or "Better" if Worksheets("Input").Cells(j, 1)=2

This gives me a type mismatch and I am not able to find out the error as I have already declared both scene array and currentsheet as string.

Are you sure that Worksheets("Input").Cells(j, 1) is always 1 or 2 — Jeanno
– Jeanno, Commented May 11, 2015 at 20:54
Your code works for me. Are you sure the 1s and 2s in the worksheet are not numbers stored as text? — vacip
– vacip, Commented May 11, 2015 at 20:55
@Sorceri - J is to loop through all the rows of input sheets, but the first column always have values as 1 or 2 , so value of J doesn't matter. — Meesha
– Meesha, Commented May 11, 2015 at 20:56
Try actually specifying which property you want from the cell, for example .Cells(j, 1).Text — SierraOscar
– SierraOscar, Commented May 11, 2015 at 20:57

SierraOscar · Accepted Answer · 2015-05-11 20:59:38Z

1

Try:

currentsheet = scene(CInt(Worksheets("Input").Cells(j, 1).Value2))

If your cell really does just have "1" or "2" then it shouldn't make a difference, but the index of your array is defined by an integer value - so casting the contents of the cell to an integer should assist.

answered May 11, 2015 at 20:59

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

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

Collectives™ on Stack Overflow

Array error in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related