Converting a formula to a VBA command -- string functions

BlackieHamel

Board Regular
Joined
May 9, 2014
Messages
91
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:
Code:
    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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Board Regular
Joined
May 9, 2014
Messages
91
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
=COUNTA(INDIRECT('B1'!A:A))
When what I need is

<tbody>
</tbody>
=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


<tbody>
</tbody>
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.

Code:
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

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
You're close. To get a " inside the string you use "". Try

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

BlackieHamel

Board Regular
Joined
May 9, 2014
Messages
91

ADVERTISEMENT

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.

Code:
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

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

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Board Regular
Joined
May 9, 2014
Messages
91
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

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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 :)).
 

Forum statistics

Threads
1,141,487
Messages
5,706,663
Members
421,459
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top