Converting a formula to a VBA command -- string functions

[BlackieHamel]

I have a worksheet with long phrases in Column A, and in Column B, I have a 3-letter snippet from the string in A, thus:
STONESTHROW NES 196
REALIGNMENT LIG 35
QUALITYTIME LIT 87
QUALITATIVE LIT 87

In this case, I'm looking for the 3 letters beginning in the 3rd position. I use the MID() function to do this.

My question is about the number in Column C. This is a count of how many rows appear in the separate worksheet with that three-letter name (NES, LIG, LIT, etc.) The formula I use to get that (which I got from a helpful person here -- thanks, Mr.Excel wonderful people!) is
=COUNTA(INDIRECT("'"&B1&"'!A:A")).

It works great. But now I'm running into trouble automating this, as I'm trying to use a Loop function to generate each line:

    Open filenamelong For Input As #1
    i = 1
    Do While Not EOF(1)
    Line Input #1, textline


' this line works    
    Worksheets(newtestsheet).Range("B" & CStr(i)) = "=MID(A" & CStr(i) & "," & midposition & ",3)"
' this line generates a Runtime error "1004"
    Worksheets(newtestsheet).Range("C" & CStr(i)) = "=COUNTA(INDIRECT(" '"&B" & CStr(i) & "'!A:A"))"   
        i = i + 1
    Loop

I'm sure my problem has to do with trying to convert "'"&B1&"' to "'"&B&" plus the variable i -- but I can't figure out how to do it right. Can you help? Thanks.

Blackie

 

[Teeroy]

In the VBE the " ' " makes the rest of the statement a comment so that is your problem. You use the ascii code (39) to insert the character e.g.

Worksheets(newtestsheet).Range("C" & CStr(i)) = "=COUNTA(INDIRECT(" & Chr(39) & "B" & CStr(i) & Chr(39) & "!A:A))"

Notwithstanding the answer to your question, are you sure you need the single quotes?

 

[BlackieHamel]

Thanks, Teeroy, but I'm still not there.

When I use the code you gave me, the code that is produced is in the form
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=COUNTA(INDIRECT('B1'!A:A))
When what I need is[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=COUNTA(INDIRECT("'"&B1&"'!A:A"))

I guess I need:
1. double quotes around that single quote
2. an ampersand
3. another ampersand and a double quote after the i variable
4. another double quote before the last two parentheses.

I tried substituting Chr(34) for ", Chr(38) for & and Chr(39) for ' in the VBE, but I guess I did something wrong, because it still returns an error.

Can this macro be saved?

Blackie

[/TD]
[/TR]
</tbody>[/TABLE]

In the VBE the " ' " makes the rest of the statement a comment so that is your problem. You use the ascii code (39) to insert the character e.g.

Worksheets(newtestsheet).Range("C" & CStr(i)) = "=COUNTA(INDIRECT(" & Chr(39) & "B" & CStr(i) & Chr(39) & "!A:A))"

Notwithstanding the answer to your question, are you sure you need the single quotes?

 

[Teeroy]

You're close. To get a " inside the string you use "". Try

Worksheets(newtestsheet).Range("C" & CStr(i)) = "=COUNTA(INDIRECT(""" & Chr(39) & """" & Chr(38) & "B" & CStr(i) & Chr(38) & """" & Chr(39) & "!A:A))"

 

[BlackieHamel]

Teeroy,

I feel like you've gotten me close, but I don't know how to get closer! The formula you gave still creates a 1004 error. The VBE doesn't give me any information about what's wrong, but the sticking point is the code line you sent.

Let me try to boil down my request. I have created a workbook with three worksheets, named newtestsheet, ELA and DEX. newtestsheet has only two items: ELA in A1 and DEX in A2. Worksheets ELA and DEX have 19 and 164 rows with data in Column A, respectively. Here's my attempt at a macro to put the enumerations (19 and 164) from worksheets ELA and DEX into cells B1 and B2 of newtestsheet.

Sub trythis()
For i = 1 To 2

        Worksheets("newtestsheet").Range("B" & CStr(i)) = "=COUNTA(INDIRECT(""" & Chr(39) & """" & Chr(38) & "A" & CStr(i) & Chr(38) & """" & Chr(39) & "!A:A))"
Next i
        
End Sub

If you re-create my worksheet and try this, I think you'll get a 1004 error, too. Thanks for continuing to help.

Blackie

You're close. To get a " inside the string you use "". Try

Worksheets(newtestsheet).Range("C" & CStr(i)) = "=COUNTA(INDIRECT(""" & Chr(39) & """" & Chr(38) & "B" & CStr(i) & Chr(38) & """" & Chr(39) & "!A:A))"

 

[Teeroy]

Blackie,

I got too focused on what you were asking rather than what you were trying to achieve. I questioned earlier whether you needed the single quotes in the formula, you don't. You also didn't specify the Formula property for the range so it was writing to the default Value property.

When I'm writing a formula in VBA i will usually write it to the immediate window (using debug.print) and copy it into the cell to ensure that the formula actually works, like the lines I've left in your code below, before continuing.

Sub trythis()
For i = 1 To 2
        Debug.Print "=COUNTA(INDIRECT(""" & Chr(39) & """" & Chr(38) & "A" & CStr(i) & Chr(38) & """" & Chr(39) & "!A:A))" 'original formula
        Debug.Print "=COUNTA(INDIRECT(A" & CStr(i) & Chr(38) & """!A:A""))" 'formula written by next line
        Worksheets("newtestsheet").Range("B" & CStr(i)).Formula = "=COUNTA(INDIRECT(A" & CStr(i) & Chr(38) & """!A:A""))"
Next i
End Sub

Let me know how this goes.

Teeroy

 

[BlackieHamel]

Wow! Works great, Teeroy -- and it's much simpler than I thought it had to be.

Thanks for staying patient with a tyro. I have my solution!

Blackie

 

[Teeroy]

Happy to help, and we all have to start somwhere.

Personally I've learned alot from helping others as well as from reading what the MVP's post here (and sometimes asking why they've done things a certain way ).