I'm trying to increment this formula across 254 columns. I get a runtime error every time. I've taken it out of the for loop, used .FormulaLocal, used .Value.
Any help is appreciated.strMastery = "=IF(COUNTA(ADDRESS(4," & j & "):ADDRESS(200," & j & "))=0, -1, COUNTIF(ADDRESS(4," & j & "):ADDRESS(200," & j & "),ADDRESS(3," & j & "))/COUNTA(ADDRESS(4," & j & "):ADDRESS(200," & j & ")))" For j = 2 to 255 workBook.WorkSheets(2).Cells(2, j).Formula = strMastery Next
Hello
Try this
My knoledge is very poor on formulars so it may not be inserting the correct formular.Dim j As Long With ThisWorkbook.Worksheets(2) For j = 2 To 255 .Cells(2, j).FormulaR1C1 = """=IF(COUNTA(ADDRESS(4," & j & "):ADDRESS(200," & j & "))=0, -1, COUNTIF(ADDRESS(4," & j & "):ADDRESS(200," & j & "),ADDRESS(3," & j & "))/COUNTA(ADDRESS(4," & j & "):ADDRESS(200," & j & ")))""" Next j End With
If not if you can give me an example of how the formular would look if written in the address bar we can go from there.
hope it helps
Hello trainump,
Are you using this in a Visual Basic Script file or writing a macro for your Excel VBA workbook project?
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
I see what you were trying to do there - enclose everything in double quotes. All that does is put the formula in the cells as a string surrounded by double quotes.
I am writing a Visual Basic script that accesses the Excel file, not a macro.
Hello trainump,
You should really post the full script. Without it, we are all guessing about what you're doing.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Let me simplify the formula and see if that helps to conceptualize it. You definitely don't want to see all of it.
strMastery = "=SUM(A1:A" & j & ")" For j = 2 to 255 workBook.WorkSheets(2).Cells(2, j).Formula = strMastery Next
You don't need a loop at all to do that, but to take your example, the string is created outside the loop, and so has whatever value of j existed there, unaffected by the loop.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Okay, well I've tried it with the j inside of the loop and I get the same runtime error on the line that assigns the formula.
Dim j As Long Dim s As String With ActiveWorkbook.Worksheets(1) For j = 2 To 255 s = "=SUM(A1:A" & j & ")" .Cells(2, j).Formula = s Next End With
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Hello trainump,
You're asking for a solution to a VBS problem. While VBS and VBA are similar, there are also small, but critical differences. I wouldn't have ask to see the entire script, if I didn't have a reason.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
SHG, in VB Script there is only one data type - Variant.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Yup. Based on the code, it wasn't clear to me that the OP really wanted VBS and not VBA.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks so much guys. I decided to go a different way altogether.
If you want to continue using the Forum I suggest you read the Forum Rules & provide information requested by people trying to help
Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks