IF statement in VB code

[danno_mbk]

Hi,

On a worksheet I have Headings A B C D, sometimes one of the headings will be missing so it might be A C D. I want to write some VB code to say if one of the headings is missing insert a new column. (taking into account there will be data under each heading.

Is is possible to write in VB the following statement ?

If active cell = A, then offset right, else insert column. then offset cell right,

then run it again for B, C, D ect..??

(Sorry my IF statements are rubbish !)

Thanks

Dan

 

[Colo]

Hi Dan, try something like this.

Sub InsertColumn()
    Dim arrHeader As Variant
    Dim i As Long
    'Header
    arrHeader = Array("A", "B", "C", "D")
    For i = LBound(arrHeader) To UBound(arrHeader)
        If Cells(1, i + 1).Value <> arrHeader(i) Then
            Columns(i + 1).Insert Shift:=xlToRight
            Cells(1, i + 1).Value = arrHeader(i)
        End If
    Next
End Sub

 

[danno_mbk]

thanks colo, that works..but only if my table starts in cell A1...

unfortunately my table doesnt. it starts in cell B4. so I get lots of columns inserted from A onwards ?

Is it possible to make the formula run for a table that starts anywhere other than cell A! ???

thankyou

 

[danno_mbk]

thanks Colo for your help on this, unfortunatly its not working. I have been playing with the code experimenting trying to get it to do what I want but it dosent want to know

The code does start at the correct place, but now the headings are shifted right not the columns ,

I tried to work it out but I failed !

I appreciate all your help on this
thanks
Dan

 

[Colo]

Dan please try this one. (I deleted my previous posting by my operating mistake.)

Sub InsertColumn3()
'Change [B4] to appropriate table starting range
    Dim arrHeader As Variant
    Dim i As Long
    arrHeader = Array("A", "B", "C", "D") 'Header
    For i = LBound(arrHeader) To UBound(arrHeader)
        If Cells([B4].Row, i + [B4].Column).Value <> arrHeader(i) Then
            Columns([B4].Column + i).Insert Shift:=xlToRight
            Cells([B4].Row, i + [B4].Column).Value = arrHeader(i)
        End If
    Next
End Sub

 

[danno_mbk]

Colo,,,,, you are the man !! ray:

thanks for helping me out dude.

ありがとう。 よい週末を過しなさい

if your fishing this weekend hope you get a good one !
thanks man
Dan

 

[Colo]

You are welcome, and your Japanese seems better than my English. Have a nice weekend to you too.
Unfortunately, I'll be @ work tomorrow.