Inserting a charecter in a set number of columns to the right of a cell containing a number which fi
Posted by John on October 30, 2001 11:37 AM
Please help. I need to insert a "1" in a number of cells to the right of a cell in which there is a number denoting the number of columns to insert the "1" into. In other words if i type a 7 in column A, i then need the spreadsheet to insert a "1" in the 7 cells immediatly to the right. Can anyone help please? my brain is ready to explode with trying diferent logical expressions!
Posted by Barrie Davidson on October 30, 2001 11:40 AM
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
Posted by Aladin Akyurek on October 30, 2001 11:44 AM
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
=========
Posted by John on October 30, 2001 1:02 PM
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
Posted by Barrie Davidson on October 30, 2001 1:05 PM
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
Posted by john on October 30, 2001 1:50 PM
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
Posted by Barrie Davidson on October 31, 2001 6:02 AM
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
Posted by John on October 31, 2001 12:50 PM
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!
