VBA Excel string datatype variable assignment

Question

I am trying to set a value for CurrentClient Variable i have defined below and then using it to count how many times it Occurs in a range. I am not sure what am i doing wrong here . It is giving me error: "runtime error 9 subscript out of range" on step where it is assigning the value from Sheet 2 cell A2 to Currentclient.

Please help.

Sub GetValue()

Dim ClientCnt As Integer
Dim CurrentClient As String

CurrentClient = Sheets("Sheet 2").Range("A2").Text

ClientCnt = Application.WorksheetFunction.CountIf(Range("A:A"), CurrentClient)

End Sub

A2 have a text value which i need to use to set the ClientCnt. I have used variant also instead of String datatype and it is still not working. — CuriousKid
– CuriousKid, Commented Sep 18, 2014 at 18:40

Gary's Student · Accepted Answer · 2014-09-18 18:40:09Z

5

Use Sheet2

rather than

Sheet 2

answered Sep 18, 2014 at 18:40

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

6 Comments

Gary's Student Over a year ago

Subscript out of range is the critical clue!!

CuriousKid Over a year ago

OMG! spent 30 minutes... on this I totttaly missed that. THanks Gary's Student

Gary's Student Over a year ago

@Prat14 _________sometimes error messages are useful.

CuriousKid Over a year ago

I am new to VBA programming and getting use to it anyways thanks for this lesson i will keep error messages in mind now.

Gary's Student Over a year ago

@Prat14 I have made this exact error many times myself. So answering this Post was like greeting an old friend!

|

Collectives™ on Stack Overflow

VBA Excel string datatype variable assignment

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related