VBA inserting formula's in cells not working

[thomassamoth]

For Excel VBA, I am trying to make a code which looks at the value in the column "case type" and let it paste a formula or clear a cell, based on said value in the cell of the column "case type". For Case type 1, Column 1 should contain be cleared of any values and Column 2 should contain Formula B, Column 3 should contain Formula C. For Case type 2, Column 1 should contain Formula A, column 2 should be cleared of any values and column 3 should contain formula C. For case 3, Column 1 should contain Formula A, Column 2 should contain formula B and Column 3 should be cleared of any values. If there is no case, all columns should be cleared. The result will look like this (example):

Case type	Column X [Irrelevant]	Column 1	Column 2	Column 3
Case type 1			Formula B	Formula C
Case type 2		Formula A		Formula C
Case type 3		Formula A	Formula B
Case type 1			Formula B	Formula C

To accomplish this, I have written the following code:

    Dim tbl As ListObject
    Dim cell As Range
    Dim CaseType As ListColumn
    Dim Column1 As ListColumn
    Dim Column2 As ListColumn
    Dim Column3 As ListColumn
    Dim ws As Worksheet

    Set ws = ThisWorkbook.ActiveSheet
    Set tbl = ws.ListObjects(1)

    Set CaseType = tbl.ListColumns("Case type")
    Set Column1 = tbl.ListColumns("Column 1")
    Set Column2 = tbl.ListColumns("Column 2")
    Set Column3 = tbl.ListColumns("Column 3")

        For Each cell In CaseType.DataBodyRange
             Select Case cell.Value
                Case "Case1"
                    cell.Offset(0, Column1.Index - CaseType.Index).ClearContents
                    cell.Offset(0, Column2.Index - CaseType.Index).Formula = "Formula B"
                    cell.Offset(0, Column3.Index - CaseType.Index).Formula = "Formula C"
                   
                Case "Case 2"
                    cell.Offset(0, Column1.Index - CaseType.Index).Formula = "Formula A"
                    cell.Offset(0, Column2.Index - CaseType.Index).ClearContents
                    cell.Offset(0, Column3.Index - CaseType.Index).Formula = "Formula C"
                   
                Case "Case 3"
                    cell.Offset(0, Column1.Index - CaseType.Index).Formula = "Formula A"
                    cell.Offset(0, Column2.Index - CaseType.Index).Formula = "Formula B"
                    cell.Offset(0, Column3.Index - CaseType.Index).ClearContents
    
                Case ""
                    cell.Offset(0, Column1.Index - CaseType.Index).ClearContents
                    cell.Offset(0, Column1.Index - CaseType.Index).ClearContents
                    cell.Offset(0, Column1.Index - CaseType.Index).ClearContents
            End Select
        Next cell
    End Select

Instead I get the following result:

Case type	Column 1	Column 2	Column 3
	Formula A	Formula B	Formula C
Case type 1	Formula A	Formula B	Formula C
Case type 2	Formula A		Formula C
Case type 3	Formula A	Formula B
Case type 1		Formula B	Formula C

I think the reason the values in the first two rows (excluding the header) are wrong, because I made a mistake in the offset function, but I haven't figured out how to fix it. Does someone know what I did wrong? Thanks in advance

 

[NdNoviceHlp]

Hi thomassamoff. There's a difference between clearcontents and clear. I think you will want to use clear which will remove the formula not just the numbers ie. your formulas won't be removed only there results when using clearcontents. Anyways you have no Case "Case1" .... you have Case "Case type 1" ; "Case type 2"; etc. HTH. Dave

 

[NdNoviceHlp]

Whoops! Strike that Clearcontents vs Clear diatribe. "Clear" clears formulas and formats. "ClearContents" clears values and formulas but leaves formatting intact. Dave

 

[Peter_SSs]

Do you really need to clear the formula from the relevant column? Couldn't it instead contain a formula that returns a null string "" when that column needs to show nothing?
Here is an example, where I have just made up some dummy formulas for when each column needs to show something.

thomassamoth.xlsm
	A	B	C	D	E
1	Case type	Column X [Irrelevant]	Column 1	Column 2	Column 3
2
3	Case type 1			26-Nov-23	cat
4	Case type 2		80		dog
5
6	Case type 3		50	23-Nov-23
7	Case type 1			22-Nov-23	dog
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(OR([@[Case type]]={"Case type 1",""}),"",RANDBETWEEN(1,99))
D2:D7	D2	=IF(OR([@[Case type]]={"Case type 2",""}),"",TODAY()-ROW())
E2:E7	E2	=IF(OR([@[Case type]]={"Case type 3",""}),"",INDEX({"cat","dog","rat"},MOD(ROW(),3)+1))

 

[thomassamoth]

Whoops! Strike that Clearcontents vs Clear diatribe. "Clear" clears formulas and formats. "ClearContents" clears values and formulas but leaves formatting intact. Dave

Alright, thanks for the heads up!

 

[thomassamoth]

Do you really need to clear the formula from the relevant column? Couldn't it instead contain a formula that returns a null string "" when that column needs to show nothing?
Here is an example, where I have just made up some dummy formulas for when each column needs to show something.

thomassamoth.xlsm
	A	B	C	D	E
1	Case type	Column X [Irrelevant]	Column 1	Column 2	Column 3
2
3	Case type 1			26-Nov-23	cat
4	Case type 2		80		dog
5
6	Case type 3		50	23-Nov-23
7	Case type 1			22-Nov-23	dog
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(OR([@[Case type]]={"Case type 1",""}),"",RANDBETWEEN(1,99))
D2:D7	D2	=IF(OR([@[Case type]]={"Case type 2",""}),"",TODAY()-ROW())
E2:E7	E2	=IF(OR([@[Case type]]={"Case type 3",""}),"",INDEX({"cat","dog","rat"},MOD(ROW(),3)+1))

No, it needs to be clear of contents. The reason being is that the empty cell can then be used to fill in a value, which in turn is used in a formula in the other two columns. For some reason, if I run the program twice, it fixes my problem. I don't know why this is the case, but I now just put in a for loop, which will always makes the program run twice and it does the job pretty well. Thanks though for your answer and effort, much appreciated!

 

[thomassamoth]

Correction, with the for loop making the program running twice, I still have the problem that the formula's get offset in the wrong row.

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Sub Test()
  Dim tblName As String
  Dim NumCount As Long
  Dim RowCount As Long
  Dim Column1 As Range
  Dim Column2 As Range
  Dim Column3 As Range
  
  tblName = ActiveSheet.ListObjects(1).Name
  
  Set Column1 = Range(tblName & "[Column 1]")
  Set Column2 = Range(tblName & "[Column 2]")
  Set Column3 = Range(tblName & "[Column 3]")
  RowCount = Column1.Rows.Count

  With Column1
    .Formula2 = "=IF(OR([@[Case type]]={""Case type 1"",""""}),TRUE,1)"
    NumCount = Application.Count(Range(.Address))
    If NumCount > 0 Then .SpecialCells(xlFormulas, xlNumbers).Formula2 = "Formula A"
    If NumCount < RowCount Then .SpecialCells(xlFormulas, xlLogical).ClearContents
  End With
  
  With Column2
    .Formula2 = "=IF(OR([@[Case type]]={""Case type 2"",""""}),TRUE,1)"
    NumCount = Application.Count(Range(.Address))
    If NumCount > 0 Then .SpecialCells(xlFormulas, xlNumbers).Formula2 = "Formula B"
    If NumCount < RowCount Then .SpecialCells(xlFormulas, xlLogical).ClearContents
  End With
  
  With Column3
    .Formula2 = "=IF(OR([@[Case type]]={""Case type 3"",""""}),TRUE,1)"
    NumCount = Application.Count(Range(.Address))
    If NumCount > 0 Then .SpecialCells(xlFormulas, xlNumbers).Formula2 = "Formula C"
    If NumCount < RowCount Then .SpecialCells(xlFormulas, xlLogical).ClearContents
  End With
End Sub