Inserting a charecter in a set number of columns to the right of a cell containing a number which fi

John, you can do this easily with VBA (macros). When you say insert a 1, do you mean shift the data to the right and put the value 1 in column H or do you mean put the value 1 in the cell in column H and over-write any value that might be in column H?

BarrieBarrie Davidson

I think this is better done with a macro.

However, you can also use a formula:

Lets say that 7 is in A1,

in B1 enter: =IF(COLUMN()-1 < $A$1,1,"")

Copy this across as far as needed.

Aladin

=========

I mean leave the 7 in column A and insert 1 in columns B to H. Aladin seems to have answered my question in his reply but any other input you have would be much appreciated. Thanks guys!

John

Aladin's formula will do the trick for you, however you will have data in cells that are not required (columns I to wherever you end the IF statement). If you want to eliminate that you could use VBA to insert the value 1. Would you like to pursue this option?

BarrieBarrie Davidson

Barrie,

Aladins formula will do for now. i am trying to learn more about VBA but never seem to be able to devote enough time to it (i have a young family). If you have a solution that a newbie to VBA can follow it would be appreciated. Will pick up your comments and reply tomorrow. Thank you

John

John, I think this code will do the trick for you (I've inserted comments to indicate what it is doing).

Sub InsertOnes()
' Written by Barrie Davidson

'This will look at each cell from A1 to the last record in column A
'using "For Each cell" and "Next cell"
For Each cell In Range("A1", Range("A65536").End(xlUp).Address)
'This sets the value of each cell (with column numbers indicated
'by the value of the cell in column A) with the number 1
Range(Cells(cell.Row, 2), Cells(cell.Row, cell.Value + 1)).Value = 1
Next cell

End Sub

If you have any questions just let me know.

Regards,
BarrieBarrie Davidson

Barrie,

Thanks for the code. I will try it out at work in the morning (i live in the UK). Once again your assistance is very much appreciated!