Adding a column to an excel sheet table based on if else condition

Question

I am using below code to add a column named "Roles".

If WorksheetFunction.CountIf(rngX, "Roles") > 0 Then
    MsgBox "Roles column Already present"
Else
     ESheet.Columns("C:C").Insert Shift:=xlToRight,CopyOrigin:=xlFormatLeftOrAbove
     ESheet.Range("C1").Value = "Roles"
     LastRow1=ESheet.Range("A", & Cells.Rows.Count).End(xlUp).Row
     ESheet.Range("C2:C", & LastRow1).Formula="VLOOKUP(B2, Roles!$A:$B,2,FALSE)"
End IF

I am trying this code, There is a Roles sheet which I am referring for vlookup and adding new column named "Roles" in new tab "Titles". If column is already there, it shows msgbox but changes Vlookup values -- vlookup(B2, #Ref!$A:$B,2,FALSE)

Basically i want to remove this vlookup error so that it keeps referring Roles tab

Anyhow, my code works for that situation, too. It would solve the problem even if the column would not be the third one... — FaneDuru
– FaneDuru, Commented Mar 2, 2020 at 9:54

Variatus · Accepted Answer · 2020-03-02 09:37:09Z

1

This code should do the job.

Const Cap As String = "Roles"
Const Clm As Long = 3

With ESheet.Columns(Clm)
    If .Cells(1).Value <> Cap Then
        .Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1).Offset(0, -1).Value = Cap
    End If
End With

It will check if the caption of columns(Clm) - where 3 = "C" - is Cap (here "Roles"). If it isn't a blank column will be inserted and given that name.

I took note that you address the worksheet by its CodeName. Few people here do although its efficient and usually preferable. If you did so by accident or error note the alternation, Worksheets("ESheet") which addresses the same sheet by its tab name.

answered Mar 2, 2020 at 9:37

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FaneDuru · Accepted Answer · 2020-03-02 09:41:05Z

1

Try this code, please:

Sub ConditInsertColumn()
  Dim ESheet As Worksheet, lastCol As Long, rngRol As Range
  'Set ESheet = ActiveSheet 'use here your definition or skip it
  lastCol = ESheet.Cells(1, Cells.Columns.Count).End(xlToRight).column
   Set rngRol = ESheet.Range(ESheet.Cells(1, 1), ESheet.Cells(1, lastCol)).Find("Roles")
    If rngRol Is Nothing Then
        ESheet.Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatLeftOrAbove
        ESheet.Range("C1").value = "Roles"
    End If
End Sub

answered Mar 2, 2020 at 9:41

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Comments

Foxfire And Burns And Burns · Accepted Answer · 2020-03-02 09:49:33Z

1

Just count if you got any header in row 1 with value Roles. If the count is 0, add the column, else it means the column already exists, so do nothing

If Application.WorksheetFunction.CountIf(ESheet.Rows("1:1"), "Roles") = 0 Then
    ESheet.Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatLeftOrAbove
    ESheet.Range("C1").Value = "Roles"
End If

answered Mar 2, 2020 at 9:49

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges43 bronze badges

2 Comments

Nipen Mahajan Over a year ago

This condition works but it changes my Vlookup formula. Please see my edited code above. Vlookup throws error.

Foxfire And Burns And Burns Over a year ago

@NipenMahajan That's a different question. Anyways, why inserting a column would make your Vlookup fail?

Collectives™ on Stack Overflow

Adding a column to an excel sheet table based on if else condition

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related