Excel VBA questions

[arnabmit]

Hi,

I am trying to use the line

iRow = [COUNTA(Sheet1!A:A)]

But instead of Sheet1 I want to use a variable shtName

Please help me with the syntax

 

[eduzs]

Like this: ?

ShtName = "SheetName"
Debug.Print Application.CountA(Sheets(ShtName).Range("A:A"))

 

[Fluff]

How about

iRow = Evaluate("counta(" & ShtName & "!A:A)")

 

[Norie]

You can't use a variable with that syntax, try this.

iRow = Application.CountA(Sheets(shtName).Range("A:A"))

 

[arnabmit]

ADVERTISEMENT

Thanks all! Really helpful

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[arnabmit]

Another question please (Not sure if I should post all questions in a single thread, so as to not unnecessarily increase thread count, or in separate threads based on exact topic)

Note: ShtName is a public variable

    Dim dbSheet As Worksheet
    Dim dbTable As ListObject

    Set dbSheet = ThisWorkbook.Sheets("Form")
    Set dbTable = ThisWorkbook.Sheets(ShtName).ListObjects(ShtName)


    ActiveSheet.lstPrev.RowSource = dbTable.DataBodyRange.Address(External:=True)

I am trying to get the content of a formatted table into an ActiveX list box lstPrev. I am getting error 438 "Object doesn't support this property or method".

What am I doing wrong?

 

[Fluff]

Different questions need their own threads, so you will need to start a new thread for this question. Thanks