Help with Excel VBScript - fill columns with data

Question

Hi all,

I have a spreadsheet with email addresses (column A) I need to write a macro/VBScript function to fill columns B to L with numbers 1 to 11 respectively i.e.

[email protected] 1 2 3 4 5 6 7 8 9 10 11

Could someone help me achieve this please?

TIA.

Answer 1

I do not own excel so I'm not sure if this works properly:

for i = 0 to rowCount
 Range("A" + i).select
 for j = 1 to 11
  Cells(i,j).Select ' I'm not sure if this line works
  ActiveCell.FormulaR1C1 = i
 next j
next i

Hope this works.

Answer 2

Search yourself a tutorial. Don't copy&paste everything.

Something like this: http://www.excel-vba.com/index.htm

Answer 3

This should do what you ask. This will add the numbers 1-11 to any row that contains data in the first cell.

Sub AddNumbers()
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    For Each cell In Range(Cells(1, 1), Cells(LastRow, 1))
        If cell.Text <> "" Then
            For i = 2 To 12
                Cells(cell.Row, i).Select
                ActiveCell.Value = (i - 1)
            Next
        End If
    Next
End Sub