VBA: Entering formulas into a sheet via VBA - correct way?

[downunder80]

Hi,

Novice and I always seem to have trouble with entering formulae into the workbook via VBA, it never seems intuitive to me.

I have a sheet "Sheet2" (name changed for this exercise). Simply a block of data sitting in A1:AA1000 (all cells populated).
Just in case the columns expand in the future I want to enter 4 new headers with 4 x formulae in the next 4 columns, rather than somewhere fixed, after the block of data (these doing things with the data).

Code

Dim sht As Worksheet
Dim lastColumn As Long, lastA As Long
Dim nextCol As Range, a2 As Range
Dim columnLetter1 As String

Set sht = ThisWorkbook.Worksheets("Sheet2")

Sheets("sht").Activate
lastColumn = sht.Range("A1").CurrentRegion.Columns.Count
columnLetter1 = Split(Cells(1, lastColumn + 1).Address, "$")(1)
Set nextCol = Range(columnLetter1 & "1")

nextCol.Value = "Source"
nextCol.Offset(0, 1).Value = "Type"
nextCol.Offset(0, 2).Value = "Maturity"
nextCol.Offset(0, 3).Value = "Total Score"

Everything to here works fine.
Now underneath these headers I want to populate scores of 0 or 1 based on very simple criteria via formulae that will be entered in row 2 and filled down.
This is where I have issues with runtime errors / syntax based on my coding of the formulae.
For background the value in the last column of formulae will be used at the end to filter out unwanted data.

Criteria 1 is whether the first three letters of value in Column C are "ABC". I've tried:

(1) nextCol.Offset(1, 0).Formula = Application.WorksheetFunction.IF(Left(Range("C2"), 3) = "ABC", 0, 1)
(2) nextCol.Offset(1, 0).Formula = Application.WorksheetFunction.IF(Application.WorksheetFunction.Left(Range("C2"), 3) = "ABC", 0, 1)
(3) nextCol.Offset(1, 0).Formula = "IF(Left(Range("C2"), 3) = "ABC", 0, 1)"
(4) nextCol.Offset(1, 0).Formula = "IF(Left(Range(C2), 3) = "ABC", 0, 1)"
(5) nextCol.Offset(1, 0).Formula = "IF(Left(C2, 3) = "ABC", 0, 1)"
(6) nextCol.Offset(1, 0).Formula = "IF(Left("C2", 3) = "ABC", 0, 1)"

Errors returned as follows:
(1) & (2) Run-time error '438' Object doesn't support this property or method
(3) compile/syntax error
(4),(5),(6) compile/syntax error Expected end of IF statement

Thereafter I need to do the following (have only provided one example for these):

nextCol.Offset(1, 1).Formula = A simple IF(Range(F2) = "Trade", 1, 0) formula here
nextCol.Offset(1, 2).Formula = A simple IF(Range("J2") < COBBILAT, 0, 1) formula here, where COBBILAT is a named range with a date in it
nextCol.Offset(1, 3).Formula = A formula to SUM the scores of the previous three columns, with consideration these column numbers may change. Have tried Application.WorksheetFunction.Sum(nextCol.Resize(1, 3))

Haven't even got to the stage whether the above works but I assume it won't.

Then was going to fill down to finish as follows (not tested yet maybe you can comment whether this looks ok while we're at it !! Many thx):

Set a2 = Range("A2")
Set lastA = Range("A" & Cells.Rows.Count).End(xlUp)
Set targetrng = Range(a2, lastA).Offset(0, lastColumn).Resize(, 4)
targetrng.FillDown

I actually probably know how to do this via a loop but I always get stumped on formulas for some reason so thought I should try and overcome it.

Help very much appreciated!

Cheers
JC

 

[Peter_SSs]

First problem first.
Your number 5 is closest. However, when entering a formula via vba you must double-up any quote marks and that is the most common stumbling block. You also need the = sign that goes in the formula. So try

nextCol.Offset(1, 0).Formula = "=IF(Left(C2, 3) = ""ABC"", 0, 1)"

 

[downunder80]

Hi Peter,
Thanks! That's fixed the entry of the formulae and I can get on with that part now.
Any ideas on how to sum the three column values at formula 4, given the columns won't be known upfront?
Cheers
JC

 

[Peter_SSs]

Everything to here works fine.

It shouldn't
Sheets("sht").Activate
The above line would error unless you have a sheet whose actual name is "sht". That line should be just
sht.Activate

There are a few things in your code that I would probably do a little differently but below I have tried to stick pretty much to what you had. See how it goes.
You had declared lastA as Long but then tried to assign it as a Range so I have changed the declaration to Range, and also added a Range declaration for targetrng
Note the R1C1 type formula entry into the 4th column. That's the easy way to deal with not knowing exactly which columns might be involved.

Sub downunder80()
  Dim sht As Worksheet
  Dim lastColumn As Long
  Dim nextCol As Range, a2 As Range, lastA As Range, targetrng As Range
  Dim columnLetter1 As String
  
  Set sht = ThisWorkbook.Worksheets("Sheet2")
  sht.Activate
  
  lastColumn = sht.Range("A1").CurrentRegion.Columns.Count
  columnLetter1 = Split(Cells(1, lastColumn + 1).Address, "$")(1)
  Set nextCol = Range(columnLetter1 & "1")
  
  nextCol.Value = "Source"
  nextCol.Offset(0, 1).Value = "Type"
  nextCol.Offset(0, 2).Value = "Maturity"
  nextCol.Offset(0, 3).Value = "Total Score"
  
  nextCol.Offset(1, 0).Formula = "=IF(Left(C2, 3)=""ABC"", 0, 1)"
  nextCol.Offset(1, 1).Formula = "=IF(F2=""Trade"", 1, 0)"
  nextCol.Offset(1, 2).Formula = "=IF(J2<COBBILAT, 0, 1)"
  nextCol.Offset(1, 3).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
  
  Set a2 = Range("A2")
  Set lastA = Range("A" & Cells.Rows.Count).End(xlUp)
  Set targetrng = Range(a2, lastA).Offset(0, lastColumn).Resize(, 4)
  targetrng.FillDown
  
End Sub

 

[Norie]

You don't actually need to activate the sheet.

Sub downunder80()
Dim sht As Worksheet
Dim lastColumn As Long
Dim nextCol As Range, a2 As Range, lastA As Range, targetrng As Range
Dim columnLetter1 As String

    Set sht = ThisWorkbook.Worksheets("Sheet2")

    lastColumn = sht.Range("A1").CurrentRegion.Columns.Count
    columnLetter1 = Split(Cells(1, lastColumn + 1).Address, "$")(1)
    Set nextCol = Range(columnLetter1 & "1")

    nextCol.Value = "Source"
    nextCol.Offset(0, 1).Value = "Type"
    nextCol.Offset(0, 2).Value = "Maturity"
    nextCol.Offset(0, 3).Value = "Total Score"

    nextCol.Offset(1, 0).Formula = "=IF(Left(C2, 3)=""ABC"", 0, 1)"
    nextCol.Offset(1, 1).Formula = "=IF(F2=""Trade"", 1, 0)"
    nextCol.Offset(1, 2).Formula = "=IF(J2<COBBILAT, 0, 1)"
    nextCol.Offset(1, 3).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

    With sht
        Set a2 = .Range("A2")
        Set lastA = .Range("A" & Cells.Rows.Count).End(xlUp)
        Set targetrng = .Range(a2, lastA).Offset(0, lastColumn).Resize(, 4)

    End If

    targetrng.FillDown

End Sub

 

[Peter_SSs]

You don't actually need to activate the sheet.

But you do often want to directly see the results of your code so that is not uncommon.
Also, If you are not going to activate the sheet you would need to qualify the setting of nextCol as well
(& fix the End If/End With issue)

Sub downunder80()
Dim sht As Worksheet
Dim lastColumn As Long
Dim nextCol As Range, a2 As Range, lastA As Range, targetrng As Range
Dim columnLetter1 As String

    Set sht = ThisWorkbook.Worksheets("Sheet2")

    lastColumn = sht.Range("A1").CurrentRegion.Columns.Count
    columnLetter1 = Split(Cells(1, lastColumn + 1).Address, "$")(1)
'    Set nextCol = Range(columnLetter1 & "1")
    Set nextCol = sht.Range(columnLetter1 & "1")

    nextCol.Value = "Source"
    nextCol.Offset(0, 1).Value = "Type"
    nextCol.Offset(0, 2).Value = "Maturity"
    nextCol.Offset(0, 3).Value = "Total Score"

    nextCol.Offset(1, 0).Formula = "=IF(Left(C2, 3)=""ABC"", 0, 1)"
    nextCol.Offset(1, 1).Formula = "=IF(F2=""Trade"", 1, 0)"
    nextCol.Offset(1, 2).Formula = "=IF(J2<COBBILAT, 0, 1)"
    nextCol.Offset(1, 3).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

    With sht
        Set a2 = .Range("A2")
        Set lastA = .Range("A" & Cells.Rows.Count).End(xlUp)
        Set targetrng = .Range(a2, lastA).Offset(0, lastColumn).Resize(, 4)

'    End If
    End With
    targetrng.FillDown

End Sub

 

[Peter_SSs]

@downunder80
BTW, a 'neater' way of entering those 4 headings all in one go is

nextCol.Value = "Source"
nextCol.Offset(0, 1).Value = "Type"
nextCol.Offset(0, 2).Value = "Maturity"
nextCol.Offset(0, 3).Value = "Total Score"
nextCol.Resize(, 4).Value = Array("Source", "Type", "Maturity", "Total Score")

 

[downunder80]

Hi Peter,
That worked. Thanks for the tidy ups as well (partly a fault in quickly transferring over and editing to post) and for keeping with the basic flow I already had there!
Really helpful response for what I needed!!


Hi Norie,
Thanks for the tip, and no doubt.. I seem to have heaps of trouble with always having to Activate a lot of things (thereafter it all works fine.... so far!!) even if I've declared the workbook and the worksheet etc.
I dearly want to stop using it!
Would you know why I'm finding it easier to write code after I Activate everything? In the case perhaps there's a simple answer? (there might not be and feel free to say so!)
I may come back with a separate post, better example of a short list of things I've tried that aren't working unless I Activate beforehand.


JC

 

[downunder80]

That little array idea is neat Peter. I will be adopting that

 

[Norie]

When you don't have a sheet reference when referring to things like ranges then Excel vba will refer to ranges on the currently active sheet, which may or may not be the one you mean.

One solution to that problem is to activate the sheet but another, perhaps better solution, is to make sure you use appropriate sheet references throughout the code.