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]

Glad you are making progress!

 

[downunder80]

@ Norie
Thanks and yes I'm with you. When I find a good example of the reason I got (wrongly) hooked on activate I'll send in a question. Maybe the answers will help others at my level.
For now I think it is because I normally build single spreadsheet tools, and once in a sheet (activated), do a lot of things with the code.
Perhaps I need to combine more With, End With practice to supplement, which are not statements in my kit bag generally but for the odd cell colouring.

Thanks again
JC

 

[Rick Rothstein]

@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")

Since the formulas are short enough, you could do the same for entering them as well...

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])"
nextCol.Offset(1).Resize(, 4).Value = Array("=IF(Left(C2, 3)=""ABC"", 0, 1)", "=IF(F2=""Trade"", 1, 0)", "=IF(J2<COBBILAT, 0, 1)", "=SUM(RC[-3]:RC[-1])")

 

[downunder80]

Another cool idea. Thanks Rick!
That has me thinking you could keep a sheet where you can create the strings of many different formulas and just create various arrays off that, for example.
So many possibilities.