Insert formulas in VBA code

saurabhlotankar · #1 06-05-2015, 05:01 AM

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.

First i want to add a column ahead of column"C"(It will be "D") and Give header to it as "Active/Inactive"
From D2 onwards till the end(in this example its D10) i want to put countif formula to count number of "True" in each row. So in D2 the formula will be=countif(E2:M2,"True") and so on till the end
In the same way i want to add one more column in the beginning that will become column "M". and from A2 onwards i want to put formula =C2&B2

I want a VBA code for these two steps. If you could help with in it then that will be really helpful.

Thanks

excelledsoftware · #2 06-05-2015, 10:43 PM

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)

Its great that you are learning how to code. Another resource you can use is the macro recorder. While this bloats things quite a bit you can get the function syntax for something like inserting a column just by recording it and then looking at the code.

Let me know if this is any help at all or if you just a code snippet.

Thanks

NoSparks · #3 06-06-2015, 07:28 AM

how 'bout this?

Code:

Range("D2").Formula = "=countif(E2:M2,""True"")"
Range("D2:D10").FillDown

excelledsoftware · #4 06-07-2015, 12:26 AM

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.

NoSparks · #5 06-07-2015, 06:59 AM

Two totally different things.
Obviously, we don't have the same interpretation of the OPs request.

excelledsoftware · #6 06-07-2015, 09:12 PM

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.

saurabhlotankar · #7 06-11-2015, 03:32 AM

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.

NoSparks · #8 06-11-2015, 08:51 AM

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.

excelledsoftware · #9 06-11-2015, 11:04 AM

Quote:

Originally Posted by saurabhlotankar

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.

Yes I will have this complete for you later tonight. Please keep in mind that 80000 rows will still take a little bit of time but it shouldn't be bad After it is done. We may want to consider additional options if you find it still takes too long. I will write it up for you later tonight

excelledsoftware · #10 06-11-2015, 03:19 PM

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

excelledsoftware · #11 06-11-2015, 08:14 PM

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

Let me know if this works or if you want to go about it a different way.

excelledsoftware · #12 06-11-2015, 08:17 PM

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

macropod · #13 06-11-2015, 08:20 PM

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

excelledsoftware · #14 06-11-2015, 08:46 PM

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

Now that I see the header Active/Inactive I am wondering if you just needed to check if the one of the columns had True or not. If this is the case we can make this way faster by using the following code that only took 6 seconds to run.

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

Please let us know if any of these workout for you.

Thanks to Macropod, NoSparks and CharlesDH for their help.

macropod · #15 06-11-2015, 09:06 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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