#1
|
|||
|
|||
Insert formulas in VBA code
Hi All,
First of all thank so much everyone for assisting me learing VBA coding. I have started doing it on my own as a beginner. I am creating a file in which i am done with half of my work but am stuck at one point where i need to enter countif formula in VBA code. I have attached my working file here.
Thanks |
#2
|
|||
|
|||
Your attached file was not saved as an xlsm so your code is not there. You can do most of the regular formulas in VBA by typing out WorksheetFunction. An example is below
Code:
val = WorksheetFunction.CountIf(range("A:A"),range("A1").value) Let me know if this is any help at all or if you just a code snippet. Thanks |
#3
|
|||
|
|||
how 'bout this?
Code:
Range("D2").Formula = "=countif(E2:M2,""True"")" Range("D2:D10").FillDown |
#4
|
|||
|
|||
NoSparks thanks for the contribution. The only reason I prefer to use the worksheetfunction rather than the formula is because if the user is using VBA they can bypass any calculations that need to continually happen if a formula is put in.
|
#5
|
|||
|
|||
Two totally different things.
Obviously, we don't have the same interpretation of the OPs request. |
#6
|
|||
|
|||
I understand the perception but disagree. I do not believe that using the worksheetfunction in VBA and inserting a formula are 2 TOTALLY different things. You have enough successful posts of code to know that there is always more than 1 way to get the same thing done. I was merely stating that using the worksheet function can eliminate the need for the worksheet to recalculate. Some people may not understand how necessary this is unless they are working with very large sets of data, such as 20,000+ rows. I myself would never run a regular countif formula on a large set of data because it just takes too long, and then if I have used an autofilter the worksheet would need to recalculate after I unfiltered or deleted any rows. To each their own but again I dont find these totally different.
|
#7
|
|||
|
|||
Hi Guys,
NoSparks, I tried your code it works perfectly fine. But i have 80000 rows in my data so it takes lot of time to complete the task. excelledsoftware, I tried your code but could not write it properly. If you could provide me the worksheet function code then that will be great coz it will save lot of time. Thnaks guys. |
#8
|
|||
|
|||
Two different interpretations of a question.
That's why you got 2 different suggestions. What I see has gone from 10 rows to over 80,000. Go with excelled's suggestion. |
#9
|
|||
|
|||
Quote:
|
#10
|
|||
|
|||
I had the opportunity to try out some similar code at work today for over 160000 rows. It took about 10 minutes to run so yours might take about 5. Will this work. If not we can make it a lot faster by sorting the data and then doing an if statement. Is it possible to have the data sorted for this project
|
#11
|
|||
|
|||
Ok I am guessing we are ok to run the regular plan. If you only have a few columns this should actually run pretty quick. I was able to use your attached spreadsheet, duplicate the data 65,000 times and run this code. It took about 8 seconds to complete.
Here is the code Code:
Option Explicit Sub CountTrueValues() 'Inserts a column to the right of C and then runs 'a countif to see how many times the phrase true 'is entered in the columns Dim CheckRow As Long, LastRow As Long, Tot As Integer Dim LastCol As String 'Insert a column Range("D:D").Insert Range("D1").Value = "TRUE Count" 'Identify where to stop LastRow = Range("C2").End(xlDown).Row LastCol = Range("A1").End(xlToRight).Address LastCol = Mid(LastCol, 2) 'remove first $ LastCol = Mid(LastCol, 1, InStr(1, LastCol, "$") - 1) 'remove the row 'Go through the data and enter a value For CheckRow = 2 To LastRow Tot = WorksheetFunction.CountIf(Range("E" & CheckRow & ":" & LastCol & CheckRow), "true") Range("D" & CheckRow).Value = Tot Next CheckRow MsgBox "done" End Sub |
#12
|
|||
|
|||
That long line with the countif drives me crazy. Here is a revised version that removes that long line but does the very same thing.
Code:
Option Explicit Sub CountTrueValues() 'Inserts a column to the right of C and then runs 'a countif to see how many times the phrase true 'is entered in the columns Dim CheckRow As Long, LastRow As Long, Tot As Integer Dim LastCol As String, CountRange As String 'Insert a column Range("D:D").Insert Range("D1").Value = "TRUE Count" 'Identify where to stop LastRow = Range("C2").End(xlDown).Row LastCol = Range("A1").End(xlToRight).Address LastCol = Mid(LastCol, 2) 'remove first $ LastCol = Mid(LastCol, 1, InStr(1, LastCol, "$") - 1) 'remove the row 'Go through the data and enter a value For CheckRow = 2 To LastRow CountRange = "E" & CheckRow & ":" & LastCol & CheckRow Tot = WorksheetFunction.CountIf(Range(CountRange), "true") Range("D" & CheckRow).Value = Tot Next CheckRow MsgBox "done" End Sub |
#13
|
||||
|
||||
With 54000 rows of data, the following took 1.25 minutes on my laptop:
Code:
Sub Demo() Application.ScreenUpdating = False Dim lRow As Long, lCol As Long, i As Long, j As Long, x As Long Dim eTime As Single ' Start Timing eTime = Timer With ThisWorkbook.Worksheets("Sheet1").UsedRange .Columns(3).Insert .Cells(1, 3).Value = "Active/Inactive" lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row lCol = .Cells.SpecialCells(xlCellTypeLastCell).Column On Error Resume Next For i = lRow To 1 Step -1 If .Range(.Cells(i, 1), .Cells(i, lCol)).SpecialCells(xlCellTypeBlanks).Count < lCol Then lRow = i: Exit For Next For i = lCol To 1 Step -1 If .Range(.Cells(1, i), .Cells(lRow, i)).SpecialCells(xlCellTypeBlanks).Count < lRow Then lCol = i: Exit For Next On Error GoTo 0 For i = 2 To lRow x = 0 For j = 5 To lCol If UCase(.Cells(i, j).Text) = "TRUE" Then x = x + 1 Next .Cells(i, 3).Value = x .Cells(i, 14).Value = x & .Cells(i, 2).Value Next End With Application.ScreenUpdating = True ' Calculate elapsed time eTime = (Timer - eTime + 86400) Mod 86400 ' Just in case execution time spans midnight MsgBox "Execution took " & Format(eTime / 86400, "hh:mm:ss") End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Macropod I guess my computer is really fast because it ran your code in the same amount of time as mine ran, crazy. I did make a mistake on the header though revised code below once more.
Code:
Option Explicit Sub CountTrueValues() 'Inserts a column to the right of C and then runs 'a countif to see how many times the phrase true 'is entered in the columns Dim CheckRow As Long, LastRow As Long, Tot As Integer Dim LastCol As String, CountRange As String 'Insert a column Range("D:D").Insert Range("D1").Value = "Active/Inactive" 'Identify where to stop LastRow = Range("C2").End(xlDown).Row LastCol = Range("A1").End(xlToRight).Address LastCol = Mid(LastCol, 2) 'remove first $ LastCol = Mid(LastCol, 1, InStr(1, LastCol, "$") - 1) 'remove the row 'Go through the data and enter a value For CheckRow = 2 To LastRow CountRange = "E" & CheckRow & ":" & LastCol & CheckRow Tot = WorksheetFunction.CountIf(Range(CountRange), "true") Range("D" & CheckRow).Value = Tot Next CheckRow MsgBox "done" End Sub Code:
Sub CheckForTrueValue() 'Inserts a column to the right of C and then runs 'a countif to see how many times the phrase true 'is entered in the columns Dim CheckRow As Long, LastRow As Long, CheckVal As String Dim CheckCol As Long, LastCol As Long 'Insert a column Range("D:D").Insert Range("D1").Value = "Active/Inactive" 'Identify where to stop LastRow = Range("C2").End(xlDown).Row LastCol = Range("A1").End(xlToRight).Column 'Go through the data and enter a 1 For CheckRow = 2 To LastRow For CheckCol = 5 To LastCol CheckVal = Cells(CheckRow, CheckCol).Value If UCase(CheckVal) = "TRUE" Then Range("D" & CheckRow).Value = 1 Exit For End If Next CheckCol Next CheckRow MsgBox "done" End Sub Thanks to Macropod, NoSparks and CharlesDH for their help. |
#15
|
||||
|
||||
Well, if I use the worksheetfunction, execution time for the 54000 rows I have reduces by 20%, to 1 minute. Then, if I also omit the code to update the last column (which isn't in your code), execution time reduces to just 35 seconds. Granted, that's still much slower than your time, but that may be partly due to whatever other background processes are running on my laptop vs your PC. Doubtless, differences in CPUs, RAM, etc, also play a part.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA code for Microsoft Word macro — select text and insert footnote | ndnd | Word VBA | 10 | 01-06-2015 01:47 PM |
Help with IF or ELSE Formulas | tsaladyga | Excel | 4 | 07-23-2014 09:04 AM |
Search and replace/insert HTML code into Master File using tags | dave8555 | Excel | 2 | 02-23-2014 03:51 PM |
Need help with formulas please | paul_pearson | Excel | 0 | 03-20-2013 06:51 AM |
IF Formulas | mizzamzz | Excel | 1 | 07-08-2010 02:32 AM |