How to edit of rows in Excel-VBA - Test Data Generation

[rfernandes]

I've recorded a macro to create columns in the table I want to use, now I want to input things in each row. I found this piece of code that will ask for number of rows of input and display that many blank rows:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A2")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
MsgBox r.Address
If r.Offset(1, 0) = "" Then Exit Do
Loop
</code>

I was wondering how exactly(where in the loop?) do i insert things into these rows based on the columns I have ? Which function lets me that? ... Like I have a columns id, firstname, lastname, date of birth. So like for column first name, I want like a random name chosen from an array of names ? ... date of birth (random dates in a range of dates), a random non-repeatable number for id. exactly how do I associate it to each column ?

 

[sijpie]

I am not sure what you are trying to do: insert rows or insert columns? And why do you want to insert rows filled with random selections? Is this really what you are trying to achieve?
Why would you not add these to the end of the table? If you really want to insert the rows, where do you want to insert them, at the top of the table?

Anyway I would think your columns are in a fixed order and don't move about. If they move about you can find the column by searching for the column header.

So say you fill a set of parameters with random names, then you can at the end put each parameter in the right column.

   dim rIns as range
   Dim lID as Long
   Dim sFN as string, sLN as String
   Dim dBD as Date
...
   ' insert at the top of the table
   set rIns= Range("A2")
   rIns.EntireRow.Insert

   ' fill the variables with random selection from the ranges
   With Application.WorksheetFunction
      lID = .Max(Columns("A"))+1
      sFN = Range("FirstNames").Cells(.RandBetween(1,Range("FirstNames").Cells.Count),1)
      sLN = Range("LastNames").Cells(.RandBetween(1,Range("LastNames").Cells.Count),1)
      dBD = Range("BirthDates").Cells(.RandBetween(1,Range("BirthDates").Cells.Count),1)
   End With

'   write the variables to the sheet
   rIns.Offset(-1,0)= lID
   rIns.Offset(-1,1)= sFN
   rIns.Offset(-1,2)= sLN
   rIns.Offset(-1,3)= dBD

for this to work, your first names should be in a named range called 'FirstNames'
your lastnames in a named range called 'LastNames', and
the birth dates in a named range called 'BirthDates'