VBA I think it is simple but

[Endeavouring]

Hi

I have a simple user form with a refedit field to select a column , and an option button (optionbutton1) I then want to insert a new column to the right of the column selected by the refedit if the value of optionbutton1 = True

I have tried things like
If optionbutton1.value = True Then
Range(RefEdit1.Value).Column
Selection.Insert Shift:=xlToRight

but then it falls over at the .column

I then ultimately want to fill that column with the length of the data in the cell to the left, but my initial problem is getting the value of the refedit to insert the column.

Thanks

 

[VoG]

Try

If optionbutton1.Value = True Then
    Range(RefEdit1.Value).EntireColumn.Insert

 

[Endeavouring]

Cheers vog

But any chance it could insert the new column to the right of the selection by refedit, this appears to insert it to the left.

 

[combatvolcano]

Probably change Selection.Insert Shift:=xlToRight

 

[VoG]

Perhaps

If optionbutton1.Value = True Then
    Range(RefEdit1.Value).Offset(, 1).EntireColumn.Insert

 

[Endeavouring]

Cheers that worked

 

[Endeavouring]

Hi

Vog kindly solved my initial problem of inserting a new column, but can anyone assist with the second part which is to fill that column with the length of the data in the cell to the left. ie. a VBA equivalent of the value of =LEN(xx)

Cheers

 

[VoG]

Try

If optionbutton1.Value = True Then
    Range(RefEdit1.Value).EntireColumn.Copy
    Range(RefEdit1.Value).Offset(, 1).EntireColumn.Insert

 

[Endeavouring]

Hi VoG
that copies the contents of the original column, but what I want is the length (number of characters incl.spaces) of the original column ie. the quivalent of =len(xx) in each cell containing data.

ie. original cell contained text "the Cat" the corresponding cell in the new column would display 7

 

[VoG]

Try

f optionbutton1.Value = True Then
    Range(RefEdit1.Value).EntireColumn.Copy
    Range(RefEdit1.Value).Offset(, 1).EntireColumn.Insert
    For Each C In Range(RefEdit1.Value).Offset(, 1).EntireColumn.SpecialCells(xlCellTypeConstants)
        C.Value = Len(C.Value)
    Next C